Conference Rooms

The problem

"Design a conference room booking system" for one company: 500 rooms on several floors. People find a free room for a time, book it with POST /bookings, and check in when they arrive.

This practice problem reuses the pattern of Design Hotel Booking: the database decides who gets the last room, and a search only shows. What's new: time ranges instead of nights, finding one room among 500, weekly meetings, and freeing rooms nobody uses. Park catering and desks.

The numbers: one database is enough

From the interviewer: 20,000 employees and 500 rooms. Say each person searches for a room about 5 times a day: 100,000 searches a day, about 1 a second (100,000 seconds to a day, 86,400 really), and about 5 a second at busy hours. Say each room is booked about 8 times a day: 4,000 bookings a day, about 1.5 million rows a year (730 MB at 500 bytes each).

One database server answers about 5,000 lookups a second, 1,000 times that load. So say it plainly: one database is enough, with a replica to take over if it fails. No cache, no sharding, no separate search service. The hard part is correctness: never two meetings in one room.

Time ranges: the overlap test

A booking is a row with a room, a start and an end, the start included and the end not. Two bookings of the same room clash when each starts before the other ends:

clash = new.start < old.end AND new.end > old.start

Against Ana's 2:00 to 3:00 in room 3B, that one test flags 1:30 to 2:15, 2:15 to 2:45, 2:45 to 4:00 and 1:15 to 3:30, and lets through 1:00 to 2:00, 3:00 to 3:30 and 3:30 to 4:00. Touching is fine: a meeting that ends at 2:00 frees the room at 2:00.

Two bookings at once

Room 4A is free all afternoon when Ben books 2:00 to 3:00 and Cy books 2:15 to 2:45 at the same moment. Checking for an overlap, then inserting, lets both in: each check runs before the other's insert, the write skew from Hotel Booking. A unique key on room and start time misses it too: 2:00 and 2:15 are different keys.

The fix is a conditional insert, one room at a time:

BEGIN;
SELECT id FROM rooms WHERE id = '4A' FOR UPDATE;     -- lock the room
SELECT count(*) FROM bookings WHERE room = '4A'
  AND start_at < '15:00' AND end_at > '14:00';       -- 0: free
INSERT INTO bookings VALUES ('4A', '14:00', '15:00', 'ben');
COMMIT;

Cy's transaction waits on the lock, then finds Ben's booking and is refused. Hotel Booking's way works too: a row per room per 15-minute slot, 4 conditional updates an hour, every slot or none. It writes 4 rows an hour and forces times onto the grid; at 4,000 bookings a day either is fine.

Finding a room for 8 at 2 pm

Ana needs 8 seats from 2:00 to 3:00, on floor 3 if possible. Of the rooms on floors 3 and 4, 3A (4 seats) is too small, 3B (booked 1:30 to 2:30) and 4A (Ben's 2:00 to 3:00) overlap her hour, and 3C (10 seats, booked from 3:00) is free. 3D's 20 seats should stay free for a big meeting. So search offers the smallest free room that fits, on her floor first: 3C.

SELECT id FROM rooms r WHERE seats >= 8 AND NOT EXISTS
  (SELECT 1 FROM bookings b WHERE b.room = r.id
     AND b.start_at < '15:00' AND b.end_at > '14:00')
ORDER BY abs(floor - 3), seats;

With 500 rooms and an index on bookings by room and start, it takes milliseconds. Two people may still pick 3C at once: the conditional insert decides.

Repeating meetings

Ben's team books room 4B every Tuesday from 10:00 to 11:00, starting Nov 3, with no end date. Store the rule once, as in Calendar Invites, but the room must be free for each meeting. So booking the series checks the next 6 months, 26 Tuesdays up to Apr 27, in one transaction, and gives each one a booking row. If Dec 22 is already taken, book the other 25 and tell Ben which one to move. A nightly job adds the next Tuesday as time moves on.

Checking only the next meeting lets someone take a Tuesday in March from under the series; checking every meeting the rule makes never ends, and even 10 years is about 520 rows for meetings that may never happen.

Rooms nobody uses

Say about 1 in 4 booked meetings never happen: about 1,000 a day. Each room has a tablet by its door, and a meeting must check in within 10 minutes of its start. At 2:10, a job finds Ana's booking b42 in room 3C without a check-in and sets it released, only if it still has none, so a check-in at 2:09 and the job can't both win. The room is free from 2:10. Releasing at the start time takes rooms from people walking down the hall; asking organizers to cancel fails because they forget.

Same pattern as Hotel Booking

One conditional write decides who gets the room, never what each person saw; search only shows; an idempotency key makes a retried booking safe; a release changes a row only if it's still in the state it expects.

Summary

• Scope: book a room for a time range, repeats, no-shows.
• Data: a row per booking; a clash is start < other end and end > other start.
• Numbers: about 5 searches a second at busy hours, 4,000 bookings a day: one database and a replica.
• Two at once: lock the room, check, insert.
• Finding a room: the smallest free room that fits, her floor first.
• Repeats: the rule, and 26 weeks of meetings checked now.
• No-shows: check in within 10 minutes, or the room is released.