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 (
schedule_id SERIAL PRIMARY KEY,
anchor_date DATE NOT NULL,
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,
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)
ON DELETE CASCADE
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,
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.