Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2009
    Posts
    4

    Help with booking system

    Hi, I'm creating a booking system for a ski resort and I am struggling with the normalization. Any help would be really appreciated.

    Here's what I have so far:

    Table Customer
    Customer ID
    Booking ID
    Name
    Address
    Phone
    Email

    Table Booking
    Customer ID
    Booking ID
    Hotel ID
    Arrive
    Depart
    No of Adults
    No of Children
    No of Infants
    Services ID
    Total Cost

    Table Hotels
    HotelID
    Hotel Name
    No. of Stars
    Address
    Phone

    Table HotelRooms
    Room ID
    Hotel ID
    Room Type



    Table Extras

    Services ID
    Booking ID
    Ski Pass
    Ski Equipment
    Lessons
    Creche
    Total Cost



    This is my first attempt at designing a database so I know I am probably way off. My main concern is the Extras table. Do I need this table or could I include this data in another table? Also, should I include the room cost in the Hotel Rooms table or the booking table? The user needs to be able to search for available rooms; what data do I need to include for this?

    I'm sure I have made many more errors so any help would be much appreciated.

    Thanks.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Let's start here:

    Table Customer
    Customer ID
    Booking ID
    ...

    So, a Customer has one and only one Booking ID? What happens when they come back next year, do you create a new Customer record, or re-use the same Booking ID they had last year? Probably neither...

    You need to identify the key in each of your relations. If you decide that Customer ID is the key for Customer (which seems reasonable) then your normalisation is saying that Customer ID -> Booking ID, i.e. for each Customer ID there is exactly one Booking ID.

  3. #3
    Join Date
    Apr 2009
    Posts
    4
    Thanks for the reply.

    I want the customer to be able to make multiple bookings. What do I have to do to allow this?

    I would also like the user to be able to search for available rooms. What do I have include to allow this?

    Thanks for any help.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Your Booking table allows for this. Just remove the redundant BookingId from the Customer table.

    A search for avalaible rooms would be to select Rooms for which no Booking exists that overlaps the required range of dates.

  5. #5
    Join Date
    Apr 2009
    Posts
    4
    Oh I see. For the rooms I was going to add a field called Occupied or Booked Until; is this unnecessary?

    One more question: There are three different levels of ski passes - beginner, intermediate and advanced - and there can be more than one pass per booking. What would be the most efficient way of doing this? Would I create a separate table for Ski Passes or is there a way I can include this in the Extras table?

    Sorry for all the newbie questions but this is the final one. Thanks for the help.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    At the moment your Booking table books the hotel as a whole. You need a table that records a room booking, either as well or instead. A "Booked Until" attribute won't work: imagine I book a room now for 1-7 March 2010. You can't say the room is "booked until" 7th March 2010!

  7. #7
    Join Date
    Apr 2009
    Posts
    4
    So instead of the Hotel ID would I put in Room ID in the booking form? What about a field to say Occupied - Yes or No. Is it even needed or can I search for available rooms without that.

    What is the best way to allow a customer to book multiple ski passes and specify which levels they are?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •