Path: utzoo!utgpu!news-server.csri.toronto.edu!mailrus!cs.utexas.edu!swrinde!ucsd!pacbell.com!ames!uhccux!virtue!comp.vuw.ac.nz!munnari.oz.au!bunyip!lance!kirk!cameron
From: cameron@kirk.nmg.bu.oz (Cameron Stevenson)
Newsgroups: comp.databases
Subject: Re: Time related records
Message-ID: <1127@kirk.nmg.bu.oz>
Date: 3 Jul 90 23:14:49 GMT
References: <0ejm02D3b48801@amdahl.uts.amdahl.com>
Organization: Network Management Group, Bond University, Australia
Lines: 35

From article <0ejm02D3b48801@amdahl.uts.amdahl.com>, by rbw00@uts.amdahl.com (Richard Wilmot):
 < reply deleted >

 Thanks very much Richard. The key register is indeed a many-many
 relationship, and if I read your message correctly you suggested I set up
 the tables as follows:
 1. a person table, with each row representing one person
 2. a key table, with each row representing one key
 3. a person-key relationship table, with each row representing a relationship
 between a person and a key - ie. a row for when a person is issued with a key.

 Sounds fine, and the logic even seems to work - ie. if a person is deleted
 from the person table (they leave) their entries in the person-key table
 can be retained for future reference. Let's complicate the issue (believe
 me, not unnecessarily)...

 What is really needed from the application are answers to the following
 questions:
 1. Who has/has had access to this room?
 2. Which room/rooms can this person get access to?

 Now we introduce a second set of many-many relationships, as one key can
 access many rooms, and one room could be accessed by many keys. Does this
 affect the overall model design, or do I just create a key-room relationship
 table? The query which traces a person through to the room should be
 interesting... (perhaps a view would be the way to go?)

 Just as a final thought to confuse it further... there are always multiple
 copies of the same key. This means the key number is not a unique instance.

 Once again, thanks very much to all who replied. 

 Cameron Stephenson                           Telephone +61 75 951220
 Bond University
 Gold Coast    Australia