Scheduling for Emergency Department

[Michael]

Can someone give input on table design?

I'm setting up a database which will be used to generate employee schedules for the
Emergency Department. I need to schedule 18 employees every 24 hours. Shifts are
mostly 12 hrs long, with some minor deviations. The shifts start at different times during
the 24 hr period, and employees will not always be working the same shift hours from
day to day.

I've generated the following table structure and am using it with some success:
The advantage here is that I enter the date once and then use the tab-or-enter key to
start filling in the shifts from morning to night using a lookup field for the employee. I'm
having some trouble getting the information out of the tables using this structure.

tblEmployees
EmplID
Name
etc.

tblSchedule
Date
7am1
7am2
7am3
7am4
9am
11am
etc.

My other alternative is:
Data entry is much more cumbersome with this structure (entering a date with each
record - eighteen entries for each day), but getting information out of the tables for
reports is simple.

tblEmployees
EmplID
Name
etc.

tblShifts
ShiftID
ShiftName

tblSchedule - this table holds linked fields to the other tables.
Date
ShiftID
EmployeeID

From a developers standpoint, which table structure makes the most sense??

Thanks for the input.
Michael

 

[Duane Hookom]

I would not put times/shifts into field names. What happens when the shifts
change? Are you going to have to change your fields, queries, forms,
reports,.... You are almost always best off normalizing your data. Also,
avoid using reserved words as field names. Don't use Name, Date, Time, etc.

 

[mm]

Obviously, I'm a novice. I thought the tables were already normalized. They seem to
be collections of similar data. Can you give me some idea of how to further normalize?
I hadn't thought about Date being a reserved word - thanks.

tblEmployees -- <holds employee demograph info>
PersonnelID (PK)
LastName
FirstName
LoginID
TitleID
Email
AddressPermanent
CityPermanent
State
ZipPermanent
AddressTemporary
CityTemporary
ZipTemporary
ScheduleGroup
WorkStatus
FTE
PhoneHome
PhoneMobile
PhonePager
cboAgencyID
SchedulePreferences

tblCalendar -- <holds the date and who is working which shift>
PayPeriodID
CalendarArchive
Date
07RN1
07RN2
07RN3
07RN4
09RN
11RN
15RN
19RN1
19RN2
19RN3
19RN4
AMC
StartAMC
EndAMC
PMC
StartAMC
EndAMC
AMEHT
PMEHT
AMMUC
PMMUC
NOCMUC
Flex1
StartFlex1
EndFlex1
Flex2
StartFlex2
EndFlex2
Flex3
StartFlex3
EndFlex3
Flex4
StartFlex4
EndFlex4
Flex5
StartFlex5
EndFlex5

 

[Duane Hookom]

You should have a tblSchedule that has one record per person per shift in a
properly normalized application. You could have a table of shifts
tblShifts
=========
ShiftID
ShiftStart
ShiftEnd
ShiftQty

The schedule table would be like
tblSchedule
============
SchedID
PersonnelID
ShiftID
ShiftDate

I don't know what all your other fields are for but it looks like they
repeat.

 

[mm]

It looks a <bit> like the second set of tables in my original post - way at the bottom of
the page. This gives me something to chew on for a while. I appreciate the help.
Hope you won't mind if I repost in the future with the new table structures. Take care.

Michael

 

[mm]

But wait, what do I do with the regularly occuring variances in shift start times?? For
example, the regular PMC <PM shift charge RN> starts at 1900 and ends at 0730. But
one of our charge nurses has an extended commute and starts at 1700, ending at
0530. One of our NOCEHT <NOC shift tech> starts an hour early for similar reasons.
The other techs start at 1900, this one starts at 1800.

Do I add a unique shift entry in the schedule table to account for every shift variation?

Maybe I should just sleep on this. Thanks again.

Michael

 

[Duane Hookom]

You can add start and end times to tblSchedule and remove the ShiftID. The
tblShifts can still be used to pick the times.