Designing a Restaurant Reservation System: How to Slice Time Granularity?

Two days ago the author helped a friend review a big‑company interview question: design a restaurant reservation system. The interviewer immediately asked how to slice the reservation time slots, highlighting the gap between interview expectations and production realities.

1. Six dimensions: interview vs. production

Interview standard answer A complete reservation system must address six questions: time granularity, concurrency control, inventory model, lifecycle, consistency, and availability. Time granularity is the foundation; the other five extend from it.

Real production These dimensions are not designed up‑front but evolve. Early versions often start with a simple reservation table and a status field; inventory and reconciliation are added after over‑selling incidents. Priorities among the six dimensions are dictated by business: hotels fear inventory inconsistency, restaurants fear low seat utilization, and attractions fear peak overload.

2. Time‑slot granularity strategies

Strategy 1: Fixed granularity

Interview standard answer Divide business hours into equal‑length slots, each representing a bookable unit. Implement with a simple slot table.

Real production Pure fixed slicing rarely exists alone. Even seemingly fixed slots (e.g., scenic‑spot sessions) are derived from "resource + duration" rules, not manually entered. Restaurants almost never use a fixed‑time‑slot model because they allocate physical tables, not pre‑cut time blocks.

Strategy 2: Rule‑driven dynamic granularity

Interview standard answer Define a service template (table type, duration, available periods) and let the system compute available slots in real time.

CREATE TABLE service_template (
    id BIGINT PRIMARY KEY,
    shop_id BIGINT,
    table_type VARCHAR(32),   -- 大桌/小桌/包间
    duration_min INT,         -- 预估用餐时长（分钟）
    weekly_pattern JSON,      -- 周期模式
    capacity_per_slot INT,
    slot_interval_min INT
);

Real production Real‑time calculation becomes a bottleneck at large scale. More problematic is rule maintenance: merchants configure rules themselves, and mistakes (e.g., a 25‑hour lunch) force the system to provide fallbacks. Rule explosion (special dates, table types, channels, member levels) makes merchants reluctant to modify rules.

Strategy 3: Pre‑materialized slots

Interview standard answer Pre‑compute slots and store them; reads are fast, trading space for time.

CREATE TABLE appointment_slot (
    id BIGINT PRIMARY KEY,
    shop_id BIGINT,
    table_type VARCHAR(32),
    slot_date DATE,
    start_time TIME,
    end_time TIME,
    total_capacity INT,
    booked_count INT DEFAULT 0,
    status TINYINT DEFAULT 1,
    version INT DEFAULT 0
);

Real production Pre‑materialized slots are mainstream but their cost is often underestimated. The slots are derived data, not the source of truth, which is the merchant's tables and schedules. When a merchant changes business hours, all affected slots must be recomputed. Large‑scale systems use incremental updates triggered by events, and stale slots can cause customer complaints for high‑demand areas.

3. Five engineering challenges

Challenge 1: Overlapping slots

Interview standard answer On each reservation insert, check whether the new time range overlaps existing confirmed or pending reservations; reject if it does.

SELECT COUNT(*) FROM appointment_record
WHERE shop_id = ? AND table_type = ?
  AND status IN ('CONFIRMED', 'PENDING')
  AND start_time < :new_end_time
  AND end_time > :new_start_time;

Real production Overlap checks cannot rely on a single SQL in a distributed environment. Multiple services may write concurrently, so database constraints (unique indexes, exclusive locks) are needed. Moreover, restaurant "seat holding" is not a strict time‑block mutex; it is "reserve this table until X o'clock" with automatic turnover, so a strict slot model creates phantom conflicts.

Challenge 2: Inventory fragmentation

Interview standard answer After cancellation, if the remaining time is insufficient for a full table, mark the slot as fragmented and do not reopen it.

Real production Fragmentation is an artifact of a fine‑grained inventory model. In practice many systems do not pre‑deduct granular inventory; they track table status—if a table is free, the next party can be seated, eliminating the notion of a "30‑minute slot split into 5 + 25".

Challenge 3: Concurrency and overselling

Interview standard answer Three‑layer protection: Redis pre‑deduction → DB optimistic lock → MQ asynchronous persistence.

-- Redis Lua atomic pre‑deduction
local remaining = redis.call('DECR', KEYS[1])
if remaining < 0 then
  redis.call('INCR', KEYS[1])
  return -1
end
return remaining

UPDATE appointment_slot
SET booked_count = booked_count + 1, version = version + 1
WHERE id = ? AND booked_count < total_capacity AND version = ?;

Real production This three‑layer approach introduces double‑write inconsistency: Redis may deduct while the DB has not persisted, Redis failures leave stale counts, and MQ retries cause duplicate deductions. A robust solution treats the DB as the single source of truth, using transactions, row locks, and unique constraints; Redis is only a cache or rate‑limit gate.

Challenge 4: Timeout release

Interview standard answer Send a delayed message (e.g., 15 minutes); if the reservation is still pending, mark it expired and release capacity.

message.setDeliverTime(now + 15 * 60 * 1000);
mqProducer.send("reservation.timeout", message);
if (res.getStatus() == PENDING) {
    res.setStatus(EXPIRED);
    slotService.releaseCapacity(res.getSlotId());
}

Real production Two hidden pitfalls: race conditions (payment may complete just as the timeout fires) require a state‑machine check that only PENDING→EXPIRED is allowed; and delayed queues are at‑least‑once, so duplicate timeout messages must be idempotent, otherwise capacity could be released twice.

Challenge 5: Holiday and special schedule handling

Interview standard answer Create a schedule‑override table with highest priority to supersede template rules.

CREATE TABLE schedule_override (
    id BIGINT PRIMARY KEY,
    shop_id BIGINT,
    override_date DATE,
    override_type TINYINT,   -- 1‑休息 2‑加班 3‑包场
    time_ranges JSON,
    capacity_override INT
);

Real production Overrides are themselves overridden: national holidays, temporary closures, channel‑specific promotions, etc., are scattered across HR, marketing, and merchant systems. A static table cannot capture this complexity. Production systems aggregate events from all sources into a daily effective rule set rather than storing a single static table.

4. Overall architecture

Interview standard answer User → API gateway → reservation service; underneath Redis for pre‑deduction, MQ for async persistence, DB for storage, plus reconciliation and scheduled tasks.

Real production The diagram is correct as a skeleton, but actual systems contain extensive glue: reconciliation jobs, compensation tasks, monitoring, feature flags, and degradation plans. The architecture evolves step‑by‑step—single DB, then cache, then service split, then MQ—so the final picture is the result of incremental evolution, not a one‑shot design.

5. Slot visibility

Interview standard answer Build a visibility rule engine: members see slots 7 days ahead, regular users 3 days, promotional slots only on the homepage.

return slots.stream()
    .filter(s -> s.getBookableTime().isAfter(now))
    .filter(s -> minutesBetween(now, s.getStartTime()) >= bufferMin)
    .filter(s -> user.canAccess(s.getVisibilityLevel()))
    .collect(toList());

Real production Visibility rules are tightly coupled with membership and marketing systems; they are not an isolated module. Changing a rule often leaves cached slots stale, causing users to see outdated results. Proper invalidation and cache refresh are essential.

Conclusion

Interview tests whether you can think of all six dimensions and articulate three granularity strategies; production tests whether you pick the right model, handle data freshness, avoid double‑write traps, and manage rule explosion. Mastering both sides shows true system‑design competence.