Scheduling Database (Base Schedule + Exceptions)

Question

Currently our employee scheduling for ~800 employees at a 24/7 company is handled with Excel workbooks. I need to move all of the scheduling into an Access employee database I designed that we've been using for years. (NOTE: I know Access isn't the ideal platform for this but it's what I have.)

Each employee has a base schedule such as 2:00am start times with Wed/Thu off. An employee's schedule for any given week will be their base schedule modified by exceptions such as:

• Time-Off requests
• Shift switching with another employee for a day
• Leaves of Absence (basically another form of time off)
• Schedule changes based on company needs made by a scheduling administrator

The database needs only store the base schedule and somehow display a given week's schedule. It doesn't need any advanced logic like scheduling based on availability.

I can see a few ways to implement this. The first one that came to my mind was storing the base schedule and then dynamically generating a given week's schedule as needed by combining the base schedule with tables based on the above exceptions (time-off, switches, etc). However, I can't see how to store the base schedule and how to merge the base with the exceptions to generate a schedule. I would think a table like baseSchedule(PKScheduleID, FKEmployeeID, DayOfWeek, StartTime) but I'm not sure.

Another method would be to generate weekly schedules into a table, for example using the "three table Kimball Star" method described here: http://www.damirsystems.com/?p=466. Basically it creates a table full of dates and has a many-to-many relationship with employees to define a schedule. I dislike that method for many reasons such as needing to check/modify that table at the application level every time time-off, etc is added and the need to "generate" a new schedule into the table. Also, it's possible this will swell to 2,000+ employees and I fear poor Access will explode in a ball of flame having a record for every employee for every day.

Does anyone have any design ideas for implementing the base schedule + modifiers method? I'd love to generate schedules on the fly with queries only but I'm comfortable with using VBA if necessary.

Thank You

Edit 8/19/11 4:30pm: I think I'm going to go with something very similar to bluefeet's answer. Below is the design I mocked up in a blank DB:

Each employee will have a record in the Base table for each day of the week with a start time and the number of hours they're scheduled to work. There's also an Exceptions table listing modifications to the schedule with a date, employee, and his new shift.

For the application level forms and reports I'll pull the base schedule into a recordset with a very sloppy query that outputs something like:

Name  Mon     Tue     Wed     Thu     Fri     Sat     Sun
Alice 6:00 PM 6:00 PM Off     Off     2:00 PM 2:00 PM 2:00 PM
Bob   4:00 PM 4:00 PM 4:00 PM 4:00 PM Off     Off     4:00 PM 

Then, in VBA, I'll also pull the exceptions for a date range (a week) into a recordset. I'll loop through the exception recordset, modifying the base recordset (from above) as I go. Then I'll set the form/report to use the modified recordset. It's a bit inelegant but it'll get the job done well enough.

If anyone has any ideas as how to combine the Base and Exceptions tables with output similar to the above using only queries and no VBA please let me know.

Thanks Again

Answer 1

My suggestion would be to have a Base table with the Employee Schedules, then have a table with the exceptions. I have something similar in a Calendar app. I have an Employee table that contains their normal schedules, then I have a separate table that contains the Exceptions - days off, leave early, training, etc.

EmployeeTable
PK - EmployeeID
EmployeeName
Schedule fields - starttime, endtime, days, etc

Employee_ExceptionTable
PK - EmployeeId
ExceptionTypeId
PK - ExceptionStartDate
ExceptionEndDate

ExceptionTypeTable
ExceptionTypeId
ExceptionName - Vacation, Leave Early, Training

Since the Employee_ExceptionTable has a key of EmployeeId and ExceptionStartDate this will only allow one exception per day but these are exceptions to the base schedule. As I said, I have something similar in my application that monitors about 100 people and it seems to work. This might be a starting point for you.