Scheduling with Date Mod Part 2

20 10 2009

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.

CREATE TABLE schedules (
anchor_date DATE NOT NULL,
title TEXT,
cycle_length INT NOT NULL

The important columns here are anchor_date and cycle_length. For an actual application, you may want some other columns like scheduled_hours, ot_cycle_length or schedule_category.

CREATE TABLE schedule_details (
schedule_id INT NOT NULL,
day_of_cycle INT NOT NULL,
start_time TIME NOT NULL,
work_duration TIME NOT NULL,
lunch_duration TIME,
CONSTRAINT schedule_details_pk PRIMARY KEY
(schedule_id, day_of_cycle, start_time),
CONSTRAINT schedule_details_fk FOREIGN KEY
(schedule_id) REFERENCES schedules(schedule_id)

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.

-- add a 14 day schedule anchored on the 1st Sunday of 2009
INSERT INTO schedules (schedule_id, anchor_date, title,
VALUES (1, '2009-01-04', 'Sample 2 wk schedule', 14);
-- add a variety of shifts
INSERT INTO schedule_details (schedule_id, day_of_cycle, start_time, work_duration, lunch_duration)
VALUES (1, 1, '8:00', '9:00', '0:30'),
(1, 2, '8:00', '9:00', '0:30'),
(1, 3, '7:00', '4:00', '0:00'),
(1, 3, '15:00', '4:00', '0:00'),
(1, 4, '8:00', '9:00', '0:30'),
(1, 5, '8:00', '9:00', '0:30'),
(1, 8, '16:00', '9:00', '0:30'),
(1, 9, '16:00', '9:00', '0:30'),
(1, 10, '16:00', '9:00', '0:30'),
(1, 11, '16:00', '9:00', '0:30');

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.

CREATE OR REPLACE VIEW schedule_calendar AS
SELECT s.schedule_id, c.cal_date, COALESCE(sd.day_of_cycle,
day_of_cycle(c.cal_date, s.anchor_date, s.cycle_length)) AS day_of_cycle,
period(cal_date + start_time, cal_date + start_time + work_duration + lunch_duration) AS work_shift,
work_duration, lunch_duration
FROM schedules s
JOIN info_calendar c ON c.cal_date BETWEEN current_date - 365
AND current_date + 365
LEFT JOIN schedule_details sd ON s.schedule_id = sd.schedule_id
AND sd.day_of_cycle = day_of_cycle(c.cal_date, s.anchor_date, s.cycle_length)
ORDER BY c.cal_date;

The view will grow pretty big once you put it in production and add a few hundred schedules. But we’ll typically be filtering it by a date range and a handful of schedules.

So now we’ve got the schedules and shifts modeled. And we have a view to project them. Obviously there are many other ways to do this. But this one is pretty simple and it scales well.

In the next segment, we’ll put our period data type to work.




3 responses

21 10 2009
David F. Skoll

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: but the only way to handle all the strange possibilities was to write a proper scripting language for expressing date calculations.

It’s really evil.

21 10 2009
Scott Bailey

> It’s really evil.

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.

22 10 2009
David F. Skoll

> I’m not sure how many of the concepts could make the transition to a database schema though.

Oh, very few, I’d imagine. 🙂

> I’m curious to see how you did the calculations for sunrise/sunset and Easter.

It’s GPL’d, so have a look.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: