Halfwit Newbie: Normalising and linking staff database (1 Viewer)

[bernmc]

Hello all.

Still getting my head around this database stuff, and need some help on defining primary keys and links:

Basically, an operating theatre staffing database:

It has a four week rolling rota.

Lets say for eg there are 10 theatres.

Week1 Monday Theatre1(am) will have
Surgeon: Joe Bloggs
Anaesthetist 1: Bert Blinkenthrop
Anaesthetist 2: Amy Phmut

Wk1 Mon Theatre1(pm) will have
Surg
Anaes1
Anaes2

Wk2 Mon am... etc etc

So my database will have a list of Anaes staff, surg staff, and theatres that need to be staffed. How do I tie it all together into Week 1, Week 2, Week 3 etc? How should I be linking the different tables - ie what should the primary keys be?

Here's the start of my ER diagram (ignore the current key settings - they're wrong):

Am I going about it the right way? Seems to me that the 'unique identifier' would have to be a combo of Week/day/site - is this correct?

 

[Adeptus]

At the minimum, add a numeric ID column to RotaWeek.
Maybe the other tables as well.

Or completely restructure it...

SurgStaff: about right how it is. Maybe add an ID number for easy linking.
I'm assuming this holds surgeons & anaesthetists, with the Specialty field telling you which they are?

RotaWeek & RotaSites: argh... misleading table names & confused structure.
How about...

tblShift: ShiftID, Week, Day, (Time? - ie to indicate AM or PM)

tblSite: SiteID, SiteName (this table is kind of optional... you could just put the name in place of SiteID below)

tblRoster: ShiftID, SiteID, SurgID, AnaesID1, AnaesID2

 

[Steve R.]

1. Each table will need either an autonumber field or a long integer field so that they can be connected.
2. I am viewing the potential design as being a time slot reservation system. Assume 10 (2 per day * 5 days) operating events a week. For the individual the location assignment would not be relevant, however you would need to know whether he/she has been or has not been assigned. From the perspective of each location you would need to fully staff each time slot.
3. The table surgical staff could have 10 logical fields, one for each time slot to identify whether the staff member has been assigned.
4. The table ROTASITES would need fields for location, staff specialty, staff name, and time slot number.
5. The way to retrieve and edit data the data would be through an SQL statement such as "Select * FROM ROTASITES WHERE TIMESLOTNUMBER = XX AND LOCATIONNUMBER =YY"
6. Problems with what I am suggesting.
6a. I am suggesting the use of logical fields for the timeslot a person is assigned to, but then using a numeric number for the timeslots in the ROTASITES table.
6b. The table ROTASITES may require a logical field to identify if a surgeon (for example) has been assigned to a particular timeslot for that location as you will need to identify which slots are filled and which are vacant. Technically, if a person has been assigned, the field would not be null so there would, in theory, be no need of a logical field. I find logical fields a bit easier to use, even if they are duplicative.
6c. The system, as currently (not)thought out, would need to be "purged" on a weekly basis, it would be better if the system could "creep". Timeslots become available again, the day after they expire.
6d. You may need a "reference" table to identify the staffing requirements for each location. This table would be linked to the ROTASITES table by the location number.

 

[bernmc]

Thank you both for your advice - starts to make a bit more sense!

I'll be back...

 

[Steve R.]

Since posting, it dawned on me (when I got out of bed) that the surgical staff table can (should) be linked to the ROTASITES table as a one to many relationship.

Another issue to be resolved in the design of the database would be the non-availability of staff due to vacations, sick, etc.