Re: ID field as logical address

From: Walter Mitty <>
Date: Mon, 08 Jun 2009 14:06:05 GMT
Message-ID: <hV8Xl.1375$>

"Bernard Peek" <> wrote in message
> In message
> <>, JOG
> <> writes

>>On Jun 6, 5:50 am, "Brian Selzer" <> wrote:
>>> "JOG" <> wrote in message
>>> > On Jun 5, 2:10 pm, "Brian Selzer" <> wrote:
>>> >> "JOG" <> wrote in message
>>> >> <snip>
>>> >> > At risk of repeating myself, S# has merely proven to be a bad key
>>> >> > again - it is clearly an unstable identifier for a supplier (just 
>>> >> > as
>>> >> > 'name' was in the 'divorcee' example). This is just another flawed
>>> >> > schema, not a problem with the RM.
>>> >> I didn't say it was a problem with the RM. I said it was a problem 
>>> >> with
>>> >> Date and Darwen's notions that a database is a collection of relvars 
>>> >> and
>>> >> that insert, update and delete are shortcuts for relational 
>>> >> assignments.
>>> >> You're underscoring my point, by the way, which is that adopting 
>>> >> those
>>> >> notions requires that every instance of every key be a permanent
>>> >> identifier
>>> >> for something in the Universe of Discourse
>>> > Yup, if you talk about something in a proposition use a stable
>>> > identifier for it. It's not just desirable, but essential. Use a nice
>>> > stable EMP# not a person's name. It is about integrity not
>>> > 'expressiveness'.
>>> It is not essential. Language terms can denote different things at
>>> different times. "The President of the United States" is Barack Hussein
>>> Obama now, but was George Walker Bush just five months ago.
>>Those are not merely 'language terms'. The "President of the US" and
>>"Barack Obama" are different things, with different properties. The
>>fact that they currently happen to coincide is what is confusing you
>>(imo of course).
>>It is /essential/ one knows which of those things one wants to keep
>>track of in order to pick a key that will be stable over time, and
>>hence construct a schema that will maintain integrity over time. If
>>you are concerned with the "person" then that should be the chosen
>>key, and their "post of office" will change over time. If you are
>>concerned with the "post of office" then that is the key, and the
>>"person" holding that position will change over time.

> Let's get metaphysical. There is an attribute of every unique object
> called "Identity." This is a non-numeric dimensionless constant. What
> makes this difficult to deal with is that there is no function that can be
> applied to {Identity} which returns a meaningful text string.

> We therefore choose a range of surrogate keys which can be manipulated as
> text strings and to a greater or lesser extent map 1:1 to the Identity
> value. In some cases we have natural keys where the mapping is enforced by
> the laws of physics. In other cases we issue an invented value to identify
> an object, and we attempt to maintain the 1:1 mapping by processes that
> take place outside the database. So we issue a National Insurance number
> and tell the person it identifies to remember on pain of dire
> consequences.

> In every case that I can think of the mapping is maintained by processes
> that are outside the database and outside the relational model. The
> relational model takes it as axiomatic that this mapping is somehow
> maintained. It does not deal with how it is maintained.

> In this it is no different from any other branch of algebra. If an
> equation asserts that 3X=6 then we assume that all three values of X are
> identical and map on to the same value. In relational algebra it is
> assumed that whatever {Identity} maps to {Key} is always the same.

> What we are discussing in this thread is pathological conditions where we
> assume that the mapping may change over time. This is essentially the same
> problems as we would face if we tried to perform simple algebra when a
> variable can have multiple different values at the same time and in the
> same equation.

> --
> Bernard Peek


Your reply is crystal clear except for one minor point. It's not clear (at least to me) who the pronoun "we" refers to in the phrase "we therefore choose a range of surrogate keys."

It seems to me that, in some cases, the people who choose surrogates and assign a new surrogate value to a newly discovered entity instance are "outside the application", such as a hiring clerk in HR assigning a new employee badge number to an employee on his/her first day of service. In some cases, the assignment is "inside the application, but outside the dataabase". In these cases, HR sees the new badge number as having been assigned by some process behind the data entry screen. But the programmers see the assignment has having been done by their programs.

In yet other cases, the database designer has chosen to use an autogenerated datatype for the column that holds the surrogate. This might be called "inside the DBMS but outside the database". But to most people, inside the DBMS and inside the database are tantamount to the same thing.

So, what do you mean by "we", kemosave? Received on Mon Jun 08 2009 - 16:06:05 CEST

Original text of this message