[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