Re: Database design, Keys and some other things

From: JOG <>
Date: 30 Sep 2005 08:39:43 -0700
Message-ID: <>

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 - 17:39:43 CEST

Original text of this message