Airline Reservation Database Schema database schema

What this schema models

An airline reservation and departure control database schema built on the distinctions that actually define the industry. 34 tables and 24 enums covering airports and fleet, seat maps, routes and dated flights, crew rostering, fare classes and revenue-managed inventory, passengers and frequent flyer accounts, PNR bookings, ticketing with coupons, seat assignment, special service requests, waitlists, ancillaries, baggage, check-in and boarding, payments and refunds, and irregular operations. THE THREE DISTINCTIONS 1. A BOOKING is not a TICKET. The booking — the PNR, identified by that familiar six-character record locator — is the reservation and the itinerary. The ticket is the FINANCIAL document issued against it. One booking can produce several tickets, a ticket can be reissued while the PNR survives, and refunds act on tickets, never on the reservation. Most published airline ER diagrams collapse these two and then cannot model an exchange. 2. A TICKET is not a FLIGHT. Each ticket carries one COUPON per flown segment, and every coupon is independently used, refunded or exchanged. That is precisely why a passenger can no-show one leg and still fly the next, and why coupon status is its own enum. 3. A ROUTE is not a FLIGHT. The route is the city pair and published flight number; a flight is one dated instance of it. Inventory, crew and aircraft hang off the dated flight, never the route. DETAILS WORTH STEALING - Seats are sold per FARE CLASS per flight, not per physical seat. flight_inventory.authorized_seats can deliberately exceed the cabin count — that is how revenue management overbooks on purpose, and the schema should express it rather than fight it. - Seat layout lives in seat_maps rather than on the aircraft type, because one type flies several cabin configurations and a refit must not rewrite history. - Every departure time is a LOCAL time at its airport, which is why airports carries a mandatory timezone. - seat_assignments is keyed per segment, so a connecting journey gets a different seat on each leg, and the unique constraint is what stops 14A being sold twice. - frequent_flyer_accounts separates miles_balance (spendable) from qualifying_miles (drives tier, resets annually). Conflating those two is the classic loyalty-programme bug. - Special service requests use a four-letter varchar code, not an enum, because SSR codes are an IATA standard rather than our own list. - boarding_passes.sequence_number is acceptance order — the input to denied-boarding rules when a flight is oversold. - disruptions.is_carrier_fault is the single column that decides compensation liability under EU261 and similar regimes. - Baggage ties to a segment, not just a passenger, because a bag can be short-checked to a connection. The ten-digit tag number is the industry licence plate. - Money in minor units as integers with an explicit currency; payments carry miles_redeemed alongside cash so award and cash-plus-points bookings share one shape. WHAT YOU GET Clean DBML that imports straight into DB Planner, plus a laid-out board with cards colour-coded by zone — network and fleet, flights and crew, fares and inventory, passengers and loyalty, booking, ticketing, ancillaries and baggage, departure, money and disruption — with sticky notes explaining each decision and every table carrying its own Note. Postgres-flavoured types that port cleanly to MySQL, Oracle or SQL Server. Useful for developers building a reservation system, booking engine or travel aggregator, students who need a realistic airline reservation ER diagram for a DBMS project, and anyone modelling capacity-constrained dated inventory for the first time.

Tables (34)

Relationships (56)

• aircraft.seat_map_id references seat_maps.id many to one
• aircraft.type_id references aircraft_types.id many to one
• ancillary_purchases.booking_id references bookings.id many to one
• ancillary_purchases.booking_passenger_id references booking_passengers.id many to one
• ancillary_purchases.booking_segment_id references booking_segments.id many to one
• ancillary_purchases.product_id references ancillary_products.id many to one
• baggage.booking_passenger_id references booking_passengers.id many to one
• baggage.booking_segment_id references booking_segments.id many to one
• boarding_passes.check_in_id references check_ins.id many to one
• boarding_passes.seat_id references seats.id many to one
• booking_passengers.booking_id references bookings.id many to one
• booking_passengers.passenger_id references passengers.id many to one
• booking_segments.booking_id references bookings.id many to one
• booking_segments.fare_class_id references fare_classes.id many to one
• booking_segments.flight_id references flights.id many to one
• check_ins.booking_passenger_id references booking_passengers.id many to one
• check_ins.booking_segment_id references booking_segments.id many to one
• crew_assignments.crew_member_id references crew_members.id many to one
• crew_assignments.flight_id references flights.id many to one
• crew_members.base_airport references airports.iata_code many to one
• disruptions.flight_id references flights.id many to one
• fare_rules.fare_id references fares.id many to one
• fares.fare_class_id references fare_classes.id many to one
• fares.route_id references routes.id many to one
• flight_inventory.fare_class_id references fare_classes.id many to one
• flight_inventory.flight_id references flights.id many to one
• flight_status_events.flight_id references flights.id many to one
• flights.aircraft_id references aircraft.id many to one
• flights.route_id references routes.id many to one
• frequent_flyer_accounts.passenger_id references passengers.id many to one
• mileage_transactions.account_id references frequent_flyer_accounts.id many to one
• mileage_transactions.ticket_id references tickets.id many to one
• payments.booking_id references bookings.id many to one
• rebookings.disruption_id references disruptions.id many to one
• rebookings.new_segment_id references booking_segments.id many to one
• rebookings.original_segment_id references booking_segments.id many to one
• refunds.payment_id references payments.id many to one
• refunds.ticket_id references tickets.id many to one
• routes.destination_code references airports.iata_code many to one
• routes.origin_code references airports.iata_code many to one
• seat_assignments.booking_passenger_id references booking_passengers.id many to one
• seat_assignments.booking_segment_id references booking_segments.id many to one
• seat_assignments.seat_id references seats.id many to one
• seat_maps.aircraft_type_id references aircraft_types.id many to one
• seats.seat_map_id references seat_maps.id many to one
• special_service_requests.booking_passenger_id references booking_passengers.id many to one
• special_service_requests.booking_segment_id references booking_segments.id many to one
• ticket_coupons.booking_segment_id references booking_segments.id many to one
• ticket_coupons.ticket_id references tickets.id many to one
• tickets.booking_id references bookings.id many to one
• tickets.booking_passenger_id references booking_passengers.id many to one
• tickets.fare_id references fares.id many to one
• tickets.reissued_from_ticket_id references tickets.id many to one
• waitlists.booking_id references bookings.id many to one
• waitlists.fare_class_id references fare_classes.id many to one
• waitlists.flight_id references flights.id many to one

How to use this template

1. Open the diagram to see the tables and how they relate.
2. Copy it into your workspace. You get your own editable copy.
3. Export it as PostgreSQL, MySQL, or MSSQL DDL, or as C# models.

Frequently asked questions

What is in the Airline Reservation Database Schema schema?

34 tables and 56 relationships, across 261 columns. The table names and the full relationship list are shown on this page.

Can I export it to PostgreSQL or MySQL?

Yes. Every DB Planner template exports as DBML, PostgreSQL, MySQL, or MSSQL DDL, and as C# models.

Is this template free?

Yes. You can open it in the browser without an account, and copy it into your own workspace with a free DB Planner account.

Can I change it after copying it?

Yes. A copy is yours: you can rename tables, add columns, and export it. Changes the author makes later do not affect your copy.