Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Normalization, Natural Keys, Surrogate Keys

Re: Normalization, Natural Keys, Surrogate Keys

From: Pablo Sanchez <pablo_at_dev.null>
Date: Thu, 16 May 2002 15:35:13 -0600
Message-ID: <3ce42440_8@news.teranews.com>

"--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
Received on Thu May 16 2002 - 16:35:13 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US