Re: Surrogate Keys: an Implementation Issue

From: JOG <>
Date: 30 Jul 2006 05:30:56 -0700
Message-ID: <>

This discussion has illuminated me, and I would like to share that. A row is of course merely a proposition, and is identified by the predicate fields that make each proposition in that set unique. A surrogate key similar helps us identify a particular row, so there initially seems no problem there, and its not an addressing issue. Rather the issue is that the surrogate _does not exist_ in the real world, it is initially an artifice of the database, and is not part of the original proposition.

So whats it doing there? Consider two people with the same names:

Ex ( Name(x, "Sarah Jones") & Marital_status(x, married) ) & Ey ( Name(y, "Sarah Jones") & Marital_status(y, married) ) & x != y

We don't have sufficient information to distinguish x and y (note this specifically refers to the propositions, not the conceptual entities involved). Perhaps this distinguishing information is impossible to record, or simply cost ineffective to do so. Anyhow, If we had a mechanism that seperated propositions x and y (an OID for example), then how on earth would we know which one to update when the time came? (Obviously we couldn't, and thats part of the problem with OID's.)

But are surrogates in RM any different? My initial opinion was that the answer was NO, that they seem to be no different to OID's, except in their encoding. However gradually my opinion has changed. The reason for this is that while an OID is always physical, a surrogate key leaks into the real world almost as soon as it is generated. While it is initially an artifice, it quickly becomes a real attribute, and that validates it's use as part of the logical model. Hence the rows become:

Ex ( EmpID(x, 1) & Name(x, "Sarah Jones") & status(x, married) ) & Ey ( EmpID(x, 2) & Name(y, "Sarah Jones") & status(y, married) )

Noone can tell you what their OID in some database is, but they may well be able to tell you what their employee/payroll number is.

It hence seems to me that it is the leak in to the real world that is absolutely _vital_ to validate surrogate use. Received on Sun Jul 30 2006 - 14:30:56 CEST

Original text of this message