Results 1 to 7 of 7
  1. #1
    Join Date
    May 2005
    Posts
    13

    Reservations Design Issues

    I am supposed to create a hotel reservation system that allows you to make a reservation either with a hotel package or without and calculate billing. However I am at lost for where to begin. I have made a couple of sorry attempts but I am having problems identifyind the tables that are needed and getting them related. Since this is supposed to be generic, general design suggestion would be greatly appreciated heres what i have come up with so far.I think I have the basic concept but wil I be ale to query for available room easily which this approach?

    Agent
    Agent_Id Pk
    Agent_Fname
    Agent_Lname

    Rooms
    Room_Id Pk
    Room_Num
    Room_Type

    Room_Rates
    Room_Num Pk
    Room_Rates Pk
    Room_Type

    Reservations
    Reservation_Id Pk
    Guest_Id
    Agent_Id
    Room_Num
    Package_Id
    Arrival
    Checkout

    Package
    Package_Id Pk
    Package_Type

    Guests
    Guest_Id Pk
    GuestFName
    GuestLName
    Guest_Address

    Facility Charges
    Reservation_Id Pk
    ChargeDate Pk
    Facility_Id
    Charge_Amt

    Bill
    Bill_Id Pk
    Guest_Id
    Reservation_Id
    Subtotal
    Finaltotal
    Payment Method
    Expiration Date
    Last edited by pico; 04-06-06 at 16:35.

  2. #2
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Is this coursework ?

    There is a forum at this site that is probably better suited for discussing conceptual models. However….

    If you ask the members of this forum to answer this problem you will probably get several different solutions so it’s difficult for me to suggest the de facto solution. I will highlight a couple of things to think about and you can decide whether they are relevant or not and how to incorporate them.

    As your model stands, I can create multiple reservations for different people in the same room at the same time. That’s good for the hotel manager as they get good utilisation but the guests might not be happy sharing a room with several strangers (maybe they will be happy ). So your PK for reservation needs to be such that only one guest can book a room at any one time. Also, you’ve used room_num as your FK in reservation. I think you mean Room_ID (same in other tables).

    What’s the purpose of Bill ? Think of it this way, suppose Bill is not there, how would you go about generating the information to produce a bill. I suspect you can get most of it without Bill using some queries (although I haven’t personally thought it through). You should not store calculated fields i.e. subtotal, finaltotal – you should be able to calculate these when needed. I’m not suggesting you shouldn’t have a bill table but just think again what you need to record in it that isn’t either recorded elsewhere or can’t be recorded elsewhere. If you do have a bill table, what is the relationship between bill and reservation – how many bills are associated with one reservation ? See my point ?

    Where do you get your room charges from ? Obviously you have a table called Room_Rates, however, this currently isn’t related to anything so isn’t being used. You could choose to relate it to reservation but you might want to consider how you manage rate changes. There different views about how best to do this.

    hth
    Chris

  3. #3
    Join Date
    May 2005
    Posts
    13
    thanks alot i was wondering was wondering if if should use the room_id or the the room_num in in the reservation table and the bill table was getting a little confused. Those are some great suggestions. My questions lies with the room availibility should thisbe done in a query or would it work better with a table?

  4. #4
    Join Date
    May 2005
    Posts
    13

    Reservation Design Issues

    I am supposed to create a hotel reservation system that allows you to make a reservation either with a hotel package or without and calculate billing. However I am at lost for where to begin. I have made a couple of sorry attempts but I am having problems identifyind the tables that are needed and getting them related. Since this is supposed to be generic, general design suggestion would be greatly appreciated heres what i have come up with so far.I think I have the basic concept but wil I be ale to query for available room easily which this approach?

    Agent
    Agent_Id Pk
    Agent_Fname
    Agent_Lname

    Rooms
    Room_Id Pk
    Room_Num
    Room_Type

    Room_Rates
    Room_Num Pk
    Room_Rates Pk
    Room_Type

    Reservations
    Reservation_Id Pk
    Guest_Id
    Agent_Id
    Room_Num
    Package_Id
    Arrival
    Checkout

    Package
    Package_Id Pk
    Package_Type

    Guests
    Guest_Id Pk
    GuestFName
    GuestLName
    Guest_Address

    Facility Charges
    Reservation_Id Pk
    ChargeDate Pk
    Facility_Id
    Charge_Amt

    Bill
    Bill_Id Pk
    Guest_Id
    Reservation_Id
    Subtotal
    Finaltotal
    Payment Method
    Expiration Date

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please don't start the same thread in more than one forum

    i'll merge the two threads for you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by pico
    thanks alot i was wondering was wondering if if should use the room_id or the the room_num in in the reservation table and the bill table was getting a little confused.
    You need to use an attribute that will uniquely define the room. If room_num is sufficient to do this then it might be a good candidate. Whatever you decide to use as the unique identifier (PK) must then be the reference that you use in other tables. Your model seems to suggest you are only interested in booking rooms at one hotel, in which case room_num seems reasonable. Some folks would argue that you should use an independant referencing system instead e.g. Room_ID, in case someone decides to extend the hotel and renumber all the rooms (room 31 then becomes the single room rather than the double suite!!). If this is just coursework then I wouldn't worry about this (imho). If you're d/b is to book rooms at more than one hotel then you need to do some more thinking.
    Quote Originally Posted by pico
    Those are some great suggestions. My questions lies with the room availibility should thisbe done in a query or would it work better with a table?
    In theory you should be able to determine what dates rooms are booked for using data from the reservation table. So a query should be used to check availability dates.
    hth
    Chris

  7. #7
    Join Date
    May 2005
    Posts
    13
    Upon entering some sample information I asked my self What if a family wants to book multiple rooms? How will I represent this information will I have one guest_id that is associated with multiple reservations?

Posting Permissions

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