Design this schema: flight seat booking

Part of the free LogicWiz SQL & Databases course, Interview pack: SQL interview problems. The queries and practice run in the app on real Postgres.

Two people click on seat 12C at the same second. One must get it and the other must see "seat taken". Your app checked that the seat was free for both of them. Which part of your design stops the double booking?

You'll learn to

• Model flights, seats as a weak entity, passengers and bookings
• Stop double booking with a partial unique index, not an app check
• Handle holds that expire and cancelled seats that can be sold again
• Write seat-map, load-factor and clean-up queries on your schema

Learn

Step 1: the requirements

• Flights run between two airports at a set time; the same flight number flies again on other days.
• Each flight has a seat map: seat 12A on one flight is not seat 12A on another.
• A passenger picks a seat. It is held for 15 minutes while they pay, then confirmed.
• A cancelled booking frees the seat so it can be sold again, but the history stays.
• The hard rule: never two live bookings for one seat, even when two people click at the same moment.
• Hot queries: the seat map of one flight, seats left, load factor, a passenger's trips.

Step 2: the seat booking ER diagram

An ER diagram of flight seat booking: airports are the origin and destination of many flights, each flight has seats as a weak entity identified by flight and seat number, a seat has many bookings over time, and a passenger makes many bookings.

A seat is a weak entity: "12A" only means something together with its flight, so its key is (flight_id, seat_no). A seat has many bookings over time (cancelled, then sold again), which is why the rule "only one live booking" needs more than a foreign key. Flights point to airports twice; in the SQL the two columns are origin and destination.

Step 3: the tables

CREATE TABLE airports (
  code  CHAR(3) PRIMARY KEY,
  city  VARCHAR(40) NOT NULL
);
CREATE TABLE flights (
  flight_id    INT PRIMARY KEY,
  flight_no    VARCHAR(6) NOT NULL,
  origin       CHAR(3) NOT NULL REFERENCES airports(code),
  destination  CHAR(3) NOT NULL REFERENCES airports(code),
  departs_at   TIMESTAMP NOT NULL,
  UNIQUE (flight_no, departs_at),
  CHECK (origin <> destination)
);
CREATE TABLE seats (
  flight_id  INT REFERENCES flights(flight_id) ON DELETE CASCADE,
  seat_no    VARCHAR(4),
  cabin      VARCHAR(3) NOT NULL CHECK (cabin IN ('ECO', 'BUS')),
  price      NUMERIC(8,2) NOT NULL CHECK (price > 0),
  PRIMARY KEY (flight_id, seat_no)
);
CREATE TABLE passengers (
  passenger_id  INT PRIMARY KEY,
  name          VARCHAR(100) NOT NULL,
  email         VARCHAR(255) NOT NULL UNIQUE
);
CREATE TABLE bookings (
  booking_id    INT PRIMARY KEY,
  passenger_id  INT NOT NULL REFERENCES passengers(passenger_id),
  flight_id     INT NOT NULL,
  seat_no       VARCHAR(4) NOT NULL,
  status        VARCHAR(10) NOT NULL CHECK (status IN ('held', 'confirmed', 'cancelled')),
  booked_at     TIMESTAMP NOT NULL,
  FOREIGN KEY (flight_id, seat_no) REFERENCES seats(flight_id, seat_no)
);
CREATE UNIQUE INDEX one_live_booking_per_seat
  ON bookings (flight_id, seat_no)
  WHERE status <> 'cancelled';

SELECT table_name, COUNT(*) AS columns
FROM information_schema.columns
WHERE table_schema = 'public'
GROUP BY table_name
ORDER BY table_name;

UNIQUE (flight_no, departs_at) lets SK101 fly every day but only once at a given time. bookings points to a seat with a composite foreign key, so you cannot book a seat that does not exist on that flight. The last statement is the guard against double booking.

The rule that matters: no double booking

The tempting design is in the app: "SELECT the seat; if it is free, INSERT the booking". Two requests can both run the SELECT before either INSERT, both see "free", and both insert. That is a race, and no amount of app code fixes it cleanly.

Put the rule in the database. A plain UNIQUE (flight_id, seat_no) on bookings is too strong: a cancelled booking would block the seat forever. So use a partial unique index:

CREATE UNIQUE INDEX … ON bookings (flight_id, seat_no) WHERE status <> 'cancelled';

At most one live booking per seat, and any number of cancelled ones kept as history.

Two passengers race for seat 1B

The database says no

-- 12A on flight 1 is already confirmed for Alex
INSERT INTO bookings VALUES (11, 2, 1, '12A', 'held', '2025-10-17 09:00');

The insert fails with a unique violation. The app catches that error and offers another seat. Seat 1A shows the other half: booking 3 was cancelled and booking 4 resold the same seat, which the partial index allows.

Holds, payment and expiry

A booking starts as held with booked_at. Payment success turns it into confirmed. A hold that is too old must free the seat, so a scheduled job runs an UPDATE that cancels held rows older than 15 minutes. Until that runs, the seat map can also treat old holds as free.

Another approach locks a seat row with SELECT … FOR UPDATE and flips a status column on seats. It works, but it keeps less history and every booking path must remember to lock. The unique index is enforced for every writer, including the one you forget about.

Steps 5 and 6: an index and the seats left

-- a passenger's trips
CREATE INDEX idx_bookings_passenger ON bookings (passenger_id, booked_at);

-- seats left per flight
SELECT f.flight_no, f.departs_at,
       COUNT(*) FILTER (WHERE NOT EXISTS (
         SELECT 1 FROM bookings AS b
         WHERE b.flight_id = s.flight_id AND b.seat_no = s.seat_no AND b.status <> 'cancelled'
       )) AS seats_left
FROM flights AS f
JOIN seats AS s ON s.flight_id = f.flight_id
GROUP BY f.flight_id, f.flight_no, f.departs_at
ORDER BY f.departs_at;

The unique index on (flight_id, seat_no) also serves this lookup, so "is this seat taken?" is one index probe. Flight 1 has one seat left (1B).

MySQL has no partial indexes. A common workaround is a generated column, live_seat VARCHAR(4) AS (IF(status <> 'cancelled', seat_no, NULL)), with UNIQUE (flight_id, live_seat). A UNIQUE index allows many NULLs, so cancelled rows never clash.

This question is really about concurrency. Say early: "two users can pass an app-side check at the same time, so the database must enforce one live booking per seat". Then name the tool (a partial unique index, or row locks) and how the app reacts to the error.

In one line

Seat booking: seats are a weak entity keyed (flight_id, seat_no); one live booking per seat is enforced by a partial unique index; holds expire with an UPDATE; never trust an app-side check-then-insert.

Interview corner

How do you prevent two people booking the same seat?

Enforce it in the database. A partial unique index ON bookings (flight_id, seat_no) WHERE status <> 'cancelled' makes the second concurrent insert wait and then fail, whatever the app does. The app catches the unique violation and offers another seat. Row locks with SELECT … FOR UPDATE are an alternative.

How do you handle a seat held during payment?

Insert the booking as held with a timestamp. Payment success updates it to confirmed. A scheduled job cancels holds older than the time limit, which frees the seat because the unique index ignores cancelled rows.

Why is seats a weak entity?

A seat number like 12A only identifies a seat within one flight. Its key is the owner's key plus its own partial key, (flight_id, seat_no), and deleting the flight deletes its seats (ON DELETE CASCADE).