[thelist] DB Schema Design

[Hershel Robinson]

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

Excellent point. This is the sort of input I was looking for.

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

You are saying that it is not possible to automatically enforce 
referential integrity due to the fact that the references to the 
statuses table must not only exist (normal referential integrity) but 
also must be of the relevant class. I see your point.

I am indeed not aware of any DB that would do this automatically. In 
particular MySQL 5. Of course this could be achieved programatically, in 
particular in light of the fact that this system is being implemented in 
an MVC framework where an on_insert action (and others) can be defined 
to check for such issues. Obviously there should be a performance 
advantage and potentially a maintenance advantage in leaving this 
functionality with the database engine.

Thanks,
Hershel

-- 
Gallery Robinson Web Services
http://web.galleryrobinson.com/