[UPHPU] database architecture for type with 2 parents

Brandon Stout bms at mscis.org
Tue Sep 16 14:32:26 MDT 2008


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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
| 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

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
SELECT
~ mysites.id,
~ type,
~ if(type=domain,domain,subdomain) as site,
~ hosting,
~ fk_id
FROM mysites
~ LEFT JOIN domain ON mysites.fk_id = domain.id AND type = "domain"
~ LEFT JOIN subdomain ON mysites.fk_id = subdomain.id AND type =
"subdomain";

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.

Brandon Stout
UDBUG Admin

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.9 (GNU/Linux)
Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org

iEYEARECAAYFAkjQF9oACgkQx0pgn74qrcILwwCcChSgYOEFsNLkgsVM1OvM1VRR
qnsAnAkhacg+OWgYPnlL4zEO5/ydTuNa
=uBsj
-----END PGP SIGNATURE-----


More information about the UPHPU mailing list