Re: Normalization, Natural Keys, Surrogate Keys

From: Kai Ponte <>
Date: 17 May 2002 07:24:59 -0700
Message-ID: <>

"Rich Dillon" <> wrote in message news:<eiWjfwR$BHA.2384_at_tkmsftngp02>...
> So what if we number the lines. Let's assume that this number represents a

>It should be clear though that this new scenario is
> little better than the old for the obvious reason that the # does not say
> anything about the physician -- it only describes the line on the paper.
Valid point...

> In the end, though,
> something which is known in the world outside of the database needs to be
> declared as a key even if you've generated it yourself.
Which, of course often happens, even in my data models. I often use a unique key which is known. Either a document number, an account number, a date code, or some variation. You're right, however, that this becomes a morphed "surrogate" key and is read as a "natural" key.

I should explain that I have no problem using natural keys where applicable. For example, in creating a database to be used by my old company to look up student records at the Cal State univerities, I utilized the student's ID number as one part of the primary key for the records. I wanted to do that here at the county for closed welfare cases, however I wasn't able to because there were too many duplicate and invalid SSN's. I was then forced to create a surrogate key for the case table.


Kai Received on Fri May 17 2002 - 16:24:59 CEST

Original text of this message