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