[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