A real world example

In an earlier thread, Marshall asked for a real-world example, and while
this isn't exactly what was asked for, it illustrates the problem. Here's
the situation. I have two databases containing orders. One is the
customer's database containing purchase orders, lines, schedules and
releases. The other is the supplier's database containing sales orders and
lines. There should be a 1:1 relationship between the releases in the
customer's database and the lines in the supplier's database.

If you join the respective tables in each of the databases, you get the
following:

Customer purchase order detail:
{PO#, POLine#, CustPart#, Schedule#, ReqestedDate, RequiredQuantity,
ReceivedQuantity}

The primary key is {PO#, POLine#, Schedule#, RequestedDate}

Supplier sales order detail:
{SO#, PO#, SOLine#, Part#, CustPart#, RequestedDate, PromiseDate,
RequiredQuantity, ShippedQuantity}

The primary key is {SO#, SOLine#},
an alternate key is {PO#, CustPart#, RequestedDate, SO#}

The first problem is that the customer uses a blanket PO, and therefore,
(usually) reuses the same PO# and Line# to order a part--the schedule number
differentiates one set of releases from another.

The second problem is that the supplier's database doesn't track schedules,
so there's no way to differentiate sets of releases, except that the SO#
will be different for different schedules.

Aside from those there have been keying errors, some rows don't have a
customer part #, some rows don't have a PO number, etc.

What I've been asked to do is to produce exception reports, showing what is
different between the two databases so that changes to purchase orders that
appear in the customer's database can be verified after they're entered into
the supplier's database.

My point is that here is a real-world example where the universes of two
databases overlap, but the set of attributes used to identify something
common to the two universes is different for different databases. I
understand that this is a common problem when merging or otherwise
connecting databases, and I've run into this numerous times and have it
covered, but the fact remains that a similar situation can occur within a
single database. With natural keys, that is, keys whose value can be
different in successive database states, it is possible for the values of
one set of identifying attributes of something in the universe to be
different in successive universe states, and when this new information is
imparted to the database, it is possible for there to be propositions in
successive database states that should correspond, but cannot because the
only relevant set of identifying attributes of something is different in
successive universe states. It doesn't matter how stable a set of
identifying attributes is, if there's the slightest chance that its values
can be different in successive universe states, you have to assume that they
will. The problem is one of relevance. If the DNA of a person is not
relevant, but is the only set of attribute values that is guaranteed to
remain constant throughout the discussion, then what do you do? If you need
to know what was known about a thing, then there are only three choices: (1)
use a surrogate for the DNA and make it the primary key, (2) identify each
statement in the database so that it can be discussed in successive database
states (in other words, use a surrogate for each statement), or (3) define a
tuple-level assignment operator (A tuple-level assignment operator would
permit the system to correlate tuples in successive database states and to
act accordingly. For example, Oracle has a FOR EACH ROW trigger.)




.

Relevant Pages

  • Re: A real world example
    ... customer's database containing purchase orders, lines, schedules and ... The other is the supplier's database containing sales orders ... Customer purchase order detail: ... The successive database states example, ...
    (comp.databases.theory)
  • Re: A real world example
    ... The other is the supplier's database containing sales orders and ... The second problem is that the supplier's database doesn't track schedules, ... different in successive universe states ... The successive database states example, ...
    (comp.databases.theory)
  • Re: A real world example
    ... The other is the supplier's database containing sales orders and ... The primary key is {PO#, POLine#, Schedule#, RequestedDate} ... While stability is an important design criterion for choosing keys, both surrogate and natural keys can be stable or unstable. ... The successive database states example, ...
    (comp.databases.theory)
  • Re: A real world example
    ... The other is the supplier's database containing sales orders ... it requires two successive database states from the customer database; ... one set of identifying attributes of something in the universe to be ...
    (comp.databases.theory)
  • Re: problem using identity column as primary key
    ... >> I am thinking of creating an identity column to use it as primary key ... More and more programmers who have absolutely no database training are ... the gap in the sequence is not filled in and the sequence ... vin CHARNOT NULL REFERENCES Motorpool); ...
    (microsoft.public.sqlserver.programming)