[UPHPU] database architecture for type with 2 parents

thebigdog bigdog at venticon.com
Tue Sep 16 14:53:52 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"
>           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

Why not have a domain table and subdomain table with a fk to the domain table?
This would allow you to link the 2 together. The for your site you can do a fk
to your domain, subdomain par.

i could be way off the mark on this one. Then create a view that will populate
from the 3 tables using joins. Technically if you wanted you could put your
stuff in the domain and subdomain tables and create your view from there based
on the where clause with your domain info or you can kick it up a notch and
create a stored proc to return your domains subdomains.


More information about the UPHPU mailing list