Re: ID field as logical address

From: Walter Mitty <wamitty_at_verizon.net>
Date: Sun, 31 May 2009 07:12:59 GMT
Message-ID: <%5qUl.1884$9L2.198_at_nwrddc02.gnilink.net>


"Brian Selzer" <brian_at_selzer-software.com> wrote in message news:O4mUl.12059$im1.3878_at_nlpi061.nbdc.sbc.com...
>
> "Walter Mitty" <wamitty_at_verizon.net> wrote in message
> news:fv9Ul.1465$Cc1.1327_at_nwrddc01.gnilink.net...
>>
>> "paul c" <toledobythesea_at_oohay.ac> wrote in message
>> news:vdUTl.29814$PH1.1299_at_edtnps82...
>>> Just a couple of comments about a post that is unusually vague and fuzzy
>>> coming from you, it's tedious to dismantle every sentence, so I mention
>>> only a couple:
>>
>> I apologize for how vague and fuzzy this topic is. If my wording is
>> vague and fuzzy, my thinking is even more vague and fuzzy.
>>
>> The first thing is whether or not I'm talking about a tuple within a
>> tuple. This was a digression. As far as I'm concerned, the difference
>> between the following two is not fundamental:
>>
>> (714, Joe, Friday, 234-5678)
>> (714, (Joe, Friday,234-5678))
>>
>> At least not fundamental for the purposes of figuring out how "714" comes
>> to be associated with Joe Friday and not with some other policeman.
>>
>> Next, the question arises whether the association is managed by the
>> database or whether its managed programatically or by some external
>> function, manual or automatic.
>> Some responder addressed precisely that question. I think it was
>> Bernard. In my experience discussing this topic with fans of the ID field
>> concept, I've found that, contrary to Bernard's opinion, they turn over
>> management of the association to the database. I smell a rat at this
>> point, because I think they are asking a database to perform a function
>> that is not really a database function. I think that assigning 714 to
>> Joe Friday is inventing data, and it's not the database system's job to
>> invent data. It's the database system's job to manage the data it's
>> given. That's not to say that some popular tool like MS Access might not
>> do it anyway.
>>
>> And it's not clear to me whether MS Access is really a DBMS. I think
>> it's an application management system.
>>
>> I think the process by which 714 comes to be associated with Joe Friday
>> is actually somewhat mysterious. Googling "Badge 714" confirmed this
>> opinion. BTW, I an NOT using "mysterious" as a code word for "mystical".
>> The one thing I can say is that it's not a consequence of the data. It's
>> either part of the data as given, or it's part of some fairly arbitrary
>> process.
>>
>> Next, my statement of the issue is fogged by my own ignorance of
>> relational theory. My introduction to relational theory was only as a
>> backdrop to my introduction to database design. At the time, I was
>> learning how to design SQL databases, which all of us called "relational
>> databases". I don't recall if I heard Ed Codd's name, but I know I never
>> read anything he wrote until much later. This informal and spotty
>> education notwithstanding, I continue to view my own interpretation of
>> relational theory to be largely orthodox, when compared to some of the
>> crazy things that have been accepted as "best practices" in database
>> design.
>>
>> Anyway, how would relational theory decribe the operation that I called
>> "UPDATE" (using the SQL keyword intentionally.)? would it be something
>> like the following?
>>
>> relvar R gets assigned relvar R minus relation(tuple T) plus
>> relation(tuple U).
>>
>> How does this differ from a DELETE followed by an INSERT?
>>
>> I don't think this post clears up any of the fuzziness. But maybe it
>> lays the groundwork for clearingf it up.
>>
>
> Many on this newsgroup adhere with blind zeal to Date and Darwin's
> interpretation of relational theory that has at its roots the notions that
> a database is a collection of relation variables (relvars) and that
> insert, update and delete are somehow "shortcuts" for relational
> assignments. They refuse to recognize the limitations these notions
> impose, such as the requirement that every instance of every key be a
> permanent identifier for something in the Universe of Discourse. If
> instances of a key do not permanently identify things in the Universe,
> then transition constraints that join the relation values before and after
> an assignment to a relvar would not be sound. For example, suppose that
> you have a relation, {L, F, Stat} Key {L, F}that starts out with the
> following value:
>
> {{L:Smith, F:Mary, Stat:Single},
> {L:Jones, F:Mary, Stat:Married}}
>
> and is then assigned the value,
>
> {{L:Smith, F:Mary, Stat:Divorced},
> {L:Jones, F:Mary, Stat:Married}}
>
> You might be wondering how someone who is Single can become Divorced?
> Should the assignment be rejected on that basis? Or is it possible that
> Mary Jones' maiden name is Smith, and that she became Divorced while at
> the same time the Mary Smith who was Single Married Robert Jones? It
> might also be that the Mary Smith who was Single moved out of the area
> while a different Mary Smith, who is Divorced, moved in.
>
> The D&D adherents would try to claim that this is a bad design, and as
> such should be dismissed, but Codd, even as early as 1970, recognized that
> key components of tuples would be the target of updates, which should be
> obvious given that there can be relation schemes in which the entire
> heading is the key. The ambiguity inherent in relational assignment that
> is illustrated by the above example is exhibited by every relation in
> which instances of the key do not permanently identify things in the
> Universe, so their claim becomes just noise.
>
> Note that the introduction of an autogenerated ID eliminates all
> confusion:
>
> If you start out with,
>
> {{ID:1, L:Smith, F:Mary, Stat:Single},
> {ID:2, L:Jones, F:Mary, Stat:Married}}
>
> and then assign
>
> {{ID:1, L:Smith, F:Mary, Stat:Divorced},
> {ID:2, L:Jones, F:Mary, Stat:Married}}
>
> then clearly the assignment should be rejected because Single people can't
> get Divorced, but if you assign
>
> {{ID:1, L:Jones, F:Mary, Stat:Married},
> {ID:2, L:Smith, F:Mary, Stat:Divorced}}
>
> then the assignment should be allowed, because Single people can get
> Married and Married people can get Divorced. Similarly, if you assign,
>
> {{ID:2, L:Jones, F:Mary, Stat:Married},
> {ID:3, L:Smith, F:Mary, Stat:Divorced}}
>
> Then it is clear that there is a different Mary Smith, and the assignment
> should be allowed.
>
> It is a consequence of adopting D&D's notions that demands the
> introduction of surrogates, such as autogenerated IDs. If, on the other
> hand, insert, update and delete are not shortcuts, but rather primitive
> operations, then the introduction of the autogenerated IDs is not
> necessary. All that is needed is to correlate the tuples in a relation
> that are the target of an update with those in its successor. For
> example, the update
>
> {{L:Smith, F:Mary, Stat:Single, L':Smith, F':Mary, Stat':Divorced}}
>
> should clearly be rejected, but the update,
>
> {{L:Smith, F:Mary, Stat:Single, L':Jones, F':Mary, Stat':Married},
> {L:Jones, F:Mary, Stat:Married, L':Smith, F':Mary, Stat':Divorced}}
>
> should be allowed. Similarly, the delete and insert,
>
> {{L:Smith, F:Mary, Stat:Single},
> {L':Smith, F':Mary, Stat':Divorced}}
>
> should be allowed.
>
>
>

Thank you for a clear explanation of a concept that you have been trying to get across for lo these many months.
The example is well chosen, and illustrates the concept you are trying to get across better than your previous abstract explanations. At least that's true for me. Maybe I just learn by moving from the concrete to the abstract more than the other way around.

I'd rather mull this over for a while before responding.

Meanwhile, a parallel conundrum is whether a key identifies something in the Universe of Discourse or a row in a table. Many of the folks in stackoverflow and similar places seem to take the latter view. Once you take that view, assigning the row an immutable ID and referencing the row by that ID starts to make a lot of sense. The idea that by doing so they have readopted the graph model of data and jettisoned the relational model of data doesn't seem to have reached their consciousness at all, as far as I can tell. If I'm right about this, then maybe they are trying to unconsciously model the data using a syncretism between two irreconcilable models of data. Received on Sun May 31 2009 - 09:12:59 CEST

Original text of this message