Airline Ticket Management System (LLD)

0) Problem Restatement

Design the core of an airline ticket system (asked at Flipkart as an LLD round: entities, schema, APIs and service implementation). Users search flights, choose a fare, pick seats for one or more passengers, pay, and get a booking (PNR). They can cancel and get a refund according to fare rules. A seat can never be sold twice.

1) Requirements

• Flights with schedules, aircraft and seat maps.
• Fare classes (e.g., Economy Saver, Economy Flex, Business) with prices, seat counts and refund rules.
• Search flights between cities on a date.
• Book seats for multiple passengers atomically, with a temporary hold during payment (e.g., 10 minutes).
• Cancel and refund according to rules. Show booking details by PNR.

2) Entities and Schema

classDiagram
    class Flight { +id +flightNo +origin +destination +departAt +arriveAt +aircraftId }
    class Seat { +flightId +seatNo +cabin +status +holdId +version }
    class FareClass { +flightId +code +price +totalSeats +soldSeats +refundPolicy }
    class Booking { +pnr +userId +flightId +fareCode +status +totalAmount +expiresAt }
    class Passenger { +bookingPnr +name +dob +seatNo }
    class Payment { +id +pnr +amount +status }
    Flight "1" --> "*" Seat
    Flight "1" --> "*" FareClass
    Booking "1" --> "*" Passenger
    Booking --> Payment

CREATE TABLE seats (
  flight_id BIGINT, seat_no TEXT, cabin TEXT,
  status TEXT,             -- available, held, booked
  booking_pnr TEXT, hold_expires_at TIMESTAMP, version INT,
  PRIMARY KEY (flight_id, seat_no)
);
CREATE TABLE fare_classes (flight_id BIGINT, code TEXT, price_cents INT, total_seats INT, sold_seats INT,
                           refund_policy JSONB, PRIMARY KEY (flight_id, code));
CREATE TABLE bookings (pnr TEXT PRIMARY KEY, user_id BIGINT, flight_id BIGINT, fare_code TEXT,
                       status TEXT,   -- held, confirmed, cancelled, expired
                       total_cents INT, created_at TIMESTAMP, expires_at TIMESTAMP, idempotency_key TEXT UNIQUE);

3) Services and APIs

• GET /flights?from=BLR&to=DEL&date=2026-10-01 → flights with fares and availability
• POST /bookings (Idempotency-Key) { flight_id, fare_code, passengers: [...], seats: ["12A","12B"] } → { pnr, status: "held", expires_at }
• POST /bookings/{pnr}/pay → { status: "confirmed" }
• POST /bookings/{pnr}/cancel → { refund_amount }

Service classes: FlightSearchService, BookingService (hold, confirm, cancel), SeatService, FareService, PaymentService, RefundPolicy (a strategy per fare class).

4) Booking Flow (concurrency-safe)

In one DB transaction:

1. Fare inventory: UPDATE fare_classes SET sold_seats = sold_seats + n WHERE flight_id=? AND code=? AND sold_seats + n <= total_seats. 0 rows → sold out.
2. Seats: for each requested seat, UPDATE seats SET status='held', booking_pnr=?, hold_expires_at=now()+10min WHERE flight_id=? AND seat_no=? AND status='available'. If any update affects 0 rows → roll back everything ("seat 12B was just taken").
3. Insert the booking (held) and passengers.

5) Cancellation and Refunds

• RefundPolicy depends on the fare class: Saver = no refund (taxes only), Flex = full refund minus fee until 24h before departure.
• Cancellation: booking → cancelled, seats → available, sold_seats -= n, and issue the refund via payments (idempotent by PNR).

6) Design Notes (what interviewers look for)

• Separation of concerns: search is read-heavy and cached, while booking is transactional.
• Strategy pattern for refund policies and pricing rules.
• Idempotency for booking creation and payment callbacks.
• Overbooking (airlines sell more than seats in some fare classes): an explicit overbook_limit in fare inventory, but seat assignment still never duplicates a seat.

7) Wrap-Up

Model flights, seat maps, fare-class inventory, bookings (PNR), passengers and payments. Book with one transaction of conditional updates, fare inventory first and then each seat from available to held, rolling back if any seat is taken. Confirm on payment, release expired holds in the background, and handle cancellations with per-fare refund strategies, with idempotency on booking and payment operations.