Determining relationships between tables

[nkm]

Hey guys

just wondering if you could help me out, i am trying to identify relationships between three tables; the relationships can either be 1-1, 1- ∞, or ∞-∞

I have identified the primary keys as well but am not sure they are correct, the only relationships i think exist are between years of experience in the pay info table with years of experience in the staff info table, along with scheduled week in the staff info table with week # in the scheduled table

please look at the tables in the attachment and help me out!?

thanks!

tables.pdf

[LaRetta]

Hi nkm, welcome to FM Forums!

Staff Info should only have staff info in it - not Scheduled Week. The Schedule Table should have the StaffID and also its OWN ScheduleID (all tables should have their own unique auto-enter serial). Also put a serial in PayInfo.

It looks like you are pulling the Pay Info wage ( * table used as a lookup) depending upon YearsOfExperience. If so, that is fine (except change to the PayInfoID because YearsofExperience for a job may change and you would break your relationships to Staff if based upon YearsOfExperience). So it appears that your joins would be:

Pay Info Table::PayInfoID = Staff Info::PayInfoID

Staff Info::Staff ID = Schedule Table:StaffID

* You are missing the Shift information. If your Pay Info::Wage is a calculation, only used to display the wage, it will change as the data changes, ie, Staff's YearsOfExperience, criteria in the Pay Info table and so on. That wage should be planted within the Shift table for history purposes. And that is the piece you seem to be missing. The Shift table would include:

ShiftID (unique auto-enter serial)

ScheduleID

StaffID

PayInfoID

WorkedFrom (timestamp start)

WorkedTo (timestamp to)

... and whatever else is determined by your business rules

It is truly best if you post a zipped sample file from which to work as you begin to create (and modify) these relationships we discuss. It will be easier for us to help you get them right. There are many wonderful people here willing to assist you.

LaRetta :wink2: