[thelist] DB Schema Design

[Mattias Thorslund]

Hi all,

Interesting discussion. I'm using a single "status" table to consolidate
hundreds of types of look-up items. Putting these in separate tables
would have meant, yes, hundreds of mini-tables with the exact same
structure (ID, Description), each containing between 3 and 10 records,
mostly.

I understand that at this level of similarity, the code to maintain the
lookup items is similar enough to be consolidated into an abstract
function for all the "status-like" tables. But littering the database
with hundreds of little tables makes no sense to me.


Ian Anderson wrote:
> 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.
>   

The way I do this is I include the "StatusTypeID" column in the primary
key. Like so (slightly simplified for readability):

CREATE TABLE Statuses (
StatusTypeID int,
StatusID int,
Description varchar(50),
PRIMARY KEY (StatusTypeID, StatusID));

This means I have to generate my own sequences (once solved, works
everywhere), but it allows the freedom to assign IDs according to a
different strategy when called for.

Cheers,

Mattias