Help With Table Relationships for Travel Booking Company

[hhound004]

I have a need to have a working database for a travel booking site that handles hotels, flights, and car rentals.

Tables:

Customers: CUSTID, First Name, Last Name, Address etc...
Sales Orders: ORDERID,CUSTID, Order Date, Order Method
Invoices: INVOICEID, CUSTID, ORDERID, Invoice Date, Amount etc...
Flights: FLIGHTID, Depart City, Depart Time, Depart Date, Arrival etc...





I'm struggling with the relationships to setup. I need to be able to run queries on invoices and customers.

I view hotels, flights, car rentals as products but they are very different, especially how flights have itineraries. So I am confused how to set that up but I think they all have to be separate tables. Then how do I link each of those 3 tables back to an order and invoice?

I was going to link it through the "sales orders" table but I am not so sure that will work or how to set it up.

Any help would be greatly appreciated, thank you.

[ranman256]

I think these items would appear in the tSalesOrderDetail. (Like an online shopping cart)
TSalesOrder table would have a child table tSalesOrderDetails
OrderID
CarID
HOtelID
FlightID

these ID key fields would join to their detail tables:
tCars
tHotels
tFlights


the tSalesOrderDetails would allow for many entries, which you may not need.
it a client doesn't need 2 flights,or 2 cars,etc,
Then you wouldnt need the child table ,tSODetail, and could put these fields in tSalesOrder master table. But you only get ONE car , oneflight etc.

[hhound004]

I'm pretty new to Access so let me see if I understand this correctly, and thanks for your response.

I should setup:

TSalesOrder
Basic fields I listed in my first post including ORDERID and CUSTID

TSalesOrderDetails
This table would have all the main ID types so they're linked. ORDERID, CARID, HOTELID, FLIGHTID

TCarRentals
Main table for CARID and all the details around the car rentals.

THotelBookings
Main table for HOTELID and details

TFlightBookings
Main table for FLIGHTID and details

Just wondering if thats correct and should work or not. Thanks again.

[orange]

You have stated that you are pretty new to Access, but do you have any experience or study related to Database Design or Concepts?

Relationships are not arbitrary lines between tables. Relationships are based on facts you discover when you analyze your business and its processes. There are some excellent free youtube "nuggets" by BA-Experts to help with tools and usage for gathering business facts.

Work through a tutorial or 2 from RogersAccessLibrary. Each one will take about 45-60 minutes, but you will learn about normalization and relationships through experience when working through the tutorial(s). Here are a couple, but there are more on the same site.

Entity Relationship Diagramming
Class information System

For some database concepts:
You might try watching some free video tutorials by Dr.Daniel Soper that will help you with the concepts.

Intro to Database
The Relational Model
Data Modelling and the ER Model

Normalization article: (google normalization or Normal Forms to get more)
Normalization by RogerCarlson

There are a number of free generic data models at Barry Williams' site. You might be able to glean info from a few of these to help with your project. Look at a few under Reservations.

Good luck.

[hhound004]

You have stated that you are pretty new to Access, but do you have any experience or study related to Database Design or Concepts?

I have some basic experience with databases but never designing them.

So I am just needing a push in the right direction. I think I could fully handle a really basic design with some tangible products but for whatever the reason I find it a challenge to handle all of flights, car rentals and hotels. They don't have the same product attributes (flights have depart/arrival times, hotels have room options, car rentals have pickup times etc...) and that confuses me in knowing what direction to go in with the database.

[orange]

The best push I can offer is for you to describe in plain English what your business is about. Start at the 30,000 foot level and gradually add detail, until it covers a "day in the life of our business" (or a processing cycle if that makes more sense.) This will eventually lead to a requirements document or a specifications document. The sort of thing a consultant would want if he/she were to be engaged by you or your firm to build such a database.

Review some of the links I offered earlier, then adapt to your situation.

You will learn more about your business and its rules by writing this description than you can imagine.
Here is an article than may offer some insight to you.

Good luck.

[HiTechCoach]

hhound004,

It is getting close to the send of the school term. I am seeing this years questions about the common class projects.

I have some basic experience with databases but never designing them.

So I am just needing a push in the right direction. I think I could fully handle a really basic design with some tangible products but for whatever the reason I find it a challenge to handle all of flights, car rentals and hotels. They don't have the same product attributes (flights have depart/arrival times, hotels have room options, car rentals have pickup times etc...) and that confuses me in knowing what direction to go in with the database.

I see flights, car rentals, and hotels as all the same. They all have a start date/time and end date/time. Flights have a departures and arrival date/time. Car rentals has a pickup and return date/time. Hotels have a check in and check out date/time.

It is possible multiple flights, multiple hotels stays and even multiple car rentals all in a single trip/booking. I have done it more than a few times while traveling.


I had a trips like this before:
Flight 1
Flight 2 (connecting flight)
Car rental 1 pick up
Hotel 1 check in
Hotel 1 check out
Car rental 1 return
Flight 3
Car rental 2 pickup
Hotel 2 check in
Hotel 2 check out
Hotel 3 check in (had to switch hotels due to room availability or changed cities)
Hotel 3 check out
Car rental 2 return
Flight 4