[thelist] DB Schema Design

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

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

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


More information about the thelist mailing list