Re: Surrogate Keys: an Implementation Issue

From: JOG <>
Date: 28 Jul 2006 19:16:55 -0700
Message-ID: <>

J M Davitt wrote:
> JOG wrote:
> > J M Davitt wrote:
> >
> >>[big snip]
> >>
> >>
> >>>What does that have to do with the
> >>>fact that Bob just updated the wrong row?
> >>
> >>The wrong row? What was wrong about it? The database was
> >>told, for example, "Make Suzie's record look like this."
> >>
> >>The confusion arising because "Suzie is George and William
> >>is Suzie but Bob thought George was still Suzie" isn't
> >>going away just because you slap another value on all your
> >>records; you're just moving the confusion around.
> >
> >
> >
> > There is miscommunication in this thread. Brian is coming from an
> > assumption that primary keys are mutable, and everyone else from the RM
> > specification that they should be immutable. The primary key identifies
> > a tuple, and if something's identity changes... well, conceptually, it
> > is a completely different thing. Hence in the example you supply Brian,
> > the RM is responding perfectly and as we would want it to.
> A couple things: I'm not presuming that primary key values are
> immutable. I'm not sure what you mean when you say "from the RM
> specification that they should be immutable."
> I don't think I agree that a "thing" changes because one of
> its attributes changes. Yes, the tuple representing it does
> but the thing is the same. For instance, the change in the
> database may occur because the value for some attribute was
> incorrectly recorded; that doesn't mean there was a different
> thing. Or instance of an entity. Whatever

Ok, I understand your viewpoint, but I think its a mistake. Liebniz, whose definition of identity is key to formal logic, states that something is identifiable and distinguishable through its attributes, and its attributes alone. If a comparison is made between item 1 and item 2 and they have identical attributes, then they are the same thing. If all attributes are not the same then they are by definition different things, and the fact that the items being considered exist at different points in time makes no difference to this equality comparison. Thinking in terms of permanent 'entities' clouds this logic (which granted is unintuitive to everyday life), and hence the preference for thinking in terms of statements of fact alone.

> > It is the database designer and his choice of primary key that is
> > causing the problems. A person's name is an absolutely dire choice as a
> > key given they are _not unique_ and _subject to change_. That's exactly
> > why you guys have SSN's and I have an UK National Insurance ID. But
> > then to me an SSN isn't a surrogate anyhow, it's a unique attribute.
> I agree with everything in the above paragraph except that SSN
> is a unique attribute. Yes, the lack of readily available and
> reliable identifiers is troublesome - but it doesn't mean that
> any made-up identifier is going to solve the problems Brian
> was describing.
> >>Surrogates aren't going to solve the problems stemming
> >>from the fact that Bob is working on a stale and partly
> >>wrong representation of the data.
> >>
> >>
> >>
> >>>How could you prevent that in
> >>>application code, or in the middle-tier? You can't, unless you either (1)
> >>>lock the row until Bob gets back from Tahiti, or (2) use a surrogate to
> >>>guarantee that the row that's about to be updated is the same as the one
> >>>that was read out.
> >>
> >>Your list is too short; there are more alternatives. We
> >>don't have to wait for Bob and a surrogate isn't going to
> >>solve the problem without creating another problem.
> >>
> >>
> >>>>You are a vociferous ignoramus. It suffices to observe that fact.
> >>>>
> >>>>[remaining nonsense snipped without further effort]
> >>>
> >>>
> >>>
> >
Received on Sat Jul 29 2006 - 04:16:55 CEST

Original text of this message