Re: Database design, Keys and some other things
Date: 30 Sep 2005 08:39:43 -0700
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