Re: Normalization, Natural Keys, Surrogate Keys

Larry Coon wrote:

> Anton Versteeg wrote:
>
> > > 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.
>
> Why not? In the PARTS example I gave above, the 3NF form
> would be PARTS(part_no, warehouse_no, qty) and
> WAREHOUSE(warehouse_no, city), the WAREHOUSE table functions
> as a code table.
>
> > Using a single code table as opposed to multiple tables or other
> > constraints like field check constraints has advantages and
> > disadvantages. If I understand you correctly they want something like
> > (code_type, code_value, code_description).
> > Advantage is that you can easily add new code types without changing the
> > data model. This works best if your codes all have the same or almost the
> > same data type. A big disadvantage is that you need to store the
> > code_type in your other data together with the code values.
>
> How so? Code tables are good for domain enforcement when
> the domain is large and/or changes. So for a car database:
>
> COLOR
> code color
> ---- --------
> 12 Beige
> 23 Silver
>
> And the data tables store 12 or 23. The advantage is that
> when Marketing changes "Beige" to "Autumn Bronze" and "Silver"
> to "Moon Mist" the change is localized, and when the domain
> of colors changes, the change can be reflected in the database
> via DML in the code table.

I fully agree that code tables can be very handy. I was trying to say that a single code table covering multiple code types is not the way to go.

>
>
> > This not only
> > increases the amount of data you need to store but also complicates the
> > databse and application design. What to do if a table has several
> > different code types?
>
> Several different code tables.
>
> > Specially if many of the codes just have a few
> > valid values like Y/N this is in my experience not the way to go.
>
> Right.
>
> > If your company cannot review the design, I would ask a third party to do
> > a review.
> > My 2 cents.
>
> Best advice yet. Sounds like a potential fiasco in the
> making.
>
> Larry Coon
> University of California
> larry_at_assist.org
> and lmcoon_at_home.com

--
Anton Versteeg
DB2 Specialist
IBM Netherlands