Re: Newbie question

Jon Heggland wrote:
> advantages of surrogate keys vs. natural keys are that they are stable
> (I.e. they don't change), and they are never unknown.

With SQL DBMSs (which force some physical aspects to follow directly from the logical model) there can also be advantages to using a "smaller" (e.g. a single 8 byte int vs a 20 char string or a combination of 2/3 attributes) domain for the key, as it simplifies, and may speed up, comparisons on FKs/joins (and might even save some space too).

I wouldn't overdo this "optimisation" though -- the use of a surrogate key purely to avoid a 2/3-attribute compound key is often a pessimisation which makes it much harder to express, and thus enforce, constraints such as "the customer to which the invoice applies must be the same as the customer to which the receipt applies" which are trivial to enforce if we just use (cust_id, invoice_id) and (cust_id, receipt_id) as the PKs of the invoice and receipt tables.

> (But that does not mean that you shouldn't record any natural key(s) as
> well, and designate them as keys, too. The distinction between
> "primary" and "alternate" keys really *is* bogus.)

Agreed -- most of the damage done by the use of surrogates is due to designers then thinking "job done" and failing to define/enforce the uniqueness of the natural keys.

> Note that the use of surrogate keys does not force you to present them
> in the user interface. Natural keys are often better for that, but they
> are not mutually exclusive in that regard.

Moreover, as Joe Celko often points out, any key that is used in the UI should have check digits or similar to catch data entry errors -- which makes SQL Server's IDENTITY and equivalent constructs pretty much unusable (directly) for such surrogate keys.

Misha Received on Mon Jun 20 2005 - 21:46:33 CEST