Emerg Dept Scheduling tables normal - ized now??

[Michael]

Here is the revised table structure for my scheduling database. I need to produce
<print out> a 2-week pay period schedule for approx. 18 employees over a 24-hour
work day. Mostly 12 hour shifts, with up to 5 people starting at one time - the rest start
at staggered time intervals. The same employee will not always be working the same
shift.

I posted my initial table structure and got great advice on how to normalize. Here is the
new structure. It would be great to have more input. Thanks.

tblPersonnel
==========
PersonnelID<PK>
LastName
FirstName
email
Address
PhoneHome
PhoneMobile
PhonePager
Notes

tblShifts
======
ShiftID<PK>
ShiftName - such as '7am nurse 1' '7am nurse 2'
ShiftStart - standard start/finish times for shifts
ShiftEnd

tblSchedule
=========
ScheduleID<PK>
Date
PersonnelID<FK>
ShiftID<FK>
ShiftStartTime - to be used when there is a variance in shift start/end times
ShiftEndTime - such as when the 0700 RN will start at 0800 instead.

I would appreciate any guidance you can provide. Thanks again.

Michael

 

[Duane Hookom]

I would only use the ShiftID or the Start and End times, not both. Or I
suppose you could use the ShiftId with fields to store the Minutes of
differentiation from the shift start and the minutes earlier or later than
the normal shift.

 

[Scott McDaniel]

In addition to Duane's advice, I'd also build an index on tblSchedule that
consists of lngPersonnelID, lngShiftID, and Date (although I'd rename the
Date field, as it's a reserved keyword in Access and can cause you
troubles). That way you ensure that (a specific person can be assigned a
specific shift on a specific day) once.

 

[Michael]

Duane gave me the advice about the 'Date' field being a keyword. Thanks for the
reminder. The field name is now 'ShiftDate'. That bit about setting up indexes to
prevent the same person from being scheduled twice on the same day is perfect - an
error I've made several times over the past few months - there are so many features
built into Access that I do not know about, and many that I have heard of but do not
understand.

I'm not understanding the concept/significance of the ShiftID/Start/EndTime problem.
The tblShifts holds default information about every shift == ShiftID is an autonumber
field; ShiftName holds a text description of each regular shift for each day (7am RN 1,
7am RN 2, 7am RN 3, 7am RN 4, 9am RN, 11am RN, 3pm RN, etc.); StartTime holds
the default start time for each shift listed in the ShiftName field (7am nurses would start
@ 0700, etc.); EndTime is 12.5 hours after the start time (I suppose this field is silly,
since the employee will work 12.5 hours on a routine basis.

tblSchedule holds the ShiftDate (a date/time field); ShiftID (a FK linked to the tblShifts
PK field); StartTime (date/time field) to hold the shift start time ONLY when it varies from
the 'standard' shift start time listed in the tblShifts. The EndTime helps to document a
short shift (8 or 4 hours instead of the regular 12 hrs).

The final result needs to be a printed schedule for posting in the department. The
desired layout is a report with 14 days (one pay period) displayed on one page. The
date displayed at the top of a column with the 18 shift start times printed in a vertical
line and the staff member assigned to that shift listed immediately to the right of the start
time. Shift times which do not have a staff member assigned should still be displayed
so that employees will have a blank space to write in their name for an extra shift.

Here is my thinking: The query I use to gather data for the report will need to collect
the date and the 18 employees - including the blank space(s) for the standard shift(s)
for which no one is scheduled. If the 07am RN1 is working 0800 - 1630 instead of the
regular 0700 - 1930 shift, I could easily substitute the 0800 start time because it is
being held in a field which is normally a null value. If I eliminate the ShiftID, or
Start/End times it seems that I would be eliminating information which is needed for the
report.

Thanks,
Michael

 

[Scott McDaniel]

The StartTime and EndTime are properties of a particular shift on a
particular day ... with your current scheme, you will be storing data
relevant to the schedule time periods in two places ... tblShift (if the
shift is a "standard" shift) or tblSchedule (if the shift is non-standard
with user-entered start/end times). How will you determine whether a shift
is standard or non-standard? While your query could certainly look at the
dteEndTime field for non-null values (and therefore a non-standard shift),
what then?

Instead, if you store you StartTime and EndTime in tblSchedule, you can
easily calculate exactly when a shift ends (and, presumably, when another
begins). For example, if the 7amRN1 shift on Jan.1, 2004 starts at 4:00 and
ends at 12:00 (8 hours), then if someone were to attempt to add another
nurse to this shift at, say, 10:00 a.m. (2 hours before the shift is
expected to end), a simple VB routine using one or more Date functions could
easily determine that you would have overlapping shifts. While this is a
simplified example, you will hopefully see the issues involved with storing
relevant data in two separate tables.

You can still use your tblShift to autofill default data to your frontend
program, of course.

Note: I would explore two different strategies regarding this: storing the
StartTime and Endtime in tblSchedule, or storing the StartTime and DURATION
(in minutes) of the shift. Either way would give you the proper results, but
depending on the other needs of your app, one storage method may be
preferable.

 

[Duane Hookom]

I failed to notice Michael was proposing to store both a position and time
in a single field. I think this is a bad idea. I would have separate fields
in tblSchedule for Position, StartTime, and EndTime. I would not store
redundant information in tblSchedule.

For some report samples, check the calendar reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

 

[Michael]

Thanks for the extra discussion. Pretty sure I understand the significance of keeping
the time info in the schedule table, rather than having the time information split into two
separate tables. And that was a great link Duane provided. This is turning into quite a
project.

Thanks,
Michael

 

[Scott McDaniel]

Those reports are impressive, Duane. That looks like it was quite a project.
My hat off to ya ...