Re: ID field as logical address

From: Kevin Kirkpatrick <>
Date: Mon, 1 Jun 2009 07:12:49 -0700 (PDT)
Message-ID: <>

On May 31, 2:12 am, "Walter Mitty" <> wrote:
> "Brian Selzer" <> wrote in message
> news:O4mUl.12059$
> > "Walter Mitty" <> wrote in message
> >news:fv9Ul.1465$
> >> "paul c" <> 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}}

What is the poor HR person to do when Mary Smith returns from a week long Vegas trip and reports that she'd gotten married on Monday and divorced on Friday?

> > 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.- Hide quoted text -
> - Show quoted text -
Received on Mon Jun 01 2009 - 16:12:49 CEST

Original text of this message