根据每周计划确定可用性

Question

Kind of a unique problem here, but one I thought I'd see if anyone had some insight into from experience.

Need to store a weekly schedule for call center employees in a web application. The web application has three entities:

Employees - Belong to groups
Employee groups - Contain employees, have a base schedule that is applied to new employees added to group
Administrators - responsible for maintaining employee groups. For the sake of the example, Administrators can also be employees.

Requirements

• Administrators can change the base schedule for an Employee Group

• Administrators can choose to allow Employees to deviate from the schedule, controlled by the Administrator

• Administrators can further choose to allow Employees to deviate from the schedule themselves

• Administrators use a UI widget to determine the schedule for an Employee Group. Schedules come in three types, sorted by general horribleness ASC

  • Type 1 - 24/7 availability (representing this as a simple flag on the schedule vs marking full Mon-Sun availability)
  • Type 2 - A set start/end time, or group of start/end times for the selected days. (Ex. 9a-11a, 1p-4p, Mon-Fri)
  • Type 3 - A variable group of start/end times, varying by day (Ex. 9a-12p, Mon, 1p-4p, 5p-9p, Tue, etc)

• Administrators can change schedule types at any time through UI

• Employees that are allowed to deviate can also change schedule types at any time through the UI

• Lookups must be fast, as we have to reach into a separate DB for more info on available employees

• For good measure, availability needs to be calculated relative to the callers timezone. So there is a chance that a group in EST with a Type 2 schedule may not actually be available, depending on where the caller is placing the call from

I did talk them down into allowing us to just blow out the schedule entries in the event that the original schedule could not be easily translated into the new schedule based on UI requirements. Best example would be Type 3 -> Type 2.

However, an additional requirement came down yesterday (3 working days til deadline), that for Type 3, user's must be able to specify time ranges that span past midnight. For example:

Start [Monday] at [9:00pm] until [Tuesday] at [3:00am] [Add new time block]
Start [Tuesday] at [8:00am] until [Tuesday] at [5:00pm] [Add new time block]

Also need to detect schedule overlaps and adjacencies at merge them automatically when changes are made through the Type 3 UI.

Example schema that should facilitate quick lookups given a time, day of week, and a group_id

employee_schedule
_________________
id (int) auto-inc // PK
employee_id (int) // FK
employee_group_id (int) // FK
start_day (int) // 0-6
start_time (time)
end_day (int) // 0-6
end_time (time)

However the UI - for type 3 at least - makes adding/editing/updating an exercise in tedium, given that you can't trust user input.

Has anyone come across a similar problem? I'm trying to figure out if I should do a bunch of front-end validation to detect overlaps/adjacencies and merge them prior to sending them to the server, or if PHP would be better suited. Or if there is a rock-solid argument against the whole thing, ha. Any insight would be appreciated.

Answer 1

My system involved two tables:

• Sheduled events such as regular appointments as well as any other obligations.
• Rule based recurring events which can be daily, weekly, etc. That included rules for when holidays and lunch breaks occur.

Appointments were made either according to providers or services. Therefore, the shedule table of the database had a system for classifying different types of events, whether it involved a specific provider or a specific service. And it also contained a client id for each event if applicable.

I also had tables for each provider and what services he could provide. If a provider was available for a given hour, then the availability of the service was calculated based on his availablity as well as well as the availability of other providers. It also depended on the type of service.

The administrator could edit the characteristics of services:

• The duration of the service.
• The number of clients per provider per unit duration. With some services it was just one on one, but other service may last an hour, and during that hour, one provider may be able to attend to multiple clients.

The system was based on average work load. If the characteristics of a service were overloading the providers, the administrator could simply adjust the service characteristics in the database to limit the number of appointments scheduled.

Another thing I did was break up the units of time into 15 minute blocks (this was actually a configuration setting that could be changed if necessary). Therefore all scheduling and rules began and ended with the blocks. I had a set of conversion functions in my classes to convert, for example, between timestamps and block units, rounding off to the nearest block.

Putting it all together was quite a tasked. Based on all the data of shedules, rules, service types, etc., I had to come up with arrays for a given duration to show the availability of providers and services. For example, for a given week and a given service, my classes had to generate an array to show the number appointments that could be scheduled for any block of time. To do that, I thought in terms of "transparencies" that could be overlayed. If there are 3 providers for a given service, then I had to overlay their schedules as if they were transparencies. If the a given block of time had all three providers available, the transparency could be considered clear for scheduling 3 * Serviceload. However, the transparencies would be more and more gray according to the unavailability of the providers. If all three were unavailable, the block would be "blacked-out", so that no appointments could be scheduled.

After I had all my data available in scheduling arrays, I incorporated it all with the jQuery FullCalendar class for drag and drop scheduling.