Re: Normalization, Natural Keys, Surrogate Keys

>> Without sounding too dim, what you just said has confused me. It
seems like
you're denormalising the schema here? Are you saying that you shouldn't store a list of codes in a separate table (providing they're not supposed to change)? <<

Whar we would really like is to have a CREATE DOMAIN statement that would give us a domain for the attributes at the scheam level. Thus, ALL zipcodes would checked and validated the same way, UPC/EAN numbers would be the same, etc. byt virtue of being declared to be of that type.

Well, we don't have that in most SQL products, so we can use CHECK() constraints or REFERENCES clauses to ensure that an attribute has only valid values. My rule of thumb -- and that is all it is -- is to put short lists of
constant values into a CHECK() clause and long lists of varying values into a auxillary code table. But use one and only one table per code; if a table has more than one code, then it is not normalized because columns have more than one meaning. Received on Mon May 20 2002 - 12:11:44 CDT