Backend Design for Restaurant Reservation System

Problem

Design the database schema and RESTful API for a restaurant reservation system. The service must allow customers to search restaurants, view tables, create/update/cancel reservations, and enforce that tables cannot be double-booked for overlapping time ranges.

Solution

1. Requirements Analysis

Functional Requirements:

Customers can search restaurants and available tables.
Customers can create, view, update, and cancel reservations.
The system must prevent double-booking of the same table for overlapping reservation time ranges.
Restaurant staff can manage tables and view reservations.

Data Requirements:

Store restaurant metadata (name, address, timezone).
Store table information (restaurant_id, capacity, section/name).
Store customer profile (name, email, phone, created_at).
Store reservations (customer_id, table_id, start_time, end_time, party_size, status, audit timestamps).

Assumptions:

Reservations are contiguous time ranges; we store start_time and end_time as timezone-aware timestamps.
Business rules (default reservation duration, cancellation windows) are enforced at the application layer.

2. Conceptual Design (Entities & Relationships)

Core Entities:

Restaurant — venue metadata.
Table — physical seating that belongs to a restaurant.
Customer — person making bookings.
Reservation — a booking linking a customer to a table for a time range.

Relationships:

Restaurant 1 — N Table
Table 1 — N Reservation (over time)
Customer 1 — N Reservation

3. Logical Design (ER Diagram)

