My relations are driving me mad!

[CJBIRKIN]

Hi

Thanks to Len Boorman for his previous help with this we went through this recently and came up with the following ( i'll take responsibility for any balls-ups!)

Basically . I want to be able to create a unique work shift that allows mutliple staff members to be assigned to each shift but as each shift is at a different physical location i need to make sure that a staff member is not a 2 locations at the same time.
The tables are:

TBL_LOCATION (unit/theatre)
Location_ID (PK)
Location Desc

TBL_SHIFT
Location_ID_FK
Date_Stamp(PK)
AM_PM(PK) (lookup, whether the shift is the morning or the afternoon)
Staff_ID_FK(PK)

TBL_STAFF
Staff_ID(pk)(autonumber)
.......


In this case as i have indexed Date_Stamp, AM_PM, Staff_ID_FK
i hope that this will mean that the member of staff cannot be entered twice on the sameday;

I believe that if the Location_ID_FK is made a key then this would only prevent the person appearing on the sameday and time in the same location, not in all locations.


Allowed:TBL_SHIFT

Location_ID_FK.....Date_stamp...am/pm......Staff_ID_FK
.............1................12/08/2003......am................1
.............2................12/08/2003......pm................1
.............2................12/08/2003......am................2



Not allowed: TBL_SHIFT

Location_ID_FK.....Date_stamp...am/pm......Staff_ID_FK
.............1................12/08/2003......am................1
.............2................12/08/2003......am................1
.............2................12/08/2003......am................2

Not allowed: TBL_SHIFT

Location_ID_FK.....Date_stamp...am/pm......Staff_ID_FK
.............1................12/08/2003......am................1
.............1................12/08/2003......am................1
.............2................12/08/2003......am................2



Now the ? is can i normalize TBL_SHIFT if you look at a longer data set you can see that the first three fields continually repeat them selves

TBL_SHIFT

Location_ID_FK.....Date_stamp...am/pm......Staff_ID_FK
.............1................12/08/2003......am................1
.............1................12/08/2003......am................2
.............1................12/08/2003......am................3
.............1................12/08/2003......am................4
.............1................12/08/2003......am................5
.............1................12/08/2003......am................6
.............1................12/08/2003......am................7
.............1................12/08/2003......am................8

Is it possible to have one table with the shift and another with the staff i.e so you only enter the date data in once and still prevent the member of staff appearing in two locations at the same time?

This would make the life of the user a hell of a lot easier.

Any help gratefully received.

Cheers

Chris

EDIT:

Would it be logical to remove Staff_ID_FK from TBL_SHIFT and create a second table (TBL_STAFF_ONSHIFT) that has the Location_ID_FK, Date_stamp, am/pm from TBL_SHIFT with Staff_ID_FK and index Staff_ID_FK ,Date_stamp, am/pm ??

 

[AncientOne]

I look at it this way:

A shift can have only ONE location and ONE Date and ONE time, but it can have MANY Staff.

To produce unique rows, you must put all the ONES into a Shift table with a PK of Shift ID. So your Shift table contains ShiftID (PK), LocationID(FK), Date and Time.

you join shifts to staff by a table which contains FKs for Shift ID and StaffID and make a compound key of those two fields to prevent any one member of staff appearing in any given shift more than once.

Done this way, the date/time will occur only once in the tables for each shift.

 

[CJBIRKIN]

Hi

Thanks for the help sorry not be on line to reply sooner.

AncientOne; I had originally tried the suggestion you gave and came across this problem.

You are correct in that a shift can have 1 location, 1 date and 1 time.

If you create another link table with the PK shift_id and staff_id as foreign keys then yes you can prevent a staff member from being assingned to the same shift twice.

What i don't think it stops is a staff member being assinged to a second shift which is at the same time in a different location. The reason being that the two shifts will have two different ID's. If you place the the data in to the link tables you get this:

TBL_SHIFT

Shift_ID...Location_ID_FK.....Date_stamp...am/pm
.......1.................1................12/08/2003......am
.......2.................2................12/08/2003......am
.......3.................4................12/08/2003......am

TBL_LINK_SHIFT

Shift_ID_FK..........Staff_ID_FK
........1..........................1
........2..........................1

As you can see both sets of data are unique, however, shift 1 is at location 1 at the same time as shift 2 is at location 2

That means I can create a record in TBL_LINK_SHIFT with a staff member being in two separate shifts at the same time.

I think that in combination of what you and Pat suggested is closest to what i actually did.


Whether it is correct is another matter but it works.

I have one table of Shifts where i have Shift_ID, location_ID, date and time all indexed as it must be unique.

I then have a staff link table with staff Id, date, and time, and Shift_ID.

Staff_ID, date(from shift table), and time(from shift table) are keys which makes them unique so a staff member can't have a record which has 2 dates and times the same and by default been in 2 locations. The Shift_ID is not indexed.

Having tested this it works great. I can have a single table with shifts and dates and another table with the staff and it prevents the staff from being in two locations at the same time.

I can also populate the shift table with code as all units have shifts everyday. So all the user need do is select the staff that will appear in the shift see pic.

Thanks again for all your help.

Chris.