Re: pervasiveness of surrogate (also called synthetic) keys

On 03/05/11 08:25, Jeff Davis wrote:

> You can generate your own keys, and if you hand them out to customers
> and include them on paperwork, they are now a part of the reality that
> your database models -- and therefore become natural keys. Invoice
> numbers, driver's license numbers, etc., are all natural keys, because
> they are known about, and used, in reality. Usernames are, too, the only
> difference is that you let the user choose it.

I've repeatedly run into situations where I generate a key that seems
entirely sensible, making a generated primary key part of the business
processes ... then external constraints force me to change the format of
that key or start accepting keys from outside. "Oh, we need to move to
14-digit client IDs because <x-system> that we interact with requires
them". "We want invoice numbers to include a Luhn check digit, can you
add that?". Etc.

I'm now strongly in favour of keeping an internal key that users never
see, and having separate user-visible identifiers. The users can demand
that those identifiers change format or generation method and it's an
easy change in only one place. Want two different keys? I can do that
too. Record a key that matches some external system? That's easy. Want
to be able to edit/override/rename keys? Yep, that's fuss free too, and
it won't affect my audit history (which uses the real internal keys) or
have to cascade to foreign key relationships in append-only ledger tables.

I use a mix of surrogate and natural keys, depending on the situation. I
see little point in surrogate keys for simple lookup tables, but find
them invaluable in audited tables with lots of foreign key relationships
that interact with other business systems.

--
Craig Ringer