Normalization, Natural Keys, Surrogate Keys

From: Kai Ponte <cybermusicdude_at_aol.com>
Date: 15 May 2002 16:25:10 -0700
Message-ID: <c1ec9b8f.0205151525.6dce24d2_at_posting.google.com>



My state (CA) is involved in a multi-hundred-million dollar project with a consulting company to re-write one of our county mainframe systems into a client-server system. (I probably should leave the names out.) Anyway, 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.)

The database being proposed for this applicaiton is Oracle. Fine enough. (I prefer MS SQL or MySQL, just because I'm used to them.) In one of the descriptions in the document, 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."

Now, I may not be a database genius, but this doesn't sound right. It may have been years since my last database design class, but I don't remember anywhere where surrogate keys or natural keys had any impact on the ability of a database designer to normalize a database.

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.

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

KP Received on Thu May 16 2002 - 01:25:10 CEST

Original text of this message