Re: Surrogate Keys: an Implementation Issue


"Bob Badour" <bbadour@xxxxxxxxxxxxxxxx> wrote in message
news:PPMxg.21411$pu3.353655@xxxxxxxxxxxxxxxxxxxxxxxxxx
Brian Selzer wrote:

"Bob Badour" <bbadour@xxxxxxxxxxxxxxxx> wrote in message
news:X1gxg.15744$pu3.340040@xxxxxxxxxxxxxxxxxxxxxxxxxx

Brian Selzer wrote:


"Bob Badour" <bbadour@xxxxxxxxxxxxxxxx> wrote in message
news:XQ5xg.14298$pu3.333248@xxxxxxxxxxxxxxxxxxxxxxxxxx


Brian Selzer wrote:


"Bernard Peek" <bap@xxxxxxxxxx> wrote in message
news:xn0ep1e3sn11oo000@xxxxxxxxxxxxxxxxxxxxxx


In comp.databases.theory Paul Mansour wrote:


The second is perhaps more profound. Consider a rollback database,
or
a database that must provide a complete audit trail of every change.
For example, the database must provide the answer to "who changed
this SS number from X to Y, and when did they change it? As far as
I
can tell, if there is no way to answer this without an immutable
identifier. ( I suppose you could design the DB to handle specific
cases, but I'm interested in DBMS with native rollback and audit
trail support.)

If what you think of as the key is mutable it's not a natural key.
Natural keys aren't mutable, at all, ever.

Untrue! Natural keys are often mutable--especially compound keys.


The problem here is that there isn't a usable natural key for
identifying people. So all that's left are surrogates of varying
quality. The SSN is a surrogate that works most of the time. If you
assign someone a payroll number and tell tham that thay won't get
paid
unless they can quote it then you have a close to immutable key. But
it's still a surrogate.

I think the original post was part correct. Use a natural key when
you
have one. I'd just add to that, don't use a surrogate unless there is
a
compelling reason for it. Efficiency is unlikely to be a compelling
argument in most cases.

There is always a compelling reason to use surrogates: natural keys
can change. This makes it difficult--if not impossible--to detect
changes to rows. For example, Bob is preparing to update a row--that
is, he has read the row and is in the process of keying in a change.
During that time, another process updated several rows in the same
table. Unless the key is immutable, when Bob issues the update,
there's no guarantee that the row he's updating is the same one that
was read out.

So? Bob issues an update statement to change some attribute identified
by a logical identifier. As long as the data Bob is entering is
correct, why should he care what happens to any other attributes?

He may be overwriting a change made by another user.

The only relevant concern is whether the update is correct. If the update
is correct, it matters not at all that it overwrites something, which is
presumably no longer correct.



This problem is magnified


if there are rows related via a foreign key constraint because it's
possible for the referenced row to appear unchanged. So you're left
with either maintaining an exclusive lock on the row until Bob returns

from the golf outing, or adding additional columns and code in order
to

determine with certainty whether or not a change occurred between the
time that a row was read and the time of the update.

Or you can just write better applications that don't update anything
that didn't change. By introducing some 'under-the-covers' identifying
attribute, you create a risk that Bob will change some data identified
by a familiar logical identifier and some other process in the meantime
will associate that identifier with a different surrogate. Your
application will then record the updates against the wrong logical
identifier.

You're missing the point. The change Bob's making may change the row,
but because several changes occurred to the table while Bob was keying
in his change, the row he's about to change may represent some other
entity altogether.

Using logical identity and natural keys, that's not possible. The key
that Bob specifies identifies the entity. And if Bob updates the entire
row, the entire row needs updating. I draw your attention, in particular,
to my observation that one can just write better applications that don't
update anything that didn't change.

Of course it's possible. The key that Bob specifies identifies the
entity at the time of the read from the database. By the time that the
write occurs, that same key now refers to a different entity.

Are you stupid or just ignorant? What you state above can only happen if
one assumes one is using a hidden surrogate. If one uses explicit values
that Bob interacts with, then the value identifies a single entity. That
identity does not change.


From Bob's perspective, what he would expect is that the entity that he's
working on, identified by the key values he used to retrieve it, would not
change; however, since the database is no longer the same when Bob attempts
to commit, the entity that he was working on may either no longer exist, or
may have been changed by another user. During that same interval, a second
entity may have been changed so that by the time Bob attempts to commit,
that second entity may be identified with respect to the new database state
by the exact same key values that Bob read out.


The logical
identity in the database value that existed at the time of the read is
different from the logical identity in the database value that exists at
the time of the write even though their atomic values are identical.
Logical identity within the value of a database at a specific point in
time can be represented by a natural key.

You are a crank. Plonk.

[longwinded idiotic nonsense snipped]


The only time Bob's change could update the wrong entity is if the
application Bob is using identifies the 'row' using a surrogate key that
Bob cannot see.

I don't think you understand the problem. See above.

I don't think you understand relatively plain english. I won't bother
directing you to anything. Since you were incapable to understand it the
first time, I have little hope you ever will.


I understand fine: You're wrong. You know you're wrong, but don't have the
stones to admit that you're wrong, so you resort to name calling. Typical
adolescent behavior.


The Relational Model doesn't take into account duration

You have yet to establish that it needs to.

, so the value of a

database at the time of a read is not necessarily the same as that at
the time of a write

This only matters if one writes shitty applications with sloppy code. See
my earlier posts.

No. The integrity of the database should not depend on the quality of
application code.

With all due respect, the dbms can only do so much. If a willful ignorant
like yourself is determined enough, he can write shitty sloppy code that
forces garbage into the dbms. All he has to do is make updates that were
never requested by users exactly as you propose when constructing your
straw man.


Haven't you ever heard about constraints? Also, the updates that occur
between the read and the write would have to be valid, otherwise one or more
intermediate database states would be inconsistent. The problem is that the
key values read out no longer reference the same entity at the time of the
write.


The database should reject changes that would cause it to
become corrupt, so shitty applications and sloppy code are not relevant
to this discussion.

To answer your stupidity, I would first have to teach you what an external
predicate is. Since that is extremely basic and since you have failed to
learn that before spouting nonsense, you don't deserve any such remedial
lesson.

I know what an external predicate is. What does that have to do with the
fact that Bob just updated the wrong row? 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.


You are a vociferous ignoramus. It suffices to observe that fact.

[remaining nonsense snipped without further effort]


.