[thelist] DB Schema Design

Matt Warden mwarden at gmail.com
Sun May 7 12:52:41 CDT 2006


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

Hershel Robinson wrote:
> Christie Mason wrote:
> 
>>I like the single table idea as long as you have some type of field, sounds
>>like that may be class, to filter the entries for selects and validation by
>>its appropriate application.
>>
>>Maintaining 17 different lookup tables would be an admin nightmare.
> 
> 
> Not sure why that would be worse than on table with 1000 entries.

Sure it is. That's like saying 1000 different classes (in the OOP sense)
is the same as having 1000 instances of the same class.

If the table is truly describing a status, then there is absolutely
nothing wrong with an additional attribute that describes the group to
which it belongs. In fact, this model happens *all the time* in
relational design: it's called a many-to-one relationship.

Would you split /products/ into different tables based on their category?

And how about an argument apart from data modeling idealism:
Let's say we've got a function/method that builds a select box of statuses.

// different tables
function build($statustype)
{
    ...
    $sql = "select ... from ". $statustype ."_status";
    ...
}

// same table
function build($statustype)
{
    ...
    $sql = "select ... from status where class='$statustype'";
    ...
}

To me the first says something's wrong: it's a symptom of separating
data that should be in the same table.

Not to mention that having all those extra tables is going to make the
data model more difficult to get acquainted with, which drives up your
client's cost significantly.

- --
Matt Warden
Miami University
Oxford, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFEXjPprI3LObhzHRMRAhVeAKDSOsB2TDMNTogYGaHYUCi8eogqSACgqy2U
NRLUNit0/IhZsdmFk1rFgxA=
=LG+E
-----END PGP SIGNATURE-----



More information about the thelist mailing list