Reservation Database Relationships? 1

[bugmenot1]

I am trying to model a hotel reservation database for a single hotel. The system will allow the user to make a reservation and bill the customer based on room rates and facilities. The guests can optionally chose for a variet of pre-priced packages. My problem is figuring out the tables needed and the subsequent relational schema.Here are my tables so far.

Room
Room_ID
Room_Num
Room_Type
Room_Rate

Reservation
Reservation_ID
Guest_ID
Room_ID
Room_Num
Date_In
Date_Out

Guests
Guest_Id
Guest_fname
Guest_lname
Guest_Address

Bill
Guest_ID
Reservation_ID
Subtotal
Payment_Method

Facilty
Facility_ID
Facility_Type

Package
Package_ID
Package_Type

Agent
Agent_ID
Agent_Fname
Agent_Lname

 

[Golom]

Looks like a good start.

The thing I would question is the "Room_Rate" field in the "Room" table. Presumably the room rate is determined by the package (if any) that they purchase and is not a fixed amount associated with the room. Perhaps you need a "RoomRates" table of the form
[tt]
RoomRates
Package_ID (PK)
Room_Type (PK)
Room_Rate
[/tt]

Another anomoly is "Room_Num" in the "Reservation" table. Presumably you can find out the room number by joining to the "Rooms" table using the "Room_ID" field so why do you need a separate room number here?

I don't see any obvious relationships between the "Facility" and "Agent" tables and the other tables in the system.

There's obviously no information about how you compute the "SubTotal" on the customer's bill but you may need more tables to hold things like room service or mini-bar charges.

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[bugmenot1]

Thanks I Was considering a Rates table that holds the rates for rooms by room type such as King,Queen and Double.
I was thinking of linking Facility to pakage and Agent to reservation.
Any suggestions on how I should go about calculating the bill?

 

[Golom]

As a first approximation
[tt]
(RoomRate * Number of Days) + SUM(OtherCharges) + Taxes
[/tt]
Where "OtherCharges" would come from the table that I suggested and would be linked to a specific "ReservationID".

These things are however, rather specific to your environment ... particularly taxes.

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[bugmenot1]

Thanks again, Any Idea how I can get the facility and Agent Table linked in the schema

 

[Golom]

Not really because I don't know what a "facility" or an "agent" have to do with anything else. That's a business issue and not a technical one.

You need to be able to make simple statements about how a "facility" (for example) interacts with something else in the system (a room perhaps?) From that you can then formulate relationships between "facility" and other entities in the system.

What prompted you to create "facility" and "agent" entities if there was no clear notion of what they had to do with rooms, guests, reservations, packages, etc.?

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bugmenot1]

Ok, a facility is an optional service provided at the hotel such as the use of a the steam room or mini bar I decided to give each of these a code and an additional price to add to the bill the agent is the clerk that makes the actual reservation this could be useful in terms of commission.Or should I leave it out?

 

[Golom]

OK. I would keep the facility table and create another table
[tt]
FacilityCharges
ReservationID (PK)
ChargeDateTime (PK)
FacilityID (FK)
ChargeAmount
[/tt]
That records a guest's usage of facilities. I would record the amount charged directly here because most "facility" charges (drinks in the bar for example) will have differing amounts and not a flat rate.

I would add an AgentID foreign key to the Reservaton table as a foreign key to capture the agent that made the reservation. For that to be useful you will probably need another table that delineates the commission structure.

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[bugmenot1]

Since this is complicated enough I think I'll omit the commission details but keep the agent table for reference purposes. Now I should have mentioned that choosing a package is optional how will I effect my rate and room tables?

 

[Golom]

Now I should have mentioned that choosing a package is optional how will I effect my rate and room tables?

I don't understand the question. What's this about "... choosing a package ..."? If you are looking at purchasing some pre-packaged hotel reservation system then it will already have its own database and associated schema.

... and what does "... effect my rate and room tables ..." mean?

Are you asking how to create the tables? How to populate them with data? How to pull data from them for reporting?

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[bugmenot1]

Bear with me if I have a room_num does this nullify the need for a room_id field?if not which one do I include in the reservation table?

 

[Golom]

If you are ABSOLUTELY CERTAIN that room numbers will always be unique and will always be known then you can probably eliminate RoomID and just use RoomNumber.

Personally I would retain the RoomID and have RoomNumber as an attribute just because the reservation system does not control the use or issuance of room numbers. It does however, control the assigning of RoomID since that's purely internal to the system.

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[bugmenot1]

No, the hotel offers a set predefined and priced packages available to the guest where facilities and nights are already included at special prices the guest can choose from this range of packages or pay separately for them. Since the package already include the room and rate will this affect the layout? Here is hat I have?

Agent
Agent_Fname
Agent_Lname

Rooms
Room_Num
Room_Type

Room_Rates
Room_Num
Room_Rates
Room_Type

Reservations
Reservation_Id
Guest_Id
Agent_Id
Room_Num
Package_Id
Arrival
Checkout

Package
Package_Id
Package_Type

Guests
Guest_Id
GuestFName
GuestLName
Guest_Address

Facility Charges
Reservation_Id
ChargeDate
Facility_Id
Charge_Amt

Bill
Bill_Id
Guest_Id
Reservation_Id
Subtotal
Finaltotal
Payment Method
Expiration Date

 

[Golom]

Given that scenario, your Package information obviously needs a lot more work ... probably in the form of at least a couple of additional tables. One of them will probably specify "Room_Type, StayDuration, PackageRate" and the other will specify included facilities and rates.

You also need to look at the "Guest" table to account for multiple guests (Mom, Dad and the Kids) using a room or package. Presumably charge rates are a function of the number of guests using the room and/or package and I don't see any place to capture that.

The "Bill" table does not account for multiple tenders and it is not uncommon in hotels catering to business travelers that they will pay for some charges on their personal credit card but charge others to a company card. Also "Guest_ID" is redundant in the Bill table because "Reservation_ID" already links to a specific guest.

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[bugmenot1]

Thanks, you've helped a bundle still in a weird place right now with my tables but I wont bug you anymore and i know i still have a whole lot of work to do. Last question when i'm finished sorting this out will i still need to normalize?

 

[Golom]

If you do this correctly the result should be a normalized system. "Normalization" isn't an add-on ... its a built-in.

Look at the link that PHV provided above for a discussion of normalization (among a great many other things.)

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]