rbw00@uts.amdahl.com (Richard Wilmot) (07/06/90)
Cameron Stephenson writes: >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)... You might wish to make a refinement so that people are not deleted immediately upon termination but instead have a datum for separation_date (termination_date) which is filled in for terminated employees. Even dead people need records. It's also nice to carry a hire_date so that you can see the spans of service. Of course, you cannot carry any data that will not be maintained (dutifully) by someone (always helps if their paycheck/commission check depends on correctly entering the data. >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? If your key_issue relationship table has inclusive dates (the from-to period when the person possessed the key) and you have a new relationship between keys and rooms then you should be able to join Room R to all the people having had access to keys that access room R (from_date > date_1 AND to_date < date_2). >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?) A key_room relationship sounds like the ticket (which would also need dates in the building where I work as new rooms become accessible to old keys and old rooms are often rekeyed -definitely a M-to-N situation again. A view can ease the coding if you are doing many different queries but I don't think it's necessary as most SQL systems support multiway joins. Vendor's documentation should have an example? >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. This brings up an interesting design problem. The real capabilities are in the key pattern: any copy will have the same room opening capabilities. So the key_room relationship table need not include the copies but the key register which stores key_issue information should contain the copy number as it is a physical object that we have issued to a person. It will be helpful if the key_ID is stored separately from the copy number. In this case the copy_number has only complicated one table (key register) and the data model stays close to the complexity of the real world. Queries to see who has access to which rooms would not involve copy_number. You might also need to record the real copies as objects and this could be done in a separate table: KEY_COPY ( key#, copy#, date_created, date_destroyed) ---- ----- where the pair <key#, copy#> are unique identifiers. This table should not be often needed but could provide a validity check and record information about copies not currently in circulation (do we have another copy of key 4567 in inventory? Before making a new copy). >Once again, thanks very much to all who replied. >Cameron Stephenson Telephone +61 75 951220 >Bond University >Gold Coast Australia Best luck. Tell us how it works out. - Dick -- Dick Wilmot (408) 746-6108 | I declaim these as my own ramblings.