The main entity is a booking (bookings).

One booking can include several passengers, with a separate ticket (tickets) issued to each passenger for one-way trip and a separate return ticket for the round trip. A ticket has a unique number and includes information about the passenger. As such, a passenger is not a separate entity, nevertheless the document number uniquely identifies the passenger.

The ticket includes one or more flight segments (segments). Several flight segments can be included into a single ticket if there are no non-stop flights between the points of departure and destination (connecting flights). Although there is no constraint in the schema, it is assumed that all tickets in the booking have the same flight segments.

Each route (routes) goes from one airport (airports) to another. Flights (flights) with the same route number have the same points of departure and destination, but differ in departure date. The routing map is updated once per month, therefore the flights and routes are linked with the temporal foreign key. When joining, mind not only the route number, but also the departure time that must fit within the route validity period. To simplify your queries, use the timetable view that hides this join.

All flights are connecting flights: at the first flight check-in, the passenger is issued boarding passes (boarding_passes), for all flights in his ticket, where the seat number and boarding time are specified. The passenger can check in for the flight only if this flight is included into the ticket. The flight-seat combination must be unique to avoid issuing two boarding passes for the same seat.

The number of seats (seats) in the airplane and their distribution between different travel classes depends on the model of the airplane (airplanes) performing the flight. It is assumed that every airplane model has only one cabin configuration. Database schema does not check that seat numbers in boarding passes have the corresponding seats in the airplane (such verification can be done using table triggers, or at the application level).