Re: Database design, Keys and some other things

From: vldm10 <vldm10_at_yahoo.com>
Date: 5 Oct 2005 02:25:54 -0700
Message-ID: <1128504354.697371.247500@g44g2000cwa.googlegroups.com>

JOG wrote:
> Marshall Spight wrote:
> > Gene Wirchenko wrote:
> > > On 1 Oct 2005 09:52:49 -0700, "Marshall Spight"
> > > <marshall.spight_at_gmail.com> wrote:
> > >
> > > [snip]
> > >
> > > >We were discussing whether there was a difference between
> > > >the natures of external ids vs. surrogate keys. What is
> > > >essential to this question is what their nature is. Generally
> > > >we do not regard context-specific considerations as essential.
> > >
> > > Was it murder or self-defence? Context.
> >
> > There are many domains where this sort of context is relevant.
> > Mathematics is not on the list, however. Whether a number is
> > an integer or not is not context-dependent. Given what's been
> > said on this thread so far about the difference, it looks to
> > me similar to the difference between the primary key and other
> > candidate keys; a psychological, non-mathematical, contextual
> > difference.

>

> I'm still not clear why you believe it's just a psychological,
> contextual difference. A surrogate is immutable, a component of the
> internal predicate, while a natural is mutable, and a component of the
> 'external predicate' (I believe Codd, Date, Celko, et al. have all
> written about this in their discussions of the advantages and
> disadvantages of both). This isn't just an ephemeral difference, but a
> practical one, as a surrogate's domain is internally controlled.
> Perhaps I incorrectly view a surrogate as an internal reference point
> for a tuple - such things seem consistently useful to me (although i am
> obviously biased by my work with relationally based composite
> data-types). Either way, maybe the debate's exhausted, and we'll all
> get back to actually doing useful stuff with the RM.

This story about "external" and "internal" is interesting. I would like to add some practical thoughts related to my solution, to clarify it. I connected "internal" and "external" using following identifiers:
1) The identifier of the state of an entity. 2) The identifier of the entity.

Every time when an attribute of the entity is changed I will create new identifier of the state of an entity. But this working definition is not real and it is not precise. The term "change of the attribute" is not precise. It also can be subjective. I defined the event from the Real World and corresponding state of the entity, defined by this event. So an event always starts in the Real World and causes the information.
For example, we have the event in the Real World that somebody deposits the amount of the money on his account; rather then "an attribute amount changed its value".
Now I have an external event totally independent from the database. Information about new value for this attribute comes from outside. I

internally denote the state of the entity which attribute is changed.
With the corresponding identifier of the entity things are connected.
It is now possible to identify the entity which attribute is changed.

An event can cause creation (or change) of one attribute or two attribute or...the whole entity. I use the events in the Real World. In my opinion the events are real and precise terms. So my identifier of the state of an entity has some far connection to the event which cause new entity's state i.e. which causes new information. Received on Wed Oct 05 2005 - 04:25:54 CDT