Table relationships Access97

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am creating a database to track training on SOP’s (Standard Operating Procedures) by person and job. SOP’s are revised periodically. It is important to know the effective date of the current version. I need help defining the table relationships. The following conditions exist

A person can have 1 or more job
A job will require 1 or more SOP’
An SOP will be applicable to 0 or more job
Training will be by group or individual

Thank you for any help

Robert
 
Robert

You left out the relationship of jobs to persons. I assume a job (?job
title or a named specific single instance of a job/contract?) can be held by
more than one person (e.g., Microsoft has more than one "programmer").

tblPerson
(with Person info)

tblJob
(with generic info about a job

trelPersonJob
(with info about a person doing a job -- e.g., when begun, when ended, ...)


Ditto for Job and SOP -- it looks like the same SOP be "used" by more than
one Job.

You need three tables for that, too ... one for each Entity, plus the
"resolver"/"relationship" table.


"Training will be by group or individual."

Now, how does training relate to SOPs? You need to work that out before you
work out how to assign folks to training (but a hint: three tables,
including the "relationship/resolver" table).

And this is the first you mention of "groups" -- how do you show one/more
persons as belonging to a "group"?
 
Robert

Access is a relational database. The concept of "relationship" includes
"one-to-one", "one-to-many", and "many-to-many".

It is this latter that requires an extra table I described as a "resolver"
or "relationship" table -- Access can't directly resolve (i.e., manage) a
"many-to-many" relationship without the help of this third table.

A real-life example might be a hotel reservation situation, where there are
persons, rooms, and "person-reserving-a-room". Because a person could
reserve one/more rooms, and a room could be reserved by one/more persons,
those two have a many-to-many relationship.

A person has a one-to-many relationship with the "person-reserving-a-room"
table. A room also has a one-to-many relationship with the
"person-reserving-a-room" table.
 
Jeff,

What is the best primary key to use in the "person-reserving-a-room" table -
PersonreservingaroomID
or a joint key:
PersonID and RoomID

Why?

Melissa
 
What is the best primary key to use in the "person-reserving-a-room" table -
PersonreservingaroomID
or a joint key:
PersonID and RoomID
Click to expand...

Of the two choices, PersonreservingaroomID is the only one that would
allow a person to stay in the same room twice.
 
Melissa

As rkc points out, PersonID and RoomID as a composite key only lets a person
reserve a room once. If you decide to use a composite key, you'll need to
add another field (a Date/Time "when" comes to mind).

I know of no hard/fast rules, and you risk igniting a "religious war" about
arbitrary vs. natural keys <g>!

That said, I'd go with a single, autonumber ID field, but still put a "No
duplicates" index on the combination of PersonID, RoomID,
RoomReservedForDate fields to ensure no double-booking (by the same person).
You might also consider a RoomID, RoomReservedFor Date unique index, to
ensure no room gets double-booked.

If you decide to go with the three field composite ID, you'd also need to
"migrate" that key to any tables that would be "child" tables of your
PersonRoomReservation table. That is, your child table(s) would need to
have all three fields, plus its own ID field. This is one advantage, in my
opinion, of using a single (autonumber) ID field ... you only need one
"foreign key" field in the child table.
 
Jeff Boyce said:
Melissa

As rkc points out, PersonID and RoomID as a composite key only lets a person
reserve a room once. If you decide to use a composite key, you'll need to
add another field (a Date/Time "when" comes to mind).

I know of no hard/fast rules, and you risk igniting a "religious war" about
arbitrary vs. natural keys <g>!
Click to expand...

The only thing I would say about this is that if you're born and raised
thinking using an autonumber will make a row unique you will end up
with a fundamental mis-understanding of the normalization process.
 
rkc said:
The only thing I would say about this is that if you're born and raised
thinking using an autonumber will make a row unique you will end up
with a fundamental mis-understanding of the normalization process.
Click to expand...

Which is why Jeff added the very important point (which you chose to snip
for some reason)

"That said, I'd go with a single, autonumber ID field, but still put a "No
duplicates" index on the combination of PersonID, RoomID,
RoomReservedForDate fields to ensure no double-booking (by the same person).
You might also consider a RoomID, RoomReservedFor Date unique index, to
ensure no room gets double-booked."
 
Joan Wild said:
need

Which is why Jeff added the very important point (which you chose to snip
for some reason)
Click to expand...

I was commenting on the religious wars aspect of his post. I was not
insinuating
that there were any deficiencies in his advice. It wasn't a personal attack.
No need
to circle the wagons.
 
Thank you for the information. I did not have an opportunity to read the response until now. I think this has been a big help
Robert
 
Back
Top