Re: Normalization, Natural Keys, Surrogate Keys

"--CELKO--" <71062.1056_at_compuserve.com> wrote in message news:c0d87ec0.0205161308.41fd487d_at_posting.google.com...
>
> >> 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
nightmare
> becuae every code winds up being stored as VARCHAR(n) and converted
to
> 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 contracts