Re: Normalization, Natural Keys, Surrogate Keys

On Mon, 20 May 2002, bap_at_shrdlu.com wrote:

> The surrogate key has its uses (others disagree on that) but you
> do have to recognise that there are risks. Any move away from the
> logical data structure has a risk. It's perfectly possible to
> take two copies of the same datum and attach two different
> surrogate key values.

Well, its perfectly possible to attach the wrong ssn to a person's row. How can you be assured that the correct ssn gets tagged to the correct person? The arguments against surrogate keys haven't helped me change my mind, although I wasn't the one asking. It should be quite obvious that surrogate keys are there as dummy keys so don't act like a dummy and assume that their uniqueness defines the true uniqueness of the row.

> If you allow this to happen then your data has been corrupted, and
> corrupted in a way that's not immediately obvious.

What happens if a customer id is email address? There are apps around where you can't get a natural, non-changing primary key for the customer. But, a completely logical identifier for, say, an online app could be an email address, or maybe even a credit card. But, this identifier can change, or they could have more than one. What do you do then?

Or, is the whole surrogate/natural argument centered around creating keys for data that already has "natural" keys and has nothing to do with new apps/data?

> Creating a surrogate key does not absolve you from the need to
> identify a true key if it exists, and to check its uniqueness in
> your database.

Bingo!

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.