Re: Database design, Keys and some other things

This is tough. The distinction is really clear to me, but I'm not sure the slightly ambiguous terminology of 'surrogate key' is helping us - we're using it in two senses, so i'm going to invent some terms for those senses to help with this post:

"substitute key" - a key made up by datbase D to represent items within
its own data. It is owned, generated and controlled by that database D.

"representative key" - an identifier that exists in the real world (by
which I mean it exists independently of D), and is controlled by some mechanism other than D.

Now Database D wants to store information about a set of cars and it needs to specify a primary key - it might use a VIN (a representative key) or it could use some internally generated key (a substitute key).

• If D uses a substitute key as a primary it can absolutely ensure that this key is unique and immutable.
• However if it uses a representative key it has no such assurance. Even a great representative key like a VIN, may be input incorrectly, may have been recorded on paper wrongly, the DMV's generating mechanism may have be faulty - who knows? At some point the DMV might even decide its time for a brand new scheme, and replace VINs completely.

All best, J. Received on Fri Sep 30 2005 - 10:39:43 CDT