Re: what are keys and surrogates?

From: Bob Badour <>
Date: Fri, 01 Feb 2008 16:47:34 -0400
Message-ID: <47a38567$0$4035$> wrote:

> JOG writes:
> [...]


>>I then realised that the entity
>>type I have in the real world (an individual bit of paper) might not
>>be the same type I've constructed in the model (a value written on
>>paper). I didn't need OID's to fix my problems...I just needed to make
>>sure my entities tallied with my constructs! And that meant I just
>>needed to use the right identifiers.

> I'm just trying to convince you that the requirement to state
> good natural keys doesn't somehow force you to get rid of OIDs.

JOG established the need for natural keys. You have not established the need for OID. A solution without a problem is no solution at all.

>>>The "trouble", as I tried to explain before, arises when a relation exists
>>>of which none of the keys can be decomposed (by following foreign key
>>>references) in such a way that only non-ID attributes remain.
>>The "trouble" is that you are probably stuck in thinking in OO, like I
>>once was when I thought C++ was the shizzle to end all shizzles.

> The trouble I see is that your answers tend to be based on guesses about
> my background, while you ignore the details of what I actually write.
> This is probably because I'm not that good a writer, for which
> I apologize, but it's still annoying.
> I've always agreed with you that the above model for Paper is broken
> when used as a database schema, when Value is not a key. I just
> don't agree that the paperID should go. It doesn't follow, in any case.

The point, you seem to keep missing, is something must connect what you are using in the database to what you are using elsewhere for the database to have any meaning elsewhere. Just adding a numeric identifier does nothing unless you somehow associate that identifier with something somehow recorded outside the database. In the example given, by recording it on the pieces of paper.

And vice versa. For the bits of paper to have meaning in the database, something must associate them with values in the database. Otherwise, they have no import.

If they are not important, that is perfectly acceptible.

>>Each "bit of paper" has already has a natural identifier of course, but we
>>can't use it (it's x,y path), and this is the root of our problem. And
>>yet all the issues can be solved one single swoop by just...
>>*Writing the paper_id in permanent marker on the back of each bit of

> :-) Well, at least you've acknowledged that there may be situations
> in which we may not (yet) have good natural keys, while I acknowledge
> that here we have a schema we need to fix.

Or requirements you need to clarify. If the bits of paper have no meaning, one can simply replace the value of the original relation with the value of the new relation.

   BTW exporting broken
> surrogates is the broken way out. Better find out the actual attribute(s)
> that make it necessary to distinguish these bits of paper, and record them;
> or find out that we don't really need to distinguish them after all,
> and make Value a key (e.g. add a Number attribute).

>>Now that the surrogate key isn't hidden, all problems vanish.

> But new problems may arise. We must now maintain this new attribute
> (e.g. mark the bits of paper and never change any of the marks)

That is not a new problem. It was always there.

> and whoever needs to identify bits of paper must store and recall
> these arbitrary marks. This is not always possible or practical.

It is always possible and practical when the bits of paper have meaning and importance. If they lack meaning and importance, there was never any need to identify them in the first place.

>>It represents the papers x,y path and every bit of paper is simply
>>identifiable, updates are trivial, and importantly the attribute is no
>>longer some magical OID or yucky tuple identifier fudge.

> Nothing yucky about magical OIDs, as long as they're used correctly.

Correctly used OIDS is an oxymoron. Received on Fri Feb 01 2008 - 21:47:34 CET

Original text of this message