Low Level Design: Airline Reservation System

Airline reservation systems are among the most demanding transactional workloads: thousands of concurrent seat holds, strict inventory partitioning by fare class, a multi-step booking state machine, and hard deadlines like check-in cutoff. This low level design covers the core schemas and flows.

Flight Schema

CREATE TABLE flight (
  flight_id          BIGINT PRIMARY KEY AUTO_INCREMENT,
  flight_number      VARCHAR(10) NOT NULL,
  origin             CHAR(3) NOT NULL,
  destination        CHAR(3) NOT NULL,
  scheduled_departure DATETIME NOT NULL,
  scheduled_arrival   DATETIME NOT NULL,
  aircraft_type      VARCHAR(50) NOT NULL,
  status             ENUM('scheduled','boarding','departed','arrived','cancelled') NOT NULL DEFAULT 'scheduled',
  INDEX (origin, destination, scheduled_departure)
);

Seat Schema

CREATE TABLE seat (
  flight_id     BIGINT NOT NULL REFERENCES flight(flight_id),
  seat_number   VARCHAR(4) NOT NULL,
  class         ENUM('economy','business','first') NOT NULL,
  status        ENUM('available','held','booked') NOT NULL DEFAULT 'available',
  held_until    DATETIME,
  booking_id    BIGINT,
  price         DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (flight_id, seat_number),
  INDEX (flight_id, class, status)
);

held_until enables TTL-based expiry of abandoned holds without a background sweeper — the application checks it on any read of a held seat.

Booking State Machine

1. Search: passenger queries origin, destination, date, class, and number of seats.
2. Select: passenger chooses a flight and specific seat(s).
3. Hold: seats are atomically set to held with held_until = NOW() + interval 15 minute using a conditional UPDATE that only succeeds if current status is available. A booking record is created in pending state.
4. Payment: passenger submits payment details. Payment processor is called. On success, proceed to confirm.
5. Confirm: seat status updated to booked, booking status to confirmed, PNR generated and returned to passenger.
6. Expiry path: if payment is not completed before held_until, the hold lapses. On next availability check the seat reverts to available.

PNR Generation

A Passenger Name Record (PNR) is the unique locator for a booking. It is a 6-character alphanumeric code (uppercase letters and digits, excluding ambiguous characters O, 0, I, 1) generated at confirmation time. Generation: take a base-34 encoded value of a sequence ID, left-pad to 6 characters, and verify uniqueness in the booking table. Collision probability is negligible given the ~1.5 billion possible values, but a uniqueness check with retry handles it.

CREATE TABLE booking (
  booking_id  BIGINT PRIMARY KEY AUTO_INCREMENT,
  pnr         CHAR(6) UNIQUE,
  passenger_id BIGINT NOT NULL,
  flight_id   BIGINT NOT NULL REFERENCES flight(flight_id),
  seat_number VARCHAR(4),
  fare_class  CHAR(1) NOT NULL,
  total_price DECIMAL(10,2) NOT NULL,
  status      ENUM('pending','confirmed','cancelled','checked_in','no_show') NOT NULL DEFAULT 'pending',
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX (flight_id, status)
);

Fare Class Pricing

Fare classes partition economy inventory into buckets with different prices and restrictions. Common classes:

• Y: full-fare economy — fully refundable, no change fee, highest price.
• Q: discount economy — non-refundable, change fee applies, lower price.
• J: business class — separate cabin, highest fare, priority boarding.

CREATE TABLE fare_inventory (
  flight_id   BIGINT NOT NULL REFERENCES flight(flight_id),
  fare_class  CHAR(1) NOT NULL,
  total_seats INT NOT NULL,
  sold_seats  INT NOT NULL DEFAULT 0,
  price       DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (flight_id, fare_class)
);

When a seat is booked in fare class Q, sold_seats is incremented for that class. If sold_seats = total_seats for Q, no more Q-class bookings are accepted even if Y-class seats remain — each bucket is independent.

Seat Hold with Distributed Lock

For multi-seat bookings (e.g., a family of four), all seats must be held atomically. The approach: acquire a Redis lock keyed on flight_id with a short TTL (2 seconds), execute the batch seat UPDATE, release the lock. If the lock cannot be acquired within 500ms, return a retry-later response. This avoids partial holds where some seats succeed and others fail.

Waitlist

When a flight is sold out in a given fare class, passengers can join a waitlist. The waitlist is ordered first by fare class (J before Y before Q) then by join time. On a cancellation, the freed seat is offered to the top waitlist entry matching the same or lower cabin class. The system sends a notification with a 30-minute response window; if not accepted, the next entry is tried.

Baggage Allowance

CREATE TABLE baggage_allowance (
  fare_class     CHAR(1) NOT NULL,
  carry_on_kg    TINYINT NOT NULL,
  checked_bags   TINYINT NOT NULL,
  checked_bag_kg TINYINT NOT NULL,
  PRIMARY KEY (fare_class)
);

Extra bags beyond the allowance trigger a fee recorded as a separate line item on the booking. Excess baggage at the gate is flagged during check-in.

Check-in Window Enforcement

Online check-in opens 24 hours before departure and closes 45 minutes before. The check-in endpoint validates: NOW() >= departure - interval 24 hour and NOW() <= departure - interval 45 minute. Bookings not checked in by cutoff are marked no_show and the seat is released for standby passengers at the gate.

Key Design Decisions

• 15-minute seat hold with TTL prevents inventory being locked by abandoned sessions without requiring a scheduled cleanup job.
• Fare class inventory buckets are a separate table so pricing and availability per class can be queried without scanning all seat rows.
• Distributed lock on multi-seat holds prevents partial allocation across concurrent requests for the same flight.
• Waitlist priority by fare class ensures higher-revenue passengers are accommodated first on cancellations.

See also: Scale AI Interview Guide 2026: Data Infrastructure, RLHF Pipelines, and ML Engineering

See also: Netflix Interview Guide 2026: Streaming Architecture, Recommendation Systems, and Engineering Excellence

See also: Airbnb Interview Guide 2026: Search Systems, Trust and Safety, and Full-Stack Engineering