A key to successful operations is routing passengers as efficiently as possible in order to get as many people together on flights as possible. To do this there will be a certain amount of multi-leg flights where jets land to drop of some passengers and pick up new ones. Keeping all these legs and passengers straight will require careful organization. This begins with the definition and organizational structure for trips, flights and legs.
Trips
Trips are associated directly with Members. When a Member requests a trip their request information is stored in the database including:
- Desired departure airport
- Desired arrival airport
- Desired arrival time
- Number of passengers in part
- Flexibility on time/location
- Special requests
Most trips will be round-trips, so they will include at least two legs, or segments.
Segments
A Flight is a set of one or more segments that get a Member from the departure Airport to the destination Airport identified in a Trip. If a trip is one-way, there will be one flight associated with the Trip. If it is a round-trip, there will be two Flights. So the foreign key in all flights will be a trip_id.
The flight table requires very little data, because all significant data is contained either in the member table (joined via the Trip table), the Trip table, and the segments table. Essentially, the Flight table is a look-up table that links Trips and segments. Following is the data structure of this table:
- flight_id
- trip_id
- segment_id_1
- segment_id_2
- segment_id_3
- segment_id_4
Flights are related to Segments in a many-to-many relationship. A Flight can be associated with up to four Segments (though this can be easily increased if we choose). That is, it could take up to four segments to get a member from their departure Airport to their destination Airport. And any Segment will generally be associated with multiple Flights. To illustrate this consider a Segment that is defined as a 12-seat jet leaving San Jose and landing in Los Angeles. That segment may show up in Flight records associated with Trips requested by a husband and wife, another party of four, and another team of six people. Note that the total number of passengers equals the number of seats available on the Segment. If the combined passenger count of the Trips associated with the Flights is greater than the seats available on the Segment, then that segment is overbooked.
Relationship Structure
- There is a one-to-many (specifically, one-to-two) relationship between Trips and Flights (there is an inbound and outbound Flight per Trip)
- A Flight is associated with a specific Member/party. So since on any given Segment there will likely by multiple Member/parties, a Segment will typically be associated with multiple Flights.
- And because any given Flight will often consist of two legs, there is Flight can be associated with multiple Segments.
- Thus, there is a many-to-many relationship between Flights and Segments
Example:
Trip 1 is associated with Flight 1 and Flight 4
Flight 1 is associated with Segment 1 and Segment 2
Segment 2 is associated with Flight 1 and Flight 2
Because all the specific details are contained in the ‘trip’ table (member info, passenger count, dates, airports, status) and the ‘leg’ table (aircraft, seating capacity, departure/arrival times, distance) there really are no details needed in the ‘flight’ table. So it basically serves the purpose of being a lookup table between Trips and Segments. If we need to know which the departure time for a given Trip, we lookup up the Flights associated with the Trip and thereby the Segment data (an inner join SQL query). If we need to know which Member is booked on a given Segment, we look up the Flight associated with the Segment and thereby the Trip data.