Need counselling on relationship problems!

[CJBIRKIN]

Hi

My brain is being reduced to the consistency of cream cheese today (way to hot and tired ).

I'm trying to develop a Rota system for a hospital department and i've got to the point of trying to store the actual data that makes the Rota.

My intention was to create a table of Units/theatres.

Note: Each unit will be used every weekday and some may be used on weekends.

Then a link table containing the date and the unit id as a foreign key with a PK(DateID).
This would be a 1 unit to many dates

Then a Rota table containing the date_id as a foreign key and because the days are split into 2 shifts am and pm 2 fields which will contain the staff_id from the staff table as foreign keys + its own unique key


See pic

My idea was to have three forms. The main form containing the unit.

A subform for the date linked to the mainform.

And a second subform for the rota staff linked to the date.

Something just doesn't feel right though.

With this setup the user will have to enter the date for each unit. There are 29 units and the rota is produced for 7 days. So each time the user selects say Theatre 1 they will have to create 7 days of dates and then Theatre 2 the same 7 days of dates and so on.

I could code something to get round this but that makes me think that i have done something wrong with the structure.

Someone please put me out of my misery! (I don't need the loan of a gun thanks all the same!) Is this right?

Cheers

Chris

 

[Mark Wild]

Hi CJ,

sorry for lack of info - I'm being called away...

One thought from my initial scratchings. A location will have many dates, but will a date not have more than one location? Think this might need to be a many to many relationship.

will try and carry on later...

 

[CJBIRKIN]

Hi Mike


To clarify a unit will be assigned a number of staff on any given day.

Therefore 1 unit may have many staff and 1 staff member may work in many units. This many to many relationship is not allowed in relational databases. So i created a link table using the date


This gives 1 unit on 1 day may have many staff,

1 staff member on 1 day may only work in 1 unit

I think this is correctly normalised.

The problem is that the link table will look like this.


PK----Date----------UnitID
1----04/07/03--------1
2----05/07/03--------1
3----06/07/03--------1
4----04/07/03--------2
5----05/07/03--------2
6----06/07/03--------2

As you can see the date field gets repeated.

Each record is unique but as i said there are 29 units and the rota is created for upto a 7 day period. So the user will have to keep entering the dates over and over again.

Unless there is a mistake in the normalization perhaps what i'm asking is, "is there a simple way to copy the dates for the next unit and add the Pk of the unit assuming that the data doesn't already exist"

Clear as Mud!

Chris

 

[Len Boorman]

Just in for a pennyth
A linking table in principle only consists of the primary keys from the tables to be linked giving a combined key.

Handy hint. Whenever you do this if you explre the attributes of the other two tables frequently you find that at least one attribute belongs in the linking table. If you cannot find anything that shopuld be in the link table apart from the key fields then okay but be suspicious. You may have missed something

len

 

[CJBIRKIN]

Hi

Len, so what you are saying is;

Tbl_Location.................Tbl_Link.........................Tbl_Rota

Location_id-------------<Location_id_fk
Location.........................Rota_ID_Fk>--------------Rota_ID
...........................................................................Staff_ID_FKAM
...........................................................................Staff_FKPM

And somewhere you put the date either in the Tbl_Link or Tbl_Rota.

How do you get the PKdata into the linktable?


Cheers

Chris

 

[Len Boorman]

Basically you have got to ensure that you normalise correctly.

Question
Why a location ID, is Location not good enough, is it not unique.

Confess I may not exactly understand you application but seems like you have

Not sure what Staff FKPM represents

But taking three tables

tbl_Location tbl_Rota tbl_Staff
Location as PK Location Staff ID as PK
Other Staff_ID Name
Location Date More Staff data
Data

tbl_Rota has Location and Staff_ID combined as PK and each acts as FK from tbl_Location and tbl_Staff

Date is the date that that Staff member is at that location

Does this help or have I miss understood your application


Len B

 

[CJBIRKIN]

Hi

Told you it was a cream of Brain cheese day!

Tbl_Location.................Tbl_Rota.............................Tbl_Staff
.....................................Rota_ID(PK)
Location_id-------------<Location_id_fk
Location.......................Staff_ID_AM(FK)>------------Staff_ID
.....................................Staff_ID_PM(FK)>----------------^
.....................................Date(PK)


I was being sooo stupid (Don't laugh) Tbl_Rota is a link table between tbl_location and tbl_staff.

The dates will still need to be added lots of times ( I can programme round that) but the structure feels right now.


Thanks for everyones help

Chris

 

[CJBIRKIN]

Hi len


Posted at the same time so didn't see your last reply.

AM and PM are just that before and after 12 mid day.

Basically for each day each unit will have two shifts and these need filling by a number of staff. Say 6. one lot Am and one lot Pm.

You wouldn't believe i've been doing this for 4 years now!

It's about 30 degrees in my office and i just can't think straight.

I hope this is right now.

Cheers

Chris

 

[Len Boorman]

Sometimes the obvious is so obscure

Mind you if you have been doing this for 4 years it must be one helluva database.

Er
Silly me
Of course it hasn't taken you 4 years on this one Has it

No probs. Wood and trees springs to mind

get brain dead myself

Len B

 

[CJBIRKIN]

Hi

No not 4 years on this! LOL , I'm usually really uptight about getting the relationships right, had a good teacher. I knew this was wrong but i just couldn't see it. As you say woods and trees.

I'm off to the pub now to finish the cream cheese process.

I'm sure if Pat H sees this i'm in big trouble!!


Cheers

Chris

 

[Len Boorman]

I also messed up a bit on the link table PK. Staff ID and Location will certainly be part of PK but may also need date.

You may also need some indexes to control a few bits

Staff Location date Unique so that staff is not at different location on same date

Similar with Shift id

could someone be at one location on one shift and second location on second shift same day

Would suggest that these rules need to be identified and accepted by all

Len