Travel Itinerary Database Design

Hi All, I am trying to design a travel requests app in MS Access. The program should be able to take flight/car, hotel, or taxi, arrangements and also get confirmation. Every itinerary sometimes requires a hotel, taxi. I have attached an image of the relationhips that I have designed. Can you take a look and see if I am going in the right direction or if there are suggestions that I can make

I have the following Tables

Itinerary

ItineraryDetails

ItineraryAccomodations

Hmmm well, I'm not sure what you mean by "get confirmation" If you mean it should be able to store the confirmation # that's fine, if you mean it should be able to automate the confirmation, that will take advanced work.

You mention requests so I"m assuming you want to track requests along and then the actual trip details. That will be a bit more complex, but doable for someone comfortable with MS access. If all you are tracking is the final arrangements, that is a bit simpler, but will still take more than a few tables to do it well.

Off the top of the my head, here's what I would do for a pure itinerary tracking DB (No requests, preferences, etc)

<b>Traveler</b>
Traveler_ID
First_Name
Last_Name
Middle_Initial
Home_Airport_Code

<b>Trip</b>
Trip_ID
Trip_Start_Date
Trip_End_Date
Traveler_ID

<b>Trip_Flights</b>
Trip_Flight_ID
Trip_ID
Flight_Confirmation_Num
Flight_Date
Flight_Deptart
Flight_Arrive
Flight_Start_Airport_Code
Flight_End_Airport_Code

<b>Trip_Hotels</b>
Trip_Hotel_ID
Trip_ID
Hotel_Confirmation_Num
Hotel_Name
Hotel_City
Hotel_State
Hotel_Start_Date
Hotel_Days_Count

<b>Trip_Car</b>
Trip_Car_ID
Trip_ID
Car_Confirmation_Num
Car_Rental_Company
Car_PU_Airport_Code
Car_Drop_Airport_Code
Car_Start_Date
Car_Days_Count
Car_Type


This doesn't cover taxis, or car services. (if you need to add those it won't be hard.) It does allow for trips that cover multiple cities. It does break flights into the individual legs, which you may or may not want, but it allows for 1 - ... however many flights for a single trip.

Hi dykebert,

Sorry for not putting the details I was trying to put a picture to provide more info but i on this forum or i haven't figured out how to yet.

In regards to your design how do you match flight record with car rental or hotel accomodation. Say I have multiple flight entries only one car rental entry.

The Trip_ID ties all of the flights, car, and hotel records together.

But for every TripID there are numerous flight entries and car entries.

For example

I fly from NYC to LA, from LA to Seattle then from Seattle to NYC. While in LA I will rent a car. How will i Know for the trip leg of NYC to LA that I have a car rental and not for the others.

Thank you,