Re: Normalization, Natural Keys, Surrogate Keys
Date: Thu, 16 May 2002 15:35:13 -0600
"--CELKO--" <71062.1056_at_compuserve.com> wrote in message
> >> To make matters worse, they later describe the use of a "code
> table" to store various codes. This is something I've often seen in
> IMS and various btrieve-based databases and I don't believe belongs
> anywhere in a relational model. <<
> Amen. It is a violation of First Normal Form (1NF); the column with
> the value is not one kind of attribute. In practice it is a
> becuae every code winds up being stored as VARCHAR(n) and converted
> temporal, fixed length strings, numerics, etc. over and over for the
> conversions. A good rule of thumb in the schema design stage is:
There's also a potential performance issue with a 'code table' (besides the very good point you raise regarding the typing of data) whereby all access for codes is single-threaded through one table: hot spot.
There's also the issue of data integrity. By creating an 'object table' (which is what a code table is), we potentially lose vital business information about the data.
-- Pablo Sanchez, High-Performance Database Engineering mailto:pablo_at_hpdbe.com http://www.hpdbe.com Available for short-term and long-term contractsReceived on Thu May 16 2002 - 23:35:13 CEST