[UPHPU] database architecture for type with 2 parents
bms at mscis.org
Tue Sep 16 14:32:26 MDT 2008
-----BEGIN PGP SIGNED MESSAGE-----
Richard gets this three times since I kept using the wrong email address
to send my reply.... sorry Richard.
Richard K Miller wrote:
| 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
| 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"
| 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?
I have a couple questions on your design:
~ 1. I can see a few reasons for having separate domains and subdomains
tables, but I can also see why one might want both to be in one table
since subdomains are really also domains in themselves. What's your
reason for separating the two?
~ 2. Can you put the two tables together without breaking anything, or
violating something you need normalized that way?
Now for a quick suggestion. Something like this might work (definitely
check that if clause):
CREATE VIEW `mysitesview` AS
~ if(type=domain,domain,subdomain) as site,
~ LEFT JOIN domain ON mysites.fk_id = domain.id AND type = "domain"
~ LEFT JOIN subdomain ON mysites.fk_id = subdomain.id AND type =
Or maybe you can use a subquery in there. If you have some sample data
dumps I can test this on, I may have time to test a few ideas.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.9 (GNU/Linux)
Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org
-----END PGP SIGNATURE-----
More information about the UPHPU