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

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

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?

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

please don't start the same thread in more than one forum

i'll merge the two threads for you

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.
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

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?