[thelist] DB Schema Design

Hershel Robinson hershel at galleryrobinson.com
Sun May 7 10:27:28 CDT 2006

I am working in partnership with someone designing a schema (for MySQL 
5) for a new PHP/MySQL web app. There are many different entities in the 
DB that need to retain a status (products, orders, customers etc.)

The plan that the other designer has is to use one table called statuses 
with three fields: id, name, class. This one table will be used for all 
statuses in the system. The name field will be the name of the status 
(on hold, active, shipped, factory, etc.) and the class field will hold 
for what type of object this status is relevant. So 'on hold' and 
'active' might be relevant for products and 'shipped' and 'factory' 
might be relevant for orders.

This is opposed, of course, to having a single table for each type of 
entity, i.e. one for product statuses, one for orders statuses etc.

The schema is between 80 to 90% finished and has 72 tables. Presently 18 
of those tables have a foreign id field called status_id; therefore to 
make a unique status table for each one would add another 17 tables (we 
already have one generic statuses table).

Any opinions on which of the above approaches seems better? That is, to 
use one large table for all statuses or 18 small tables, one for each 
type of entity, each not then requiring a class field.


Gallery Robinson Web Services

More information about the thelist mailing list