Designing a Data Model for Scheduling

Time is one of the most difficult things to get right in software. Unlike other data types — strings, integers, booleans — time is fluid, relative, and often messy.

When we talk about modeling time for scheduling systems, we aren’t just storing points in time; we are managing intervals, preventing collisions, and handling the quirks of human clocks. In this post, we’ll design a database schema for a Room Booking System. We will start with a naive implementation and evolve it to handle real-world complexities like double-booking prevention, recurrence, and time zones.

Iteration 1: The Naive Approach

Let’s start where most projects start: two timestamps.

We have a rooms table and a bookings table. A booking links a room to a specific time slot.

CREATE TABLE bookings (
  id SERIAL PRIMARY KEY,
  room_id INTEGER REFERENCES rooms(id),
  start_at TIMESTAMP WITH TIME ZONE NOT NULL,
  end_at TIMESTAMP WITH TIME ZONE NOT NULL
);

The Query

To find all bookings for a room today, beginners often query for bookings that start today. But what about a multi-day workshop that started yesterday?

The correct logic for finding “occupancy” is checking if the intervals overlap.

-- Find any booking that overlaps with "Today"
SELECT * FROM bookings
WHERE room_id = 1
  AND start_at < '2025-08-04 00:00:00+00' -- Ends after today starts
  AND end_at   > '2025-08-03 00:00:00+00'; -- Starts before today ends

The Problem: Race Conditions

The biggest issue here isn’t the query, but data integrity. If two users try to book “Room A” for 2:00 PM at the exact same millisecond, your application code might check availability for both, see it’s empty, and insert both records.

You now have two meetings in the same room.

To fix this, you would typically need to lock the table (LOCK TABLE) or row (SELECT FOR UPDATE) before reading. This kills concurrency and performance. We need the database to enforce this rule for us.

Iteration 2: Ranges and Exclusion Constraints

PostgreSQL offers a superpower for temporal data: Range Types. Instead of separate start and end columns, we can use a tsrange (timestamp range).

To use this with a specific room ID, we first need to enable a specific extension that allows us to index scalars (integers) alongside ranges (GIST).

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE bookings (
  id SERIAL PRIMARY KEY,
  room_id INTEGER REFERENCES rooms(id),
  duration tsrange NOT NULL,
  
  -- The Magic: Prevent overlapping ranges for the same room
  EXCLUDE USING GIST (
    room_id WITH =,
    duration WITH &&
  )
);

Why this is better

1. Atomic Integrity: The database engine itself enforces that no two rows for the same room_id can have overlapping (&&) time ranges. No application-level locking is required.
2. Simpler Math: Queries become more expressive. Postgres ranges are “closed-open” [) by default, meaning they include the start time but exclude the end time — perfect for back-to-back meetings.

To check if a room is available:

SELECT NOT (duration && '[2025-08-03 14:00, 2025-08-03 15:00)') 
FROM bookings 
WHERE room_id = 1;

Iteration 3: The Recurrence Nightmare

So far, we’ve handled single events. Now the requirement comes in: “I want to book this room every Monday at 10 AM for the next year.”

There are two main schools of thought here: Expansion vs. Rule Storage.

Approach A: Rule Storage (RFC 5545)

You store a standard recurrence rule string, like FREQ=WEEKLY;BYDAY=MO.

• Pros: Infinite recurrence takes almost no storage space.
• Cons: Querying is a nightmare. “Is the room free next Tuesday?” requires parsing the rule and calculating occurrences in memory. You cannot use database indexing or exclusion constraints easily.

Approach B: Expansion

You insert 52 individual rows into the bookings table.

• Pros: Queries remain simple. SELECT * works. Exclusion constraints still prevent double bookings.
• Cons: If the user changes the time, you have to update 52 rows. Infinite recurrence is impossible.

The Hybrid Solution

Store the rule and expand the occurrences for a limited window (e.g., 1 year or 5 years).

CREATE TABLE recurring_patterns (
  id SERIAL PRIMARY KEY,
  rrule TEXT NOT NULL -- e.g. "FREQ=WEEKLY;BYDAY=MO"
);

