Skip to content
notes.vinhnq.com

Search

Database & ORMSenior22 min read

Designing an airline booking database: relationships, seat inventory and overbooking

Review 15 cards
On this page

Airline booking database series: 1. Flights, seats and overbooking · 2. Holding seats under concurrency · 3. Bookings, ledger and history

Selling airline tickets packs most of the hard parts of data modelling into one small domain: reference data, a scarce resource people fight over (seats), an odd business rule (selling more seats than exist), money and audit. This post follows the course from v1 to v2, explains why each decision was made, fixes a few mistakes in the original notes, and runs the SQL on PostgreSQL 16 (the MySQL 8.4 differences were run too). Part 2 covers holding seats when many people book at once, part 3 covers bookings, tickets, double-entry money and history; the general process is in The data modeling process.

Read first: normalization, indexes and EXPLAIN, transactions and locking.

1. The problem and some numbers

Key idea: total write load is modest, but at peak it piles onto the same few rows of one flight. The seat inventory design has to account for that.

The system must: search flights, show the seat map, book one-way or round trip for several passengers, pick seats (or auto-assign), pay, issue tickets, cancel and refund. Assumed numbers for a mid-sized domestic airline:

Quantity Assumed value Design consequence
Flights per day ~400 146,000 flights a year; reference data is small
Seats per flight ~180 (A321) Per-flight seat table: ~26 million rows a year
Look-to-book ratio 100:1 or more Search is the hot read path and should be cached; booking is the write path that must be correct
Peak booking a few hundred people targeting one Tết holiday flight Contention concentrates on one flight, not the whole system

2. Write relationships as sentences before drawing

Key idea: write each relationship as subject + verb + object + cardinality (cardinality = “one” or “many”), and read it in both directions. Only draw the line when both sentences are true.

  • One relationship rests on one column. A flight has two relationships with airports (departure, arrival), so it has two foreign keys, two sentences and two lines on the diagram. Collapsing them into “a flight relates to airports” loses information.
  • Reading the reverse direction is where mistakes show up. v1 said “a flight has many bookings, a booking belongs to one flight”; the reverse sentence breaks as soon as there’s a round trip, because a round-trip booking belongs to two flights.
Two-way relationship sentences for the whole model
Forward sentence Reverse sentence Conclusion
An airport has many departing flights A flight has exactly one departure airport 1–N via departure_airport
An airport has many arriving flights A flight has exactly one arrival airport A second 1–N via arrival_airport
An aircraft model is used by many flights A flight uses one aircraft model 1–N
An aircraft model has many seats A seat belongs to one aircraft model 1–N, key (aircraft_code, seat_code)
A booking has many passengers A passenger can appear in many bookings N–N via booking_passenger
A booking has one or two directions A direction belongs to one booking 1–N via booking_direction
A direction has many tickets (one per passenger) A ticket belongs to one direction of one booking 1–N
A booking has many money transactions (pay, refund) A transaction belongs to one booking 1–N

3. Conceptual ER model

Key idea: the top half of the diagram (airports, aircraft, seats, flights, fares, seat inventory) is this post; the bottom half (booking, directions, money lines, tickets, ledger) is part 3.

