Design an airline reservation system database
PremiumDatabase designMedium
- Seat inventory
- PNR
- Booking holds
Quick answer
This database design interview question asks you to design an airline reservation system database. Aim for a design that stores data correctly and serves its main queries well, then explain the schema, keys, indexes and trade-offs. Interviewers want to see how you model entities, choose keys and design for the real access patterns.
The problem
Design the database for an airline reservation system. Customers search routes, book an itinerary for several passengers, hold it for 15 minutes while paying, get ticketed, pick seats and check in. Overbooking of a few percent is allowed by policy; double-selling a specific seat is not.
The airline publishes a schedule of flights; each departure on a date is a flight instance with an aircraft, a seat map and a set of fare classes with a number of seats for sale in each. Itineraries are direct or have one connection.
What must an airline reservation system database do?
- Book: Create a booking (PNR) for 1 to 9 passengers across 1 to 4 flight legs; decrement the fare-class inventory on every leg or on none.
- Seat selection: Assign a specific seat per passenger per leg from the aircraft seat map; a seat on a leg belongs to at most one passenger.
How well must it do it?
- Inventory accuracy: A fare class on a flight instance never sells more seats than its allocation plus the overbooking allowance.
- Atomic itineraries: A multi-leg booking takes inventory on all legs or none.
What scale should you design for?
Use these numbers in your estimates.
The scale numbers unlock with this questionUnlock
Key concepts
- Seat inventory
- PNR
- Booking holds
- Optimistic locking
- Fare classes
- Multi leg itineraries
How you’ll practise
- Access patterns
- Entities
- Schema
- Indexes and queries
- Scale and consistency
- Wrap-up
Lala, personalized to you, gives a hint when you ask and reviews your design when you are ready.
What do interviewers look for?
In any database design round.
- Lists the entities and how they relate before writing tables
- Designs the schema around the main access patterns
- Chooses primary keys, foreign keys and indexes with reasons
- Explains where to normalise and where to denormalise
- Considers integrity, transactions and concurrent writes
- Plans for growth: partitioning, archiving and migrations
Questions people ask about this interview question
- Which design type is this question?
- It is a database design question. You design an airline reservation system database and explain your choices the way you would in an interview.
- Is this question asked at entry level?
- It is marked Medium. Entry-level engineers can practise it, but it expects you to reason about requirements and trade-offs, not only list components.
- What should I cover first?
- Start with the entities and the main queries the database must answer. Agree on scope with the interviewer before you design anything.
- Can I practise it for free?
- It is a premium question. Sign up and unlock it once for 10 credits; it then stays unlocked. When you finish, Lala, your personalized AI mentor, can review your design for 30 credits.
Related questions
Updated · Written by the SevyDevy team