Scheduling with Date Mod Part 2

20102009

So as the title suggests, this is the second part of a series on scheduling. In the first part we did some basic setup. Now we are ready to dig in. The goal here is to be able to model any recurring pattern no matter the cycle.

The linchpin in this solution is to define an anchor date and the cycle length for every schedule. Given those two pieces of information, we can calculate the day of cycle for any given date using date mod. So lets create a couple of tables.

The important pieces of this table are the schedule_id and day_of_cycle columns. There are quite a few options for how we model the actual shift. We could have used INT to represent minutes or seconds past midnight or we could have used an INTERVAL. But one important design decision was to model the work duration instead of the end time. This makes it easier to model shifts that start in the evening and end in the morning.

Now we’ll add a sample schedule to work with. We’ll make a 2 week schedule with some day shifts, split shifts and swing shifts.

So now we can model our schedules and shifts, but we need to be able to project those to any date in the past or future. We’ll do that with a view and the help of the day_of_cycle function from the Chronos toolkit.

Related

Actions

Information

3 responses

21102009

David F. Skoll(12:17:39) :

This is fairly cool, but real-world scheduling is completely evil. For example: “Garbage pickup happens in five shifts (Monday to Friday). However, if there’s a holiday, then that day’s shift (plus all the subsequent shifts for that week) are delayed by one day.”

Doing something like that in a database is a world of pain. I wrote a tool that can handle these and many other weird scheduling issues: http://www.roaringpenguin.com/remind but the only way to handle all the strange possibilities was to write a proper scripting language for expressing date calculations.

There’s no doubt about that David. The garbage pickup would be pretty easy to handle. Maybe I can work it in to the examples.

Remind looks like an interesting app. I’m not sure how many of the concepts could make the transition to a database schema though. I’m curious to see how you did the calculations for sunrise/sunset and Easter.