CREATE TABLE bookings (
  id SERIAL PRIMARY KEY,
  pattern_id INTEGER REFERENCES recurring_patterns(id), -- Nullable for single events
  room_id INTEGER REFERENCES rooms(id),
  duration tsrange NOT NULL
  -- ... exclusion constraint ...
);

How it works in practice:

1. Create: When a recurring event is created, save the rrule, calculate the instances for the next year, and insert them into bookings.
2. Read: Queries are fast because they just hit the bookings table.
3. Update: If the pattern changes, delete all bookings with that pattern_id that are in the future, and re-expand the new rule.

Handling Exceptions

This “Hybrid/Expansion” model makes handling exceptions — like “Cancel just the meeting on Monday the 12th” — trivial.

In the Rule Storage approach (Approach A), you would need to store an EXDATE (Exception Date) alongside the rule, and your query logic normally has to parse the rule and check the exclusion list every time it generates instances.

In our Expansion approach (Approach B), because every future meeting is a real row in the database, you simply delete that specific row.

• Cancel one occurrence: DELETE FROM bookings WHERE id = 123;
• Reschedule one occurrence: UPDATE bookings SET duration = ... WHERE id = 123; (The pattern_id remains, keeping it linked to the series).
• “This and all future”: Update the original pattern’s end date, and re-expand a new pattern starting from today.

This flexibility is why the expansion model is often superior for applications that need rich interactivity (like dragging and dropping a single instance of a recurring meeting).

Iteration 4: Time Zones (The “Local Time” Problem)

We used TIMESTAMP WITH TIME ZONE (UTC) in Iteration 1. This is generally best practice for history (logs, past transactions). However, future scheduling requires a nuance: User Intent vs. Absolute Time.

If I book a meeting for “9:00 AM New York Time” on a date 6 months from now, and in the interim, the government changes the Daylight Saving Time rules (which happens more often than you think), what should happen?

1. If stored purely as UTC: The meeting stays at the absolute UTC moment. If DST rules change, the meeting might visually shift to 8:00 AM or 10:00 AM local time. This violates the user’s intent.
2. If stored as Wall Clock Time: The meeting stays at “9:00 AM”, effectively shifting its UTC timestamp.

Best Practice: Dual Storage

For future appointments, Wall Clock Time (plus the intended Time Zone ID) is the source of truth for the user’s intent, while UTC is the source of truth for the database’s mechanics (ordering, collision detection).

CREATE TABLE bookings (
  -- ...
  -- 1. The Source of Truth describing User Intent
  local_start_time TIMESTAMP WITHOUT TIME ZONE, -- 2025-12-25 09:00:00
  time_zone TEXT,                               -- 'America/New_York'
  
  -- 2. The Derived Truth for Database Mechanics (Collision Checks)
  derived_utc_range tsrange                     
);

The Strategy:

• Insertion: Your app calculates the derived_utc_range based on the current known timezone rules so the Exclusion Constraint can do its job.
• Maintenance: You run a background job that listens for tzdata (timezone database) updates. When the rules for ‘America/New_York’ change, it recalculates the derived_utc_range for all future bookings.
• Display: When showing the calendar to a user in Tokyo, you take derived_utc_range, and convert it to ‘Asia/Tokyo’ time. When showing it to the organizer, you might show the local_start_time and time_zone to confirm it matches their original request.

Use UTC for the mechanics of the system (overlapping, sorting, querying), but preserve the Wall Clock variables to ensure you can regenerate that UTC reality if the world’s definition of time changes.

Summary

Designing for time requires anticipating how users perceive time, not just how computers store it.

1. Start Simple: Use UTC timestamps, but know their limits for future events.
2. Enforce Integrity: Use Database Constraints (specifically Postgres EXCLUDE with btree_gist) to prevent overlaps at the engine level.
3. Handle Recurrence: Prefer expanding events for query performance over calculating rules on read.
4. Respect Locality: For future events, store the intended “Wall Clock” time and Zone, and treat UTC as a derived value for collision detection.

Time is complicated, but your data model doesn’t have to be fragile.