[thelist] DB Schema Design

Ian Anderson ian at zstudio.co.uk
Sun May 7 10:34:14 CDT 2006


Hershel Robinson wrote:

> But why, from a database or informational point of view, does this 
> answer the question? It may be the I agree with you (or that I don't)
>  but I am looking for a compelling reason to suggest one approach
> versus the other.

Well, the basic answer is that in my opinion you're using a single table
to describe unrelated attributes of different entity classes; in other
words you're merging three unrelated tables into one, just because they
happen to share a similar name.

You're merging product_status, order_status and customer_status into one
table, then adding a redundant field so you can find out which original
table you're querying on.

Like, you might as well have a categories table for products, customers
and suppliers.

id   category    class
1    electricals product
2    corporate   customer
etc

I don't think either of these is a good idea from a normalisation
perspective.

On the other hand, I don't think there is any compelling *practical* 
reason why the merged status is a terrible idea, either. Unlike, for 
instance, the "one table per page" idea someone came up with for a CMS a 
few months ago. Brrr...[shudder]

>> Also, I think you have to ask: if you have separate tables with
>> entities so similar that they share a single set of statuses, why
>> are they in separate tables?
> 
> We don't. Each status has one and one 'class' only. Entities do not 
> share statuses.

I think I failed to explain myself clearly. What I was thinking was that 
you may have too many tables, because if there is genuinely more than 
one table that shares the same "class" of status values from your 
statuses table, my suspicion is that the entities in those multiple 
tables must be so similar that perhaps they ought to be in a single table.

As I understood it the reason for suggesting this was that you had 
subsets of the 17 tables which would all share the same class of status 
values, is this not right?

This is partly why I was jocularly suggesting that the schema was 
"borked"* - that maybe the question is not whether this amalgamated 
statuses (statii?) table is good idea, but that its apparent necessity 
may be a symptom of some other problem in the schema.

Cheers

Ian

* See http://www.urbandictionary.com/define.php?term=borked. I was using 
the term in context of the second definition, as it turns out, and it 
wasn't meant unkindly.




-- 
zStudio - Web development and accessibility
- http://zStudio.co.uk
Snippetz.net - Your personal, private code library
- http://snippetz.net




More information about the thelist mailing list