Airline Reservation data model
Normalized (3NF) airline reservation model. A schedule spine of flight to dated flight-leg over aircraft and airports, priced by fares, feeds bookings (PNRs) containing passengers, segments, and tickets, with seat assignments and baggage tracked at the leg level and a frequent-flyer loyalty side. Passenger PII is classified and masked.
Airline passenger service system (PSS) operations: the airport/aircraft/flight schedule and dated operating legs, fare/booking classes, the PNR booking with passengers, itinerary segments, and issued tickets, plus seat assignments, checked baggage, and frequent-flyer accounts — modeled as a normalized OLTP reservation and inventory schema with passenger PII classified and masked. It is a normalized (3NF) relational model that mirrors the source system, suited to operational databases and to landing source data faithfully.
- 12
- tables
- 114
- columns
- 16
- relationships
- 9
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: PostgreSQL). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Flight schedule vs. dated operating leg
A flight number is a recurring scheduled service, while a flight leg is one dated departure flown by a specific aircraft with its own inventory and times; separating them is essential for seat availability and irregular operations.
PNR with passengers, segments, and tickets
A booking (PNR) groups one or more passengers and an itinerary of segments, while tickets are issued per passenger; modeling these as distinct related entities matches GDS/PSS semantics in 3NF.
Airport as a lookup
Airports are slowly changing static reference data shared across flights, so a lookup table avoids repeating IATA/timezone attributes on every flight row.
Normalized (3NF) rather than dimensional
Reservation and inventory processing is highly transactional with constant seat-count and PNR updates, where relational integrity outweighs analytical aggregation.
Tables in the Airline Reservation model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Tables (11)
aircraft
Grain: one row per aircraft tail
Fleet asset.
| Column | Type | Keys |
|---|---|---|
| aircraft_key | bigint | PK |
| tail_number | varchar | BK |
| aircraft_type | varchar | |
| manufacturer | varchar | |
| total_seats | integer | |
| seat_configuration | varchar | |
| max_range_km | integer | |
| status | varchar |
flight
Grain: one row per scheduled flight number
Scheduled service.
| Column | Type | Keys |
|---|---|---|
| flight_key | bigint | PK |
| flight_number | varchar | BK |
| airline_code | varchar | |
| origin_airport_key | bigint | |
| destination_airport_key | bigint | |
| scheduled_departure | varchar | |
| scheduled_arrival | varchar | |
| duration_minutes | integer | |
| frequency | varchar | |
| is_active | boolean |
flight_leg
Grain: one row per dated, operating flight leg
Operating instance (one dated departure).
| Column | Type | Keys |
|---|---|---|
| flight_leg_key | bigint | PK |
| flight_key | bigint | |
| aircraft_key | bigint | |
| leg_id | varchar | BK |
| departure_date | date | |
| scheduled_departure_at | timestamp | |
| scheduled_arrival_at | timestamp | |
| actual_departure_at | timestamp | |
| actual_arrival_at | timestamp | |
| gate | varchar | |
| status | varchar | |
| seats_available | integer |
fare
Grain: one row per fare/booking class on a flight
Fare product / RBD.
| Column | Type | Keys |
|---|---|---|
| fare_key | bigint | PK |
| flight_key | bigint | |
| fare_basis | varchar | BK |
| booking_class | varchar | |
| cabin | varchar | |
| base_fare | decimal | |
| taxes_fees | decimal | |
| currency | varchar | |
| refundable | boolean | |
| change_fee | decimal | |
| baggage_allowance_kg | integer | |
| is_active | boolean |
frequent_flyer_account
Grain: one row per loyalty account
Frequent-flyer membership.
| Column | Type | Keys |
|---|---|---|
| frequent_flyer_account_key | bigint | PK |
| membership_number | varchar | BK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| varchar | PII | |
| tier | varchar | |
| points_balance | integer | |
| lifetime_miles | integer | |
| enrolled_date | date | |
| status | varchar |
booking
Grain: one row per booking (PNR)
Passenger Name Record.
| Column | Type | Keys |
|---|---|---|
| booking_key | bigint | PK |
| record_locator | varchar | BK |
| booking_channel | varchar | |
| currency | varchar | |
| total_amount | decimal | |
| status | varchar | |
| contact_email | varchar | PII |
| contact_phone | varchar | PII |
| booked_at | timestamp |
passenger
Grain: one row per passenger on a booking
Booking traveler.
| Column | Type | Keys |
|---|---|---|
| passenger_key | bigint | PK |
| booking_key | bigint | |
| frequent_flyer_account_key | bigint | |
| passenger_id | varchar | BK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| date_of_birth | date | PII |
| passport_number | varchar | PII |
| nationality | varchar | |
| passenger_type | varchar |
segment
Grain: one row per flown segment in an itinerary
Itinerary leg coupon.
| Column | Type | Keys |
|---|---|---|
| segment_key | bigint | PK |
| booking_key | bigint | |
| flight_leg_key | bigint | |
| fare_key | bigint | |
| segment_number | integer | |
| cabin | varchar | |
| booking_class | varchar | |
| status | varchar |
ticket
Grain: one row per issued ticket
E-ticket document.
| Column | Type | Keys |
|---|---|---|
| ticket_key | bigint | PK |
| booking_key | bigint | |
| passenger_key | bigint | |
| ticket_number | varchar | BK |
| fare_amount | decimal | |
| tax_amount | decimal | |
| total_amount | decimal | |
| currency | varchar | |
| status | varchar | |
| issued_date | date |
seat_assignment
Grain: one row per passenger seat on a leg
Seat-map assignment.
| Column | Type | Keys |
|---|---|---|
| seat_assignment_key | bigint | PK |
| passenger_key | bigint | |
| flight_leg_key | bigint | |
| seat_number | varchar | |
| seat_type | varchar | |
| cabin | varchar | |
| seat_fee | decimal | |
| assigned_at | timestamp |
baggage
Grain: one row per checked bag
Checked baggage item.
| Column | Type | Keys |
|---|---|---|
| baggage_key | bigint | PK |
| passenger_key | bigint | |
| segment_key | bigint | |
| bag_tag_number | varchar | BK |
| weight_kg | decimal | |
| bag_type | varchar | |
| excess_fee | decimal | |
| status | varchar |
Lookup tables (1)
airport
Grain: static reference of airports
Airport reference.
| Column | Type | Keys |
|---|---|---|
| airport_key | bigint | PK |
| iata_code | varchar | BK |
| icao_code | varchar | |
| airport_name | varchar | |
| city | varchar | |
| country | varchar | |
| timezone | varchar | |
| latitude | decimal | |
| longitude | decimal |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- flight.origin_airport_key → airport (many-to-one)
- flight.destination_airport_key → airport (many-to-one)
- flight_leg.flight_key → flight (many-to-one)
- flight_leg.aircraft_key → aircraft (many-to-one)
- fare.flight_key → flight (many-to-one)
- passenger.booking_key → booking (many-to-one)
- passenger.frequent_flyer_account_key → frequent_flyer_account (many-to-one)
- segment.booking_key → booking (many-to-one)
- segment.flight_leg_key → flight_leg (many-to-one)
- segment.fare_key → fare (many-to-one)
- ticket.booking_key → booking (many-to-one)
- ticket.passenger_key → passenger (many-to-one)
- seat_assignment.passenger_key → passenger (many-to-one)
- seat_assignment.flight_leg_key → flight_leg (many-to-one)
- baggage.passenger_key → passenger (many-to-one)
- baggage.segment_key → segment (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.