Re: Relation Schemata vs. Relation Variables


"Brian Selzer" <brian@xxxxxxxxxxxxxxxxxxx> wrote in message
news:H%lGg.16832$o27.8955@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx

"JOG" <jog@xxxxxxxxxxxxx> wrote in message
news:1156163752.697442.161770@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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.




.

Relevant Pages

  • Re: design question
    ... Are you sure you don't mean "primary keys made up by composing several ... The second stage was logical database design, ... SQL modeling and relational modeling that I've since seen in this newsgroup. ...
    (comp.databases.theory)
  • Re: [OT] Normalization
    ... >> primary keys on every database table. ... >> changed the database design for those situations was to provide a better ...
    (comp.lang.php)
  • Re: Key attributes with list values was Re: What are the differences ...KEY
    ... Jane Harper is married. ... And a constraint that states that single people cannot become divorced. ... database, or users, for that matter, to distinguish between them. ... That's the whole point of keys. ...
    (comp.databases.theory)
  • Re: Pin generation algorithm question
    ... the keys would be a big ... Suppose that we have a database that contains all valid numbers, ... load among several servers that all need access to this database. ... So the only real problem is which systems are accessing this crypto box. ...
    (sci.crypt)
  • Re: Database design, Keys and some other things
    ... >> Or 'the database has no opinion as what Donald Trump's e-mail address might ... some keys can be wrong or a data can ... Meaning is not related to just one number. ... > is concerned a VIN is not a surrogate key, ...
    (comp.databases.theory)