erDiagram
    AIRPORT ||--o{ FLIGHT : "departs from"
    AIRPORT ||--o{ FLIGHT : "arrives at"
    AIRCRAFT ||--o{ FLIGHT : "operates"
    AIRCRAFT ||--|{ SEAT : "has seat map"
    FLIGHT ||--|{ SEAT_AVAILABILITY : "state of each seat"
    FLIGHT ||--|{ FARE : "price per class"
    BOOKING ||--|{ BOOKING_DIRECTION : "away/return"
    FLIGHT ||--o{ BOOKING_DIRECTION : "is booked on"
    BOOKING_DIRECTION ||--|{ BOOKING_LINE : "money lines"
    BOOKING_DIRECTION ||--o{ TICKET : "issues"
    PASSENGER ||--o{ TICKET : "travels on"
    BOOKING ||--o{ BOOKING_PASSENGER : ""
    PASSENGER ||--o{ BOOKING_PASSENGER : ""
    BOOKING ||--o{ LEDGER_TRANSACTION : "payments, refunds"
    LEDGER_TRANSACTION ||--|{ ENTRY : "entries"

4. From v1 to v2: what changed and why

Key idea: v1 points the right way but still “thinks in screens” (repeated columns, a column holding a list, overwrites). v2 splits those into their own tables, snapshots the price at purchase and adds history.

Where v1 v2 Why
Flights in a booking departure_flight_id, destination_flight_id columns + a one-way/round-trip type A booking_direction table, one row per direction Removes a repeating group; each direction has its own money, tickets and refunds
Passengers A passengers column on booking A booking_passenger table One column can’t hold a list (violates 1NF)
Price pricing(flight_id, seat_class, price, date) Price lives with the seat or fare class, and is snapshotted into money lines at booking time Prices change over time; a booking keeps the price it was sold at
Time columns departure_date, destination_date departure_time, arrival_time “destination” is a place, not a time; you need a time, not just a date
Money transaction(amount, method, fee) transaction with debit/credit accounts + an entry table Double-entry bookkeeping, auditable
Changing a booking Overwrite A version column + a booking_history table Optimistic locking and history

5. Reference data: airports, aircraft, seats, flights

Key idea: reference data (rarely changes, used for lookups) is small and caches well. The decisions worth noting are the keys: natural for airports, surrogate for flights, plus CHECKs for cheap invariants.

CREATE TABLE airport (
  airport_code char(3) PRIMARY KEY,          -- IATA code: HAN, SGN, DAD
  name         text NOT NULL,
  time_zone    text NOT NULL                 -- IANA, used to show local times
);

CREATE TABLE aircraft (
  aircraft_code text PRIMARY KEY,            -- model / cabin configuration: 'A321'
  name          text NOT NULL,
  status        text NOT NULL DEFAULT 'ACTIVE' CHECK (status IN ('ACTIVE', 'RETIRED'))
);

CREATE TABLE seat (                          -- seat map per aircraft model
  aircraft_code text NOT NULL REFERENCES aircraft,
  seat_code     text NOT NULL,               -- '12A'
  fare_class    text NOT NULL CHECK (fare_class IN ('ECONOMY', 'BUSINESS')),
  position      text NOT NULL CHECK (position IN ('WINDOW', 'MIDDLE', 'AISLE')),
  PRIMARY KEY (aircraft_code, seat_code)
);

CREATE TABLE flight (
  flight_id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  flight_no         text        NOT NULL,    -- 'VN213'
  aircraft_code     text        NOT NULL REFERENCES aircraft,
  departure_airport char(3)     NOT NULL REFERENCES airport,
  arrival_airport   char(3)     NOT NULL REFERENCES airport,
  departure_time    timestamptz NOT NULL,
  arrival_time      timestamptz NOT NULL,
  status            text        NOT NULL DEFAULT 'SCHEDULED'
                    CHECK (status IN ('SCHEDULED', 'DELAYED', 'DEPARTED', 'ARRIVED', 'CANCELLED')),
  CHECK (departure_airport <> arrival_airport),
  CHECK (arrival_time > departure_time),
  UNIQUE (flight_no, departure_time)         -- same flight number every day, different departure time
);

CREATE TABLE fare (                          -- current price per class
  flight_id    bigint  NOT NULL REFERENCES flight,
  fare_class   text    NOT NULL,
  price_amount bigint  NOT NULL CHECK (price_amount > 0),   -- VND, smallest unit
  currency     char(3) NOT NULL DEFAULT 'VND',
  PRIMARY KEY (flight_id, fare_class)
);
  • Natural key for airports, surrogate key for flights. IATA codes are short, stable and universally understood, so they make a fine primary key. A flight has no tidy natural key (the flight number repeats daily), so it gets an identity flight_id plus UNIQUE (flight_no, departure_time) to block duplicate entries.
  • CHECKs for cheap invariants. Departure airport differs from arrival airport, landing comes after take-off: two CHECK lines block a whole class of data-entry bugs at almost no runtime cost.
  • Status is text with a CHECK. Easy to read when debugging and writing reports. More in Enums in the database and the API.

6. Seat inventory: one row per seat or a counter

Key idea: this is the central decision, and both v1 and v2 leave it open. If customers pick seats at booking time and you never overbook, use one row per seat; if seats aren’t picked at booking time, a counter is much simpler.

Option A, one row per seat per flight:

CREATE TABLE seat_availability (
  flight_id   bigint      NOT NULL REFERENCES flight,
  seat_code   text        NOT NULL,
  fare_class  text        NOT NULL,
  status      text        NOT NULL DEFAULT 'AVAILABLE'
              CHECK (status IN ('AVAILABLE', 'HELD', 'BOOKED', 'BLOCKED')),
  booking_id  bigint,                         -- foreign key to booking, added in part 3
  held_until  timestamptz,                    -- hold expiry while waiting for payment
  PRIMARY KEY (flight_id, seat_code),
  CHECK ((status IN ('HELD', 'BOOKED')) = (booking_id IS NOT NULL)),
  CHECK ((status = 'HELD') = (held_until IS NOT NULL))
);
-- generate rows when a flight opens for sale: one row per seat in the model's seat map
INSERT INTO seat_availability (flight_id, seat_code, fare_class)
SELECT f.flight_id, s.seat_code, s.fare_class
FROM flight f JOIN seat s USING (aircraft_code)
WHERE f.flight_id = :newFlightId;

The last two CHECKs encode the status invariants: a held or booked seat must know its booking, and a held seat must have an expiry. Without them, a bug that forgets to set booking_id creates seats “sold to nobody”.

Option B, one counter per flight per class:

CREATE TABLE flight_inventory (
  flight_id     bigint       NOT NULL REFERENCES flight,
  fare_class    text         NOT NULL,
  capacity      int          NOT NULL CHECK (capacity > 0),
  overbook_rate numeric(4,3) NOT NULL DEFAULT 0 CHECK (overbook_rate BETWEEN 0 AND 0.2),
  sell_limit    int GENERATED ALWAYS AS (floor(capacity * (1 + overbook_rate))::int) STORED,
  booked        int          NOT NULL DEFAULT 0 CHECK (booked >= 0),
  PRIMARY KEY (flight_id, fare_class),
  CHECK (booked <= sell_limit)
);

The throughput ceiling of that hot row and how to hold inventory safely in both options are the subject of part 2.

7. Overbooking: the correct formula

Key idea: airlines sell more seats than they have because some passengers never show up (no-shows). The sell limit is floor(capacity × (1 + rate)), and you must add the new booking before comparing.

The original v1 note: 100-seat aircraft, overbook by 10%, accept a booking if booked_seats < total_seats * overbook_rate. That formula is wrong under either reading:

  • overbook_rate = 0.1: the condition becomes booked < 10, so you sell only 10 seats on a 100-seat flight.
  • overbook_rate = 1.1: the condition becomes booked < 110, but it checks the count before adding the new booking. With booked = 109 and a group of 3, it holds and you sell 112, past the 110 limit.
sell_limit = floor(capacity × (1 + overbook_rate))
accept a booking of q seats  ⇔  booked + q ≤ sell_limit

Put it straight into the UPDATE so checking and writing are one atomic operation (nothing can slip in between):

UPDATE flight_inventory
SET    booked = booked + :q
WHERE  flight_id = :flightId AND fare_class = :fareClass
  AND  booked + :q <= sell_limit
RETURNING booked;
-- 0 rows: nothing left to sell. 1 row: q seats are held.

sell_limit is a generated column (the database computes it from other columns), so nobody forgets to apply the formula elsewhere in the code; CHECK (booked <= sell_limit) is the last safety net if some UPDATE is written wrong.

Overbooking follow-ups: the rate, the model, lowering the rate

Follow-ups a senior interviewer tends to ask:

  • The rate is not a constant. It’s estimated from no-show statistics by route, time of day and season, and is usually zero for business class. Store it per flight and class (like flight_inventory.overbook_rate) so revenue management can tune it.
  • Overbooking only makes sense with a counter. With one row per seat, the primary key (flight_id, seat_code) forbids selling a seat twice; overbooked passengers have no seat until check-in.
  • Lowering the rate after sales can push sell_limit below booked, and UPDATE ... SET overbook_rate = ... is rejected by the CHECK. That’s the right behaviour (no silent nonsense state), but it needs a procedure: only allow lowering down to booked, or close the flight for sale.

8. Indexes for the main queries

Key idea: design indexes from the queries, not the tables. Equality columns go first and the range column last; partial indexes (indexes holding only rows that match a condition) cover free seats and held seats.

Query Index Note
Search flights by route and date flight (departure_airport, arrival_airport, departure_time) Equality columns first, the range column last
Seat map of a flight Primary key (flight_id, seat_code) Already there
Count free seats per class Partial (flight_id, fare_class, seat_code) WHERE status = 'AVAILABLE' Small index, allows an Index Only Scan
Auto-assign seats (SKIP LOCKED, part 2) The same partial index Already ordered by seat_code, so no Sort
Release expired holds Partial (held_until) WHERE status = 'HELD' Contains only held seats, tiny
Price per flight and class Primary key of fare Already there
Plans on 6,480 flights and 1.17 million seat rows, and partitioning
-- Search HAN → SGN flights for one day
Nested Loop (actual rows=12)
  -> Bitmap Heap Scan on flight f (actual rows=12)
       -> Bitmap Index Scan on flight_route_time_idx
            Index Cond: departure_airport = 'HAN' AND arrival_airport = 'SGN'
                        AND departure_time >= ... AND departure_time < ...
  -> Index Scan using fare_pkey on fare fa (actual rows=1 loops=12)

-- Count free seats on one flight
GroupAggregate (actual rows=2)
  -> Index Only Scan using seat_avail_free_idx on seat_availability (actual rows=180)
       Index Cond: flight_id = 100
       Heap Fetches: 0

The flight search still has a Sort on departure_time because the planner picked a bitmap scan (a bitmap doesn’t preserve index order); for 12 rows it’s not worth worrying about. How to read each line of a plan is in the hands-on EXPLAIN ANALYZE post.

Long-term size: the per-flight seat table grows by ~26 million rows a year and is almost read-only once the flight has departed. You can partition it by departure month (add a departure_date column as the partition key) and detach or archive old partitions; see joins and partitioning and deleting old data safely.

Exercises

Exercise 1: Relationship sentences for a connecting itinerary

Task: a passenger flies Hanoi → Da Nang → Phu Quoc on two flights under one itinerary. Write the relationship sentences (both directions) and say which table in the design must change.

Solution to Exercise 1: add a segment level
  • “A direction consists of one or more segments”; “a segment belongs to exactly one direction”. 1–N.
  • “A segment uses exactly one flight”; “a flight is a segment of many bookings”. N–1.
  • “A ticket has many coupons, one per segment”; “a coupon belongs to one ticket”.

Design change: booking_direction.flight_id is no longer enough; add booking_segment (bd_id, seq_no, flight_id) and move seat holds and coupon issuing to the segment level. That’s also why a 0/1 direction column doesn’t stretch to multi-city itineraries (discussed in part 3).

Exercise 2: Design indexes for the booking schema

Task (the index lesson’s homework): given five queries, (a) search flights by route and date; (b) free seats on a flight per class; (c) a user’s bookings, newest first; (d) the passenger list of a flight; (e) pending-payment bookings that have expired, propose indexes and explain.

Solution to Exercise 2: indexes for the five queries
Query Index
(a) flight (departure_airport, arrival_airport, departure_time); equality first, range last
(b) Partial seat_availability (flight_id, fare_class, seat_code) WHERE status = 'AVAILABLE'
(c) booking (user_id, created_at DESC); the first page needs no Sort
(d) ticket (flight_id), or reuse the unique (flight_id, seat_code) index if every ticket has a seat
(e) Partial booking (expires_at) WHERE status = 'PENDING_PAYMENT'

Also: index every foreign key that queries go through (PostgreSQL doesn’t do it for you), and verify with EXPLAIN (ANALYZE, BUFFERS) on realistically sized data, not a 10-row table. Parts (c), (d) and (e) use the tables in part 3.

Exercise 3: Groups and overbooking

Task: a flight has 168 economy seats, a 5% overbooking rate, and 175 sold. A group of 3 books. What happens with the formula from the original notes and with the correct one? Write the SQL.

Solution to Exercise 3: 175 + 3 against 176

sell_limit = floor(168 × 1.05) = 176. The original formula read as a 1.05 multiplier checks 175 < 176.4, which is true, and sells to 178, two over. The correct formula checks 175 + 3 ≤ 176, which is false: rejected.

The SQL is the UPDATE from section 7 with :q = 3, returning 0 rows. Selling part of the group is a product decision (usually not: groups want to travel together).

Remember

  • Write two-way relationship sentences and spot when a junction table is needed.
  • Explain why flight times must be stored as absolute instants, not local time.
  • Know when to pick one row per seat versus a counter, and why real airlines combine both.
  • Write the atomic overbooking UPDATE and point out the bug in booked < total × rate.
  • Design indexes from the queries, remembering PostgreSQL doesn’t index foreign keys for you.

Interview questions

Junior

Why does a flight have two foreign keys to the airport table?

Because there are two different relationships, the departure airport and the arrival airport, and each rests on one column.

Each reads as its own sentence (“a flight has exactly one departure airport”, “a flight has exactly one arrival airport”). Add CHECK (departure_airport <> arrival_airport) to block nonsense data.

Why store a booking’s passengers in a junction table instead of a column?

Because booking to passenger is N–N, and a column holding a list violates 1NF.

One booking has many passengers, one passenger takes many bookings. A list column can’t carry a foreign key and makes “which flights has this passenger taken” hard to query. A booking_passenger (booking_id, passenger_id) table with a composite primary key solves all three.

What is overbooking and how do you compute the sell limit?

Selling more tickets than seats because you know a share of passengers won’t show up; the sell limit is floor(capacity × (1 + rate)).

A booking of q seats is accepted only when booked + q ≤ sell_limit. You must add the new booking’s q before comparing, not compare the count already sold.

Mid

Should seat inventory be stored per seat or as a counter?

It depends on whether customers pick seats at booking time; airlines often combine them: sell against a per-class counter, select seats and check in against the seat map.

  • Per seat: seat selection works, contention spreads over many rows, the primary key prevents selling a seat twice, but there are many rows and no overbooking.
  • Counter: simple, overbooking comes naturally, but every booking on the flight fights over one hot row.
Why store departure and arrival times as timestamptz instead of local time?

Because a flight connects two time zones, and local times without a zone give the wrong duration when subtracted.

They can also break a “lands after it departs” CHECK on westbound flights. Store absolute instants (timestamptz, or UTC in a MySQL DATETIME), keep the IANA zone on the airport table, and convert to local time for display.

How would you index the query that searches flights by route and date?

(departure_airport, arrival_airport, departure_time): the two equality columns first and the time range last.

Columns after a range column can no longer narrow the scan. Verify with EXPLAIN ANALYZE on real data; for a result of a few dozen rows, a Bitmap Index Scan followed by a small Sort is normal.

Senior

The original notes accept a booking if booked < total × rate. How do you review that?

It’s wrong under both readings; change it to booked + q ≤ floor(capacity × (1 + rate)) inside the UPDATE’s WHERE.

  • Rate 0.1 sells only 10% of the seats; rate 1.1 compares the count before adding the new booking, so groups push past the limit.
  • Move the limit into a generated column and add CHECK (booked <= sell_limit) as a safety net.
  • Then discuss: rate per flight and class, zero for business, and the procedure for lowering the rate below what’s already sold.
Airports use a natural key and flights a surrogate key. Why not pick one style everywhere?

Choose by the nature of the data, not by a blanket rule.

IATA codes are short, stable and meaningful to people and external systems, so they make a good primary key and save joins just to display a code. Flights have no tidy natural key (the number repeats daily, the departure time can change when delayed), so they get an identity key plus UNIQUE (flight_no, departure_time) to block duplicates. If a natural key can change (a schedule change moves the departure time), it shouldn’t be the primary key referenced from millions of rows.

Useful phrases for interviews

  • “Before drawing the ER diagram, I write every relationship as a sentence and read it in both directions.”
  • “A flight has two separate relationships with airports, so it gets two foreign keys.”
  • “The core decision is whether seat inventory is one row per seat or a counter per class, and it depends on whether customers pick seats at booking time.”
  • “With a counter, overbooking is just a sell limit above capacity; with per-seat rows it’s impossible by construction.”
  • “I’d put the overbooking rule in the UPDATE’s WHERE clause, so the check and the write are atomic.”
  • “The sell limit is a generated column with a CHECK constraint as a safety net.”
  • “I design indexes from the queries: equality columns first, the range column last.”
  • “On PostgreSQL I index foreign keys explicitly, because unlike InnoDB it doesn’t do it for me.”

Notes based on the Advanced Backend course (Ronin Engineer), rewritten in my own words.