erDiagram
    RESTAURANT {
        int restaurant_id PK
        varchar name
        varchar address
        varchar timezone
    }
    TABLE {
        int table_id PK
        int restaurant_id FK
        varchar name
        int capacity
        varchar section
    }
    CUSTOMER {
        int customer_id PK
        varchar name
        varchar email
        varchar phone
        timestamptz created_at
    }
    RESERVATION {
        int reservation_id PK
        int customer_id FK
        int table_id FK
        timestamptz start_time
        timestamptz end_time
        int party_size
        varchar status
        timestamptz created_at
        timestamptz updated_at
    }

    RESTAURANT ||--o{ TABLE : has
    TABLE ||--o{ RESERVATION : "has_reservations"
    CUSTOMER ||--o{ RESERVATION : makes

Table descriptions

`restaurant`

restaurant_id (PK): surrogate primary key. Using integer serial keeps joins efficient.
name, address, timezone: basic metadata used in searches and for displaying available times in the correct timezone.

`restaurant table`

table_id (PK): surrogate id for the physical table.
restaurant_id (FK): links the table to its restaurant.
name: optional human-friendly label (e.g., T1, Table A).
capacity: maximum number of guests the table can seat. Used to match party_size when auto-assigning tables.
section: optional field to group tables by floor/area (useful for staff views and allocation policies).

`customer`

customer_id (PK): surrogate id for customers.
name, email, phone: contact information. email/phone are useful for confirmations and notifications.
created_at: auditing when the profile was created.

`reservation` (detailed)

reservation_id (PK): surrogate id for the booking record.
customer_id (FK): which customer made the booking.
table_id (FK): the assigned table. In auto-assign flows this is chosen by the server; in manual flows the client may provide this.
start_time, end_time: time range of the reservation stored as TIMESTAMPTZ. We store both endpoints to support variable-length bookings and to compute period ranges.
period (generated tsrange): a computed range field created from start_time and end_time to make range overlap checks efficient and support the exclusion constraint. Using TSRANGE allows use of GIST indexes and the && operator for overlap checks.
party_size: number of guests expected. This drives table assignment (capacity must be >= party_size) and reporting metrics (e.g., occupancy, revenue per cover). It should be an integer > 0.
status: small textual state for the reservation lifecycle. Typical values:
- OPEN or PENDING — created but not confirmed (optional confirmation step)
- CONFIRMED — booked and expected to arrive
- SEATED — guests have arrived and are using the table
- COMPLETED — guests finished and table cleared
- CANCELLED — booking cancelled (soft-delete so historical data stays readable)
- NO_SHOW — guest didn’t arrive within a grace window
created_at, updated_at: audit timestamps to track when the reservation was created and last modified. updated_at is maintained by a trigger.

Rationale and behaviors

We store party_size explicitly because group size affects table choice, revenue estimates (covers), and validation rules (max capacity).
status enables soft workflow transitions without deleting historical rows. Soft-cancels (CANCELLED) keep analytics intact.
The period field and exclusion constraint provide a strong DB-level invariant: no two reservations for the same table may have overlapping period ranges. This is preferred over solely application-level checks because it prevents race conditions in high-concurrency scenarios.
Using TIMESTAMPTZ ensures correct handling across timezones (important for multi-location chains or for scheduling reminders in local time).

4. Physical Design (SQL Schema)

Below is a PostgreSQL schema. It uses an exclusion constraint on a generated tsrange to prevent overlapping reservations for the same table.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62


-- Extension for btree_gist (required for some exclusion patterns)
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE restaurant (
    restaurant_id   SERIAL PRIMARY KEY,
    name            TEXT NOT NULL,
    address         TEXT,
    timezone        TEXT DEFAULT 'UTC'
);

CREATE TABLE rest_table (
    table_id        SERIAL PRIMARY KEY,
    restaurant_id   INT NOT NULL REFERENCES restaurant(restaurant_id) ON DELETE CASCADE,
    name            TEXT,
    capacity        INT NOT NULL CHECK (capacity > 0),
    section         TEXT
);

CREATE TABLE customer (
    customer_id     SERIAL PRIMARY KEY,
    name            TEXT NOT NULL,
    email           TEXT,
    phone           TEXT,
    created_at      TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE reservation (
    reservation_id  SERIAL PRIMARY KEY,
    customer_id     INT NOT NULL REFERENCES customer(customer_id) ON DELETE CASCADE,
    table_id        INT NOT NULL REFERENCES rest_table(table_id) ON DELETE CASCADE,
    start_time      TIMESTAMPTZ NOT NULL,
    end_time        TIMESTAMPTZ NOT NULL,
    period          TSRANGE GENERATED ALWAYS AS (tsrange(start_time, end_time, '[)')) STORED,
    party_size      INT NOT NULL CHECK (party_size > 0),
    status          TEXT NOT NULL DEFAULT 'OPEN',
    created_at      TIMESTAMPTZ DEFAULT now(),
    updated_at      TIMESTAMPTZ DEFAULT now(),
    CONSTRAINT reservation_no_overlap EXCLUDE USING GIST (
        table_id WITH =,
        period   WITH &&
    )
);

-- Indexes to support common queries
CREATE INDEX idx_rest_table_restaurant_id ON rest_table (restaurant_id);
CREATE INDEX idx_reservation_table_id ON reservation (table_id);
CREATE INDEX idx_reservation_customer_id ON reservation (customer_id);
CREATE INDEX idx_reservation_start_time ON reservation (start_time);

-- Trigger to update updated_at
CREATE OR REPLACE FUNCTION trigger_set_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_reservation_updated_at
BEFORE UPDATE ON reservation
FOR EACH ROW
EXECUTE FUNCTION trigger_set_timestamp();

Notes:

The exclusion constraint ensures no two reservations for the same table have overlapping period ranges.
The btree_gist extension is required in some Postgres versions to support = on integers inside a GIST index.

5. Design Rationale & Indexing

Normalization & Data Integrity:

The schema is normalized to 3NF. Each entity is modeled independently and referenced via foreign keys. This avoids duplication of restaurant or customer data in reservations.
Foreign key constraints enforce referential integrity; ON DELETE CASCADE keeps dependent rows tidy when a parent is removed (adjust to SET NULL if you need historical preservation).

Preventing double-booking:

The EXCLUDE USING GIST constraint on (table_id, period) guarantees database-level prevention of overlapping reservations for the same table. This is safer than application-only checks which are vulnerable to race conditions.

Indexing Strategy:

Index foreign keys (restaurant_id, table_id, customer_id) and start_time for common JOIN and time-based queries.
The GIST index backing the exclusion constraint is used for overlap checks; avoid redundant heavy indexes on period.

Scaling & Operational Notes:

For very high write concurrency, consider an availability cache or a reservation-service layer that assigns tables and retries on conflicts (HTTP 409). Use idempotency tokens for client retries.

6. API Endpoint Design (RESTful)

Restaurants

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


POST /restaurants
Request:
{
  "name": "Pizzeria Roma",
  "address": "100 Main St, City",
  "timezone": "Asia/Kolkata"
}
Response (201 Created):
{
  "restaurant_id": 42
}

1
2
3
4
5
6
7


GET /restaurants/{restaurant_id}
Response (200):
{
  "restaurant_id": 42,
  "name": "Pizzeria Roma",
  "address": "100 Main St"
}

1
2
3
4
5
6


GET /restaurants?name=...&min_capacity=...
Response (200):
[
  { "restaurant_id": 42, "name": "Pizzeria Roma" },
  { "restaurant_id": 43, "name": "Trattoria" }
]

Tables

1
2
3
4
5


POST /restaurants/{restaurant_id}/tables
Request:
{ "name": "T1", "capacity": 4, "section": "Patio" }
Response (201):
{ "table_id": 101 }

1
2
3


GET /tables/{table_id}
Response (200):
{ "table_id": 101, "restaurant_id": 42, "capacity": 4, "section": "Patio" }

1
2
3
4
5
6


GET /restaurants/{restaurant_id}/tables?capacity=...
Response (200):
[
  { "table_id": 101, "capacity": 4 },
  { "table_id": 102, "capacity": 6 }
]

Customers

POST /customers
Request:
{ "name": "Alice", "email": "[email protected]", "phone": "+1-555-0100" }
Response (201):
{ "customer_id": 900 }

1
2
3

GET /customers/{customer_id}
Response (200):
{ "customer_id": 900, "name": "Alice", "email": "[email protected]" }

Reservations

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


POST /reservations
Request (auto-assign example):
{
  "customer_id": 900,
  "restaurant_id": 42,
  "start_time": "2025-10-20T19:00:00+05:30",
  "end_time": "2025-10-20T21:00:00+05:30",
  "party_size": 4
}
Response (201 Created):
{ "reservation_id": 1234, "table_id": 101, "status": "CONFIRMED" }
Errors: 409 Conflict when DB exclusion constraint prevents booking (client should retry or choose another slot).

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


GET /reservations/{reservation_id}
Response (200):
{
  "reservation_id": 1234,
  "customer_id": 900,
  "table_id": 101,
  "start_time": "2025-10-20T19:00:00+05:30",
  "end_time": "2025-10-20T21:00:00+05:30",
  "party_size": 4,
  "status": "CONFIRMED"
}

1
2
3
4
5


PUT /reservations/{reservation_id}
Request:
{ "start_time": "2025-10-20T20:00:00+05:30", "end_time": "2025-10-20T22:00:00+05:30" }
Response (200):
{ "reservation_id": 1234, "status": "CONFIRMED" }

1
2


DELETE /reservations/{reservation_id}
Response (204 No Content)

Availability

1
2
3
4
5
6


GET /availability?restaurant_id={rid}&start_time=...&end_time=...&party_size=...
Response (200):
[
  { "table_id": 101, "capacity": 4 },
  { "table_id": 102, "capacity": 6 }
]

1
2


# Behavioral note (transactional behavior)
POST /reservations must be implemented as a transactional operation. When auto-assigning a table, the server should find a candidate table (using the availability query) and attempt to insert the reservation; if the DB exclusion constraint fails due to a race, return 409 Conflict and allow the client to retry.

7. Core Logic Queries

Below are the key SQL patterns for the most important business logic. They are written for PostgreSQL and assume the schema defined above.

Find an available table for a restaurant, time range and party size (uses NOT EXISTS with tsrange):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


SELECT t.table_id
FROM rest_table t
WHERE t.restaurant_id = :restaurant_id
    AND t.capacity >= :party_size
    AND NOT EXISTS (
        SELECT 1
        FROM reservation r
        WHERE r.table_id = t.table_id
            AND r.status <> 'CANCELLED'
            AND r.period && tsrange(:start_time, :end_time, '[)')
    )
ORDER BY t.capacity ASC
LIMIT 1;

Transactional create with auto-assign (PL/pgSQL): find a candidate and insert; handle exclusion conflict (SQLSTATE 23P01) gracefully so callers can return 409 and retry.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42


CREATE OR REPLACE FUNCTION create_reservation_auto(
        p_customer_id INT,
        p_restaurant_id INT,
        p_start_time TIMESTAMPTZ,
        p_end_time TIMESTAMPTZ,
        p_party_size INT
) RETURNS INT AS $$
DECLARE
        v_table_id INT;
        v_reservation_id INT;
BEGIN
        SELECT t.table_id
        INTO v_table_id
        FROM rest_table t
        WHERE t.restaurant_id = p_restaurant_id
            AND t.capacity >= p_party_size
            AND NOT EXISTS (
                SELECT 1 FROM reservation r
                WHERE r.table_id = t.table_id
                    AND r.status <> 'CANCELLED'
                    AND r.period && tsrange(p_start_time, p_end_time, '[)')
            )
        ORDER BY t.capacity ASC
        LIMIT 1
        FOR UPDATE SKIP LOCKED;

        IF v_table_id IS NULL THEN
                RAISE EXCEPTION 'no_table_available';
        END IF;

        BEGIN
                INSERT INTO reservation (customer_id, table_id, start_time, end_time, party_size)
                VALUES (p_customer_id, v_table_id, p_start_time, p_end_time, p_party_size)
                RETURNING reservation_id INTO v_reservation_id;
                RETURN v_reservation_id;
        EXCEPTION WHEN SQLSTATE '23P01' THEN
                -- exclusion constraint violation: concurrent booking happened
                RAISE NOTICE 'reservation conflict, caller should retry or return 409';
                RETURN NULL;
        END;
END;
$$ LANGUAGE plpgsql;

Simpler approach: attempt insert and rely on exclusion constraint; catch 23P01 in application and return HTTP 409.

1
2
3


INSERT INTO reservation (customer_id, table_id, start_time, end_time, party_size)
VALUES (:customer_id, :table_id, :start_time, :end_time, :party_size);
-- On exclusion violation (SQLSTATE 23P01) return 409 Conflict to client

Cancel a reservation (soft-cancel by status):

1
2
3
4


UPDATE reservation
SET status = 'CANCELLED', updated_at = now()
WHERE reservation_id = :reservation_id
    AND status <> 'CANCELLED';

Get a customer’s upcoming reservations:

1
2
3
4
5


SELECT *
FROM reservation
WHERE customer_id = :customer_id
    AND end_time > now()
ORDER BY start_time;

Query to list all reservations for a table in a time window (useful for staff views):

1
2
3
4
5


SELECT *
FROM reservation
WHERE table_id = :table_id
    AND period && tsrange(:start_time, :end_time, '[)')
ORDER BY start_time;

Notes on behavior & concurrency:

POST /reservations should run in a DB transaction. If auto-assigning a table, the server should attempt an insert; if the exclusion constraint triggers, return 409 Conflict and allow the client to retry or choose another slot.
POST /reservations should run in a DB transaction. If auto-assigning a table, the server should attempt an insert; if the exclusion constraint triggers, return 409 Conflict and allow the client to retry or choose another slot.

Problem#

Solution#

1. Requirements Analysis#

2. Conceptual Design (Entities & Relationships)#

3. Logical Design (ER Diagram)#

Table descriptions#

restaurant#

restaurant table#

customer#

reservation (detailed)#

Rationale and behaviors#

4. Physical Design (SQL Schema)#

5. Design Rationale & Indexing#

6. API Endpoint Design (RESTful)#

7. Core Logic Queries#

Notes on behavior & concurrency:#