I'm attempting to design the after sales and also have the following use situation.
I've flight information from point One place to another and want to define a schema which assists different use cases.
I am looking for a great way to handle situation, when you will find stopover via points.
For e.g. flight route for any -> B really appears like this:
A -> C C -> D D -> B
so A -> B is a entity, however in turn, it's composed of countless legs.
My current design:
- id - departure and arrival information - viaPoints: BOOL
- id - airLegId // FK into Airleg table - similar departure and arrival information from airLeg table
// if viaPoints flag holds true in AirLeg table, viaPoints table could be queried for, using airLegId table to retrieve intermediaries.
It is possible to better way to cope with this ?
I figured I'll add the data I'm storing in regards to a one of the ways trip or segment:
- Departure Airport terminal : FK into international airports
- Arrival Airport terminal : FK into international airports
- Departure timestamp (in departure city's local time)
- Arrival timestamp (in arrival city's local time)
- flight amount of this airleg: static value
- flightId : FK into air carriers yielding air travel title and flight number
- Baggage Policy : text
- Misc (TEXT: Cancellation policy)
I added an associated question and i believe the response to this issue will need to focus on both needs.
If you will find multiple segments inside a trip, cost is determined for that complete trip and never individual segments
Similarly, the cost for any round trip is specified like a unit and never individual aspects of A->B and back, B->A.
I'd set it up such as this:
Journeys: - ID - Other info (billing, whatever) Segments: - ID - JourneyID (FK) - departure, arrival, etc
As well as an additional view
Journeys_View - Journeys.* - First departure - Last arrival
I am attempting to patch together the 2 questions, and it is not totally obvious what for you to do - but It boils lower towards the following.
You possess an itinerary, the parent item. An "itinerary" has multiple legs (question: would you like to cope with multi-part itinerarys, e.g. "London->Paris->New You are able to->London"?). A trip includes a cost. The cost isn't the sum cost from the legs, because return outings are less costly than two one ways.
Itinerary --------- ID Price Leg ---- Departure Airport : FK into airports Arrival Airport : FK into airports Departure timestamp (in departure city's local time) Arrival timestamp (in arrival city's local time) flight duration of this airleg: static value flightId : FK into airlines yielding airline name and flight number Baggage Policy : text Misc (TEXT: Cancellation policy)
You can store cost inside a separate table - however, you only have to do that if cost changes individually of itinerary (e.g. when the cost on Monday is $100, as well as on Tuesday it's $200).
I'd encourage explore to make use of "magic numbers" inside your database schema - rather than getting the return leg be "-1", you need to let it rest NULL - there's no return leg. This will make your SQL a great deal simpler to see, and less error prone - you do not rely on designers recalling that "-1" means there is no return leg, -2 meaning there is a provisionally reserved leg etc.