Results 1 to 7 of 7
  1. #1
    hhound004 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2017
    Posts
    3

    Lightbulb Help With Table Relationships for Travel Booking Company

    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...
    Click image for larger version. 

Name:	relationships.JPG 
Views:	22 
Size:	39.4 KB 
ID:	27976




    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.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    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.

  3. #3
    hhound004 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2017
    Posts
    3
    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.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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.

  5. #5
    hhound004 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2017
    Posts
    3
    Quote Originally Posted by orange View Post
    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.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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.

  7. #7
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    hhound004,

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

    Quote Originally Posted by hhound004 View Post
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 12-31-2016, 12:29 PM
  2. How to link booking history table
    By JackieFeng in forum Access
    Replies: 1
    Last Post: 04-21-2016, 05:18 AM
  3. Replies: 7
    Last Post: 08-09-2013, 12:04 AM
  4. Connecting Company Table to Staff Table
    By 3Khats in forum Database Design
    Replies: 3
    Last Post: 12-11-2012, 01:12 PM
  5. Replies: 1
    Last Post: 10-31-2012, 01:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums