CSE Roadmap

Core tier · about 3 hours · 7 parts

Part 3 of 7 · 1 min read

First design

A straightforward relational schema and the obvious reservation flow.

Think of the first design like a sign-up sheet pinned to a board. Anyone can walk up, check a row, and write their name in an empty slot, one person at a time.

A straightforward relational schema covers the requirement directly: a seats table holding each seat's status, and a reservations table recording who holds or bought which seat.

CREATE TABLE seats (
    seat_id      BIGINT PRIMARY KEY,
    event_id     INT NOT NULL,
    seat_label   TEXT NOT NULL,
    status       TEXT NOT NULL DEFAULT 'available',  -- available, held, sold
    held_until   TIMESTAMP
);

CREATE TABLE reservations (
    reservation_id BIGINT PRIMARY KEY,
    seat_id        BIGINT REFERENCES seats(seat_id),
    customer_id    BIGINT NOT NULL,
    status         TEXT NOT NULL,  -- held, paid, released
    created_at     TIMESTAMP NOT NULL DEFAULT now()
);

The obvious flow: a customer selects a seat, the application checks status = 'available', and if so, updates it to held with a held_until timestamp. On payment, status becomes sold; if the hold expires unpaid, a background job sets it back to available.

Seat hold

A seat hold is a temporary reservation that marks a seat unavailable to other customers for a short window while one customer completes payment, automatically releasing it if payment does not complete in time.

Example: the held_until timestamp above is what a background job checks to release an expired hold back to available.