Re: Newbie question

In message <MPG.1d2096e332422a4f9896a6_at_news.ntnu.no>, Jon Heggland <heggland_at_idi.ntnu.no> writes

>I'm not sure why you think surrogate keys are not acceptable---they are
>a staple of relational database design---but I'll assume you think they
>are useless in the presence of natural keys (which is the term for keys
>that come from the "real world").

I haven't found anyone who says that surrogate keys are unacceptable. They do have disadvantages though so I would usually suggest trying to use natural keys wherever possible.

>Off the top of my head, the main
>advantages of surrogate keys vs. natural keys are that they are stable
>(I.e. they don't change),

That is one of their failure modes. If the natural key changes then any surrogate keys should change too. If they don't then the system is badly broken.

> and they are never unknown. And of course:
>It's not always *possible* to find a reasonable natural key.

That happens quite a lot.

>
>(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.)

True.

>
>Anyway, many "real-world" keys are really surrogates---SSNs, account
>numbers, registration numbers, student numbers and so on. They are bogus
>counters (even if they have some internal structure (which in most cases
>is a bad idea)).

Also true. Their value as a key is essentially that we can trust the issuing authority to maintain a 1:1 mapping between key values and the objects they are supposed to identify. The key to using surrogates safely is to make sure that there are adequate systems to maintain that 1:1 mapping. Quite often those systems are outside the database.

-- 
Bernard Peek
London, UK. DBA, Manager, Trainer & Author.