[thelist] DB Schema Design

Ian Anderson ian at zstudio.co.uk
Mon May 8 04:31:16 CDT 2006


Matt Warden wrote:

> 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. 

But you then ought to normalise that column, surely?

The scenario is that we have a table like this, as I understand the 
discussion:

tbl_statuses
ID Status    Class
1  On order  product
2  Declined  payment
3  Not live  customer
4  In stock  product
5  Live      customer


This table is not normalised: the values for class should be moved out 
into some other table and related with a foreign key.

And the values in this table would then need to define which table in 
the database the associated statuses belong to:

tbl_classes
ID  Classname  Table
1   product    tbl_products
2   payment    tbl_payments
3   customer   tbl_customers

I dunno. Even then, I think it's bad design.

Suppose you want to add a constraint to one of the main tables - like 
requiring that the foreign key tbl_customers.statusID  is a current 
value from tbl_statuses.ID, and someone adds a status ID of 4, which is 
actually one of the statuses intended for use by tbl_products.

There is no straightforward way for the database to catch this error, 
because of the way the schema is set up.

I think you should have separate status tables for each main table - 
then constraints can work as intended.



-- 
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