Database planning (basic schedule + exceptions)

Question

Currently, our employee planning ~ 800 employees at the company 24/7 is being processed by Excel workbooks. I need to move the entire schedule to the Access employee database that I developed that we have been using for many years. (NOTE: I know that access is not an ideal platform for this, but that is what I have.)

Each employee has a basic schedule, such as starting work at 2:00 in the morning with the conclusion of Wed / Thu. The employee schedule for any week will be their base schedule, modified using exceptions, such as:

• Deferral Requests
• Change of switching with another employee during the day
• Absence sheets (mostly a different form of leave)
• Schedule changes based on company needs made by the planning administrator

The database should store only the basic schedule and somehow show the specified schedule of the week. This does not require advanced logic such as availability planning.

I see several ways to implement this. The first thing that occurred to me was to save the base schedule, and then dynamically generate the given weekly schedule as needed, combining the base schedule with tables based on the above exceptions (timeout, switches, etc.). However, I don’t see how to save the base schedule and how to merge the base with exceptions to create a schedule. I would think of a table like baseSchedule (PKScheduleID, FKEmployeeID, DayOfWeek, StartTime), but I'm not sure.

Another method would be to generate weekly schedules in a table, for example, using the Kimball Star Three-Table Method described here: http://www.damirsystems.com/?p=466 . Basically, he creates a table full of dates, and has a many-to-many relationship with employees to determine the schedule. I do not like this method for many reasons, for example, if necessary, check / modify this table at the application level each time it is disconnected, etc., and the need to “generate” a new schedule in the table. In addition, it may be up to 2,000 + employees, and I am afraid that bad access will explode in a flame that has a record for each employee for every day.

Does anyone have any design ideas for implementing the base schedule + modifiers method? I would like to generate graphs "on the fly" only with requests, but it is convenient for me to use VBA if necessary.

thanks

Edit 8/19/11 4:30 PM: I think I'm going to go with something very similar to a bluish answer. Below is the design that I used up in an empty database:

Each employee will have an entry in the base table for each day of the week, indicating the start time and the number of hours for which they plan to work. There is also an exception table that lists the changes in the schedule with the date, employee and his new shift.

For application level forms and reports, I pulled out the base schedule into a recordset with a very messy 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 will also select exceptions for the date range (week) in the recordset. I will go through the error record set by changing the base record set (above) when I go. Then I set up a form / report to use the modified recordset. It is a little inelegant, but it will do the job well enough.

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

Thanks again

Answer 1

My suggestion would be to have a base table with employee schedules, and then have a table with exceptions. I have something similar in the Calendar app. I have an Employee table that contains their usual schedules, then I have a separate table containing Exceptions - weekends, early holidays, tuition, 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 Employee_ExceptionTable has the key EmployeeId and ExceptionStartDate, this will only allow one exception per day, but these are exceptions from the base schedule. As I said, I have something similar in my application that tracks about 100 people and seems to work. This may be the starting point for you.

Answer 2

I think this is one way to do this:
You have 3 tables:
employees (employee_id PK), scheduler (employee_id FK) and timeOff (employee_id FK)

scheduler
date
employee_id (FK employee table)

start_time
end_time
day_off
comments

The employee table will have basic information about employees.

Table

scheduler will have schelude data, as well as a flag for day_off (set to 1 if it is an output), and a comment field for any type for a comment.

If day_off is set to 1, you will have a description of day_off in the timeOff table.
timeOff , which you can design in any way. You will have a filing date so you know which day. You can have a mind field, where 1 is a day of vacation, 2 is a sick day, 3 is a personal day, 4 is a switch with another employee, and so on. You can have a comment box here, as well as the employee_id of the person with whom the schedule was switched using