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`)
) 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"
UNION
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?
Richard
_______________________________________________
UPHPU mailing list
[email protected]
http://uphpu.org/mailman/listinfo/uphpu
IRC: #uphpu on irc.freenode.net
