[UPHPU] database architecture for type with 2 parents

Richard K Miller richardkmiller at gmail.com
Thu Sep 11 15:38:38 MDT 2008

Here's a database architecture question:

I have a "domain" table (google.com, byu.edu, etc.) and a "subdomain"  
table (fakesteve.blogspot.com, dev.mysql.com, etc.) A subset of the  
domains and subdomains are my sites (richardkmiller.com,  
richardkmiller.wordpress.com) and have additional attributes that  
don't need to be stored for all domains and subdomains.

What's the best practice for creating a "mysites" table that is  
composed of both a subset of domains and a subset of subdomains?   
Here's what I have:

CREATE TABLE `mysites` (
   `id` int(12) NOT NULL auto_increment,
   `type` enum('domain','subdomain'),
   `fk_id` int(12),
   `hosting` varchar(24),
   PRIMARY KEY  (`id`),
   KEY `fk_id` (`fk_id`)

When type="domain", fk_id is a foreign key to the domain table. When  
type="subdomain", fk_id refers to the subdomain table. This has a  
"hacky" feel that I don't like. Also, if I create a VIEW of my sites,  
I can't edit it because the VIEW contains a UNION:

CREATE VIEW `mysitesview` as
	SELECT mysites.id, type, domain AS site, hosting, fk_id
	  FROM domain JOIN mysites
             ON domain.id = mysites.fk_id AND type = "domain"
         SELECT mysites.id, type, subdomain AS site, hosting, fk_id
           FROM subdomain JOIN mysites
             ON subdomain.id = mysites.fk_id AND type ="subdomain";

Any thoughts? How could I do this better?


More information about the UPHPU mailing list