Re: Normalization, Natural Keys, Surrogate Keys

From: Pablo Sanchez <pablo_at_dev.null>
Date: Thu, 16 May 2002 15:35:13 -0600
Message-ID: <>

"--CELKO--" <> 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
Available for short-term and long-term contracts
Received on Thu May 16 2002 - 23:35:13 CEST

Original text of this message