Core Entities
Flight: flight_id, airline, flight_number, origin_airport, destination_airport, departure_time, arrival_time, aircraft_type, status (SCHEDULED, BOARDING, DEPARTED, ARRIVED, CANCELLED, DELAYED). Seat: seat_id, flight_id, seat_number, class (ECONOMY, BUSINESS, FIRST), status (AVAILABLE, HELD, BOOKED), features (WINDOW, AISLE, EXTRA_LEGROOM). Booking: booking_id, booking_reference (6-char alphanumeric), user_id, status (CONFIRMED, CANCELLED, REFUNDED), total_price_cents, currency, created_at. BookingItem: item_id, booking_id, flight_id, passenger_id, seat_id, ticket_price_cents, fare_class. Passenger: passenger_id, first_name, last_name, passport_number, date_of_birth, nationality. Itinerary: itinerary_id, booking_id, legs (ordered list of flight_ids with connection times).
Flight Search
Search for flights between origin and destination on a given date. Direct flights: SELECT from flights WHERE origin=X AND destination=Y AND DATE(departure_time)=D AND status=SCHEDULED ORDER BY departure_time. Connecting flights: find one-stop connections. Query leg 1: origin=X, departure date=D. For each result: query leg 2 with origin=leg1.destination AND departure_time between leg1.arrival_time+60min AND leg1.arrival_time+360min (min/max connection window). This join can be expensive for large datasets. Pre-compute connections: a batch job builds a connections table for all city-pair combinations with valid layovers. Cache flight availability in Redis: available_seats:{flight_id} = SET of available seat IDs. Check SCARD (set cardinality) to show “7 seats left” without hitting the DB.
Seat Selection and Hold
When a user selects a seat during booking: create a temporary hold. Redis atomic operation: SREM available_seats:{flight_id} {seat_id} — returns 1 if the seat was in the set (available), 0 if already removed (unavailable). If 1: set a hold expiry key: SET seat_hold:{flight_id}:{seat_id} {session_id} EX 900 (15-minute hold). If 0: seat was taken concurrently — prompt user to select another. On payment success: remove from Redis available_seats (already removed), update DB seat status to BOOKED, delete the hold key. On session expiry (15 minutes without payment): a background job detects expired hold keys (Redis keyspace notifications on expiry events), releases the seat: SADD available_seats:{flight_id} {seat_id}, update DB status to AVAILABLE. This two-layer approach (Redis gate + DB source of truth) handles the high-concurrency seat selection at scale.
Pricing Engine
Flight prices are dynamic: they change based on demand, days until departure, seat class, and fare rules. Price components: base fare (set by the airline per fare class), taxes and fees (airport tax, fuel surcharge, carrier fees — varies by route), ancillary services (baggage, seat upgrade, meals). Fare classes: within Economy there are 10+ fare buckets (Y, B, M, H, K, L, V, Q, T, X) with different prices and restrictions (refundable, changeable, advance purchase required). On search: query current fares for each flight from a fare cache (updated every 5 minutes from the airline’s GDS connection). The displayed price = fare + all mandatory taxes. Fare lock: user can lock the current price for 24 hours for a small fee — store a PriceLock record with the locked amount and expiry.
Check-In and Boarding Pass
Online check-in opens 24-48 hours before departure. On check-in: verify booking is CONFIRMED and not cancelled. Collect travel documents (passport scan or number). Assign seat if not already selected (or confirm existing). Generate a boarding pass: boarding_pass_id, barcode (encodes: airline, flight number, passenger name, seat, booking reference, departure date/time). Store as PDF and send to the user’s email. Mobile boarding pass: generate a QR code or Aztec code. At the gate: scanner reads the barcode, calls the boarding API to validate: is this boarding pass for today’s flight? Has it already been scanned (prevent re-entry)? Mark as SCANNED in real time. Boarding sequence: sort by zone (first/business first, then economy by seat row).
Asked at: Airbnb Interview Guide
Asked at: Uber Interview Guide
Asked at: Stripe Interview Guide
Asked at: Lyft Interview Guide