Path: utzoo!attcan!uunet!samsung!usc!elroy.jpl.nasa.gov!lll-winken!sun-barr!newstop!sun!amdahl!rbw00
From: rbw00@uts.amdahl.com (Richard Wilmot)
Newsgroups: comp.databases
Subject: Re: Time related records
Keywords: time,database,semantics,flexibility
Message-ID: <0ejm02D3b48801@amdahl.uts.amdahl.com>
Date: 29 Jun 90 15:44:36 GMT
References: <1082@kirk.nmg.bu.oz> <7vi4uht@unify.uucp>
Reply-To: rbw00@amdahl.uts.amdahl.com (Richard Wilmot)
Organization: Amdahl Corporation, Sunnyvale CA
Lines: 44

In Message-ID: <7vi4uht@unify.uucp> reg@unify.uucp (Russell Grau)
replies to Cameron Stephenson's request for assistance with a history
keeping application with a design suggestion:

> Cameron -

> I would recommend that you create a "person" table if you do not already
> have one.  This person table would include the name, department, etc.,
> etc. that you need for this person.  It would also include as a foreign
> key the primary key for your "key register" table, the date that the key
> was issued and the date that the key was turned into HQ.

> This is the classic one to many relationship with the one being the "key
> register" and the many being the "person".

> Should be fun....

But if the person table includes as a foreign key the primary key (e.g. key#)
of the key_register table then a person can only have one key at a time.
There are two entity types here: people and keys. There is also a relationship
between some of the entity occurrences: issued_to. Another problem is that
if I delete a person record (row) then I will have lost some of the history
regarding the key that person last possessed. Additionally a person's key
possession history cannot be tracked. We could not answer whether John Doe
ever had access to a particular if he had been issued a new one so that
his key history were overlayed with new key issue information. In short
this appears to be a many-to-many relationship: each person can have
multiple keys and over time each key may have been issued to multiple
people. Many-to-many relationships require relationship tables to store
the information about the relationships. In general, choosing to model
a relationship as one-to-many is risky compared with electing many-to-many
if application/business needs might at some point require it to become
many-to-many because application logic assuming the former is hard to
change whereas applications developed for the many-to-many case easily
handle the one-to-many case.

Storing information about the people to key relationships in a saparate
relationship table will remove the above update anomalies and tracking
limitations. It will then be necessary, of course, to have an integrity
constraint to disallow removal of an object which is still involved in
a relationship or to cascade such a delete (e.g. maybe we don't care which
keys a terminating employee had). Doesn't Oracle support integrity constraints?
-- 
 Dick Wilmot  (408) 746-6108   | I declaim these as my own ramblings.