Re: Relation Schemata vs. Relation Variables

"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. Received on Tue Aug 22 2006 - 12:49:21 CEST