🎟️Designing Event Booking / Seat Reservation System Part 1- Database Modelling

Understanding the Problem

Core Requirements

• One seat can be booked by only one user

• Thousands of users may attempt booking simultaneously

• Seat must be temporarily held during payment

• Holds must expire automatically

• Booking must be atomic and consistent

Solutions

1️⃣ Naive Design (Why It Fails)

Naive Table

CREATE TABLE Seat (
    seat_id BIGINT PRIMARY KEY,
    event_id BIGINT,
    status VARCHAR(20) -- AVAILABLE / BOOKED
);

Booking Logic (❌ Wrong)

SELECT status FROM Seat WHERE seat_id = 101;
-- if AVAILABLE
UPDATE Seat SET status = 'BOOKED' WHERE seat_id = 101;

Problems

❌ Race conditions
❌ Double booking
❌ No payment timeout
❌ No concurrency control

2️⃣ Correct Mental Model

Booking is not a single step.

It is a state machine:

AVAILABLE → HELD → BOOKED

With timeouts and ownership.

3️⃣ Optimized Core Data Model

🎯 Seat Table (Static)

CREATE TABLE Seat (
    seat_id BIGINT PRIMARY KEY,
    event_id BIGINT,
    seat_number VARCHAR(10)
);

🎯 Seat Reservation (Dynamic State)

CREATE TABLE SeatReservation (
    seat_id BIGINT,
    user_id BIGINT,
    status VARCHAR(20), -- HELD / BOOKED / EXPIRED
    hold_expires_at DATETIME,
    PRIMARY KEY (seat_id),
    INDEX (user_id)
);

Why Separate Table?

• Seat = metadata

• Reservation = state

• Easier expiration & cleanup

4️⃣ Seat Hold (Critical Concurrency Control)

Atomic Seat Hold

INSERT INTO SeatReservation (seat_id, user_id, status, hold_expires_at)
VALUES (?, ?, 'HELD', NOW() + INTERVAL 5 MINUTE);

✔ Primary key on seat_id
✔ Only one insert succeeds
✔ Others fail → seat already held

The database becomes the arbiter of truth.

5️⃣ Booking Confirmation (After Payment)

UPDATE SeatReservation
SET status = 'BOOKED'
WHERE seat_id = ?
  AND user_id = ?
  AND status = 'HELD'
  AND hold_expires_at > NOW();

Guarantees

✔ Seat still held
✔ Hold not expired
✔ Correct user

6️⃣ Hold Expiry (Auto Cleanup)

Option A — Background Job (Most Common)

UPDATE SeatReservation
SET status = 'EXPIRED'
WHERE status = 'HELD'
  AND hold_expires_at < NOW();

Option B — Redis TTL (High Scale)

SET seat:101:user:7 HELD EX 300

7️⃣ Preventing Overselling at Scale

🔒 Strategy A — DB Uniqueness (Baseline)

• PK on seat_id

• Simple

• Reliable

⚡ Strategy B — Redis Lock (High Throughput)

SETNX lock:seat:101 userId EX 5

• Fast

• Distributed

• Must handle crash recovery

8️⃣ Read Optimization (Seat Map)

Seat Availability Query

SELECT s.seat_id,
       CASE
         WHEN r.status = 'BOOKED' THEN 'BOOKED'
         WHEN r.status = 'HELD' AND r.hold_expires_at > NOW() THEN 'HELD'
         ELSE 'AVAILABLE'
       END AS status
FROM Seat s
LEFT JOIN SeatReservation r ON s.seat_id = r.seat_id
WHERE s.event_id = ?;

Cache This (Redis)

event:42:seatmap

9️⃣ High-Level Architecture

Failure Scenarios

Scenario	Handling
Payment fails	Let hold expire
User abandons	TTL cleanup
App crash	DB still correct
Redis down	DB uniqueness saves
Retry request	Idempotent update