[thelist] DB Schema Design
Mattias Thorslund
mattias at thorslund.us
Mon May 8 11:09:57 CDT 2006
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
More information about the thelist
mailing list