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:

AirLeg table:

- id
- departure and arrival information
- viaPoints: BOOL

viaPoints table:

- 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:

  • AirLeg-id
  • 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)

EDIT:

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.