Itinerary Design

[xraive]

HI guys,


Iam am having issues setting up an itinerary database application. My current setup is includes the follwoing tables.

Itinerary ItineraryID(PK)
TravellerID
StarDate
EndDate

Trips
TripID(PK)
ItineraryID(FK)
From
To
ReservationStat us

Flights
FlightID(PK)
TripID(FK)
AirlineID
DepartCity
DepartDate
DepartTime
ArrivalCity
ArrivalDate
ArrivalTime
Confirmation#
Staus

I currently have a list of the flights including (Flight#,Locati ons, and Times) This information rarely changes. So i created 3 tables to represent this. See attached picture. I am having issues because I want to find a way to identify if a flight trip is one way or round trip. Also I would like to have the correct design so users can just fill out an itinerary and based on the entry make the correct flight entries, which may include connections or stop offers. Can you take a look to see if there is anything i am missing I can show a sample of the database.

[nico5038]

I want to find a way to identify if a flight trip is one way or round trip.

When you process the flights for one passenger "in sequence" of date time and the first departure city matches the last arrival city you have a round trip.
The number of intermediate stops won't be visible using First() and Last()....

Nic;o)