Re: Surrogate keys (Was: enums)

On Thu, Jan 19, 2006 at 09:37:12AM -0500, Pollard, Mike wrote:
> 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.

There is one thing to consider: consistency. If you mix and match
'natural' keys and surrogate keys as PK, then how do you know which one
you're supposed to be joining on? How does everyone else on the team
know?

Sure, there's many examples where you don't really need a surrogate key.
But there's just as many (if not more) where you want a surrogate key so
that you don't have to deal with the pain of a multiple-field key. (Note
that I don't consider simply defining a multiple-field key to be unique
as painful). So ISTM it's much easier to just use surrogate keys and be
done with it. Only deviate when you have a good reason to do so.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461