Re: Normalization, Natural Keys, Surrogate Keys

> As an aside, I've not seen a clear guide for the use of the terms
"surrogate
> key" and "natural key" to describe what are really four kinds of keys --
> keys for internal use by the dbms, keys which are declared in the data
model
> but not used by applications (other than for joining) and are unknown to
the
> outside world, keys which are generated but externally used (account #s,
> invoice #s, etc), and keys which are natural attributes of the entity
being
> modeled (from names to DNA). I've been using "surrogate" to represent the
> second of these and "natural" to cover the third and fourth.

I'm not sure if I'm right here, but I've got a feeling that surrogate keys tend to become natural keys once they start getting used by the business. Most natureal keys I see are unique numbers/alpha-numeric sequences, which makes me wonder whether they started out as surrogate keys.

I've recently worked on a project where a developer had assigned surrogate keys to all entities. One entity actually already had a 'real world' (or natural ) key, but this was ignored. Within 2 weeks of working with the client, we now have to clarify whether we're talking about the original 'natural' key, or the newly created 'surrogate' key.

IMHO this is a nightmare. A 2 minute design decision is costing us 5 seconds each time we talk about business entites - just clarifying keys. To me this is unwelcomed complexity, to which there is little gain.

I think the point is to think about your surrogate keys carfully, not just assign them as habit (which many do). I believe that if you can find an existing natural key, then use it!

Tobin Harris Received on Thu May 16 2002 - 17:13:31 CDT