Re: Surrogate keys (Was: enums)

Martijn van Oosterhout wrote:

> Please provides natural keys for any of the following:
>
> - A Person
> - A phone call: (from,to,date,time,duration) is not enough
> - A physical address
> - A phone line: (phone numbers arn't unique over time)
> - An internet account: (usernames not unique over time either)

Ahh, a challenge. Hmm, not sure about all of them, but here goes:

A Person - well, you could use a bit map of their fingerprints, or maybe
their retinal scan. Of course, that could change due to serious injury.
Maybe some kind of representation of their DNA?

A physical address - how about longitude/latitude/height from sea level?

The point here is two-fold. First, what we call 'natural' is frequently
itself a surrogate key (yes, even your name is really just a surrogate
key. As with all surrogate keys, it is a sequence of symbols that you
use to represent yourself). The second point is even when you find a
truly 'natural' key (something not arbitrarily made up by anyone, and
uniquely identifying the data in question), it may be completely and
utterly inappropriate to use in a database.

What is 'natural' anyway? If someone phones in an order, we usually
assign an order number to that request. This order number is not the
actual order, and the customer couldn't care a less what it is, but I've
never heard a DBA argue we should get rid of it (well, to be fair, I've
never discussed order numbers with a DBA at all). After all, would it
make sense for the key for that order to be the customer's name, the
date/time of the order, all the items ordered, and the address to ship
the order? That isn't a key, but it's the only 'natural' thing that
identifies that order that immediately comes to my mind.

On the other hand, would anyone argue that an order_item table should
have a surrogate key? Well, I wouldn't. The key for the order_item
table should be something like the order number and the inventory item
number together (IMHO).

The point? Surrogate keys and natural keys are two tools in the
database arsenal. Just as it is unwise to use a hammer to drive a screw
just because you don't believe in screwdrivers, it is unwise to just off
hand discard either method of specifying a key. Rather, use
intelligence and education (one of which is discussions such as this) in
deciding how best to represent your data to aide in performance, ease of
use, and adaptability.

Mike Pollard
SUPRA Server SQL Engineering and Support
Cincom Systems, Inc.