[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,
PRIMARY KEY (`id`),
KEY `fk_id` (`fk_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
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