Re: Surrogate Keys: an Implementation Issue

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Thu, 20 Jul 2006 14:28:45 GMT
Message-ID: <xqMvg.12797$pu3.293621@ursa-nb00s0.nbnet.nb.ca>

Paul Mansour wrote:

> Bob Badour wrote:
>

>>Paul, I think you are confusing the logical level and the physical
>>level. Whether one uses some kind of pointer (or any other structure)
>>that is hidden from the user is entirely a physical consideration.

>
> Bob,
>
> As I noted to paul c, perhaps the choice of "surrogate key" was a bad
> idea. I think I understand the importance of keeping the logical and
> physical levels separate. But when you sit down to actually implement
> somthing, it seems one must think of both at the same time.

If the user never sees it and can never interact directly with it, then it is not part of the logical level of discourse. At that point, it becomes a physical structure only.

> In the particular DBMS implementation I'm working on I need an
> immutable identifer for each row due to rollback and audit
> requirements. The implementation of this immutable identifier is just a
> system column for the table, so it can do double duty if the user has
> not defined a natural key for a table, and it can be exposed as an
> auto-incrementing surrogate key. So for most tables it is not exposed,
> but for some it is.

The auto-incrementing column is a logical attribute. Using your physical structure to implement the logical attribute does not magically transform the physical structure into a logical attribute; even though, one might have a 1:1 mapping between the values of one and the other.

> I'm hoping that in the result that is presented to the user I have not
> confused the physical and logical models.

If the designer/user requests an auto-increment attribute and specifies a name for it, providing one doesn't confuse anything. If the designer/user never requested the attribute, exposing the physical structure anyway would confuse a lot. Received on Thu Jul 20 2006 - 09:28:45 CDT