Re: Normalization, Natural Keys, Surrogate Keys

>> ... we recieved this document which they called "detailed design"
that was supposed to describe the application they are writing for us.  Unfortunately, I firmly believe the company is blowing smoke and has sent us a bunch of ERD's, UML's and various descriptions of data theory. (There are no screen descriptions or workflow items.) <<

That sounds more like a conceptual design for just the database, and not a detailed design for the entire system. I'd like to see the applications represented in a DFD or structure chart at least.

>> ... they indicate that they are creating a databse design which
uses Natural Keys vs. Surrogate Keys because they "were necessary for the nomalization of the data model." The document went on to say, "natural keys are necessary to determine functional dependence and the efficient normalization of the the data
model." <<

Of course, you want to know what the natural keys are for data integrity, but why mention it? Was someone pushing to use Oracle's ROWID as the key in every table or something??

>> 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:

1. mycode <datatype> NOT NULL DEFAULT <value> CHECK (mycode IN (<list>)

for relatively short lists with constant values over time (sex, race, states, etc.)

 2) mycode <datatype> NOT NULL DEFAULT <value>

    REFERENCES CodeTable(mycode)
    ON UPDATE CASCADE for relatively long lists with values that are expected to change over time (stock ticker codes)

>> Can somebody please comment on this? Am I totally wrong? <<

You are not totally wrong. Of course it is hard to do diagnosis at a distance ... Received on Thu May 16 2002 - 16:08:32 CDT