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.