Re: Relation Schemata vs. Relation Variables

[Quoted] "JOG" <jog_at_cs.nott.ac.uk> wrote in message news:1156264778.188455.225660_at_m79g2000cwm.googlegroups.com...
>
> David Cressey wrote:
> > "Brian Selzer" <brian_at_selzer-software.com> wrote in message
> > news:H%lGg.16832$o27.8955_at_newssvr21.news.prodigy.com...
> > >
> > > "JOG" <jog_at_cs.nott.ac.uk> wrote in message
> > > news:1156163752.697442.161770_at_i3g2000cwc.googlegroups.com...
> > > > Brian Selzer wrote:
> > > >> Identity beyond that provided by a candidate key (that is, a single
> > > >> database
> > > >> state) is in the eye of the beholder:
> > > >
> > > > Identity beyond that provided by the identifying keys is a nonsense.
By
> > > > not encoding it you have told the system it does not exist. If this
is
> > > > not true who is at fault?
> > > >
> > >
> > > How do you tell the system that it does exist? How can you know when
you
> > > design a system whether a key that appears to be very stable will
remain
> > > that way? I was called in to fix a problem at a company where
management
> > > decided to add a prefix to each part number so that they could tell at
a
> > > glance which plant produced a part. So an identifier that was very
stable
> > > suddenly wasn't for several days while admin staff were busily
updating
> > > every part number in the system, and duplicating parts that were made
at
> > > more than one plant. In the end many reports and the application had
to
> > be
> > > modified in order to summarize the information for parts produced at
> > > multiple plants and to work around the redundancy introduced as a
result.
> > > During that period the database became corrupt because it allowed
changes
> > to
> > > occur that shouldn't have been allowed. In this case, preventing
those
> > > changes was left up to the application, which was not designed to deal
> > with
> > > multiple entries for the same part and which remained broken for
several
> > > weeks--making the problem even worse. It can be surprising how a
change
> > > that doesn't seem that significant can spiral into a total disaster.
The
> > > point is, keys that appear to be very stable can change for the
stupidest
> > > reasons, and constraints should be definable and enforcible in the
> > > database--including transition constraints. The designers of a system
are
> > > making a gross assumption about the stability of those keys if they
think
> > > that what is stable today will remain stable tomorrow.
> > >
> > >
> >
> > BTDT. This is a classic case, one that has happened enough times so
that
> > it's well documented in the literature.
> > The consequences should have been eminently predictable, to a seasoned
DBA
> > or database designer. That person should have pushed back to
management,
> > alerting them to the probable disaster awaiting if they changed the keys
for
> > part number.
> >
> > The specific change wanted by management was, itself, a bad one. It
adds
> > "intelligence" to a key, with the attendant disadvantages.
> >
> > If a DBA did push back, and management went ahead anyway, then
management
> > screwed up, big time. Unfortunately, managment screwups are often, in
> > today's world, blamed on somebody else, and managment continues to make
the
> > same mistake.
>
> I agree with David.
>
> However this is not an issue with the logical model. It is not like an
> identifying 'hair_colour' attribute changing from brown to red. Rather
> it is just a /translation/ of one label to another, even though it
> represents /exactly/ the same value it did before. (no different from
> translating a database into french say). Its a subtle difference but
> the key is still completely stable, and imo should have been altered in
> old relation values too so as to maintain consistency given the
> managements foolhardy renaming of the world.
>

I agree that it's just a translation, or perhaps a transliteration.

But there are some practical conseuquences to changing a key, even in a systematic way. In practice, a great deal of data is often under the management of the DBA while its in the database, but passes beyond the DBA's span of control, when it is extracted and either fed to some other body of data or printed in a report.

In particular, some of the old foreign keys will be beyond the control of any DBA to translate into the new key value. Thus, when people go through the archived data, or perhaps the warehoused data, the issue will arise of whether a given foreign key should be referred back to the "old reference table" or the "new reference table". This issue won't arise in the case under discussion, because you can tell "at a glance" (shudder!) whether a key is old style or new style. But there are key changes where it's not obvious.

As an example, In October, 1752, the American colonies adopted the "new style calendar" (Gregorian). For several years thereafter, dates had to be qualified as O.S. (Old Style) or N.S. (New Style). An unqualified date was subject to misinterpretation, and attachment to the wrong day. This had practical consequences in, for example, the calculation of mortgage amortizations. Yeah, yeah, this has nothing to do with computerized databases, but it illustrates a problem with data management nicely.

And, in the long run there isn't much difference between mismanaging data with quills and parchment or with computers and long term storage. The biggest difference is how much data you can screw up in an hour. Received on Thu Aug 24 2006 - 14:15:45 CEST