Reservation Database Relationships

[kellya]

I am currently trying to design a reservation database for a hotel that will
make a reservation and calculate billing based on facilities and package
types I am having a bit of trouble identifying which tables are needed and
the relational schema. I have attached the tables I currently have.
Also How will I go about quering the database for the that rooms available?
Will I need to incorporate a Calender Contol on my switchboard?

 

[TC]

Have you searched? - I get 370 hits:
http://groups.google.com/groups/search?q=reservation+database+group:*access*&qt_s=Search

The critical thing is to get the table structures correct. You need to
do that *before* you even *think* of writing any forms or reports. This
will be a fairly hard task, if you have not done anything like it
before. Putting a calendar control on the switchboard form, would only
be a tiny percentage of the total work required.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[kellya via AccessMonster.com]

Have you searched? - I get 370 hits:
http://groups.google.com/groups/search?q=reservation+database+group:*access*&qt_s=Search

The critical thing is to get the table structures correct. You need to
do that *before* you even *think* of writing any forms or reports. This
will be a fairly hard task, if you have not done anything like it
before. Putting a calendar control on the switchboard form, would only
be a tiny percentage of the total work required.

HTH,
TC (MVP Access)
http://tc2.atspace.com

Thanks My table structures are as follows:
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

My problem now is getting them related if you see any errors please respond

 

[BruceM]

If you add hotel+ in front of "reservation" in that search link you will
narrow it down to about 200. Some of those links look pretty helpful.

 

[TC]

Thanks My table structures are as follows:

It's very important to state the primary key field(s) of each table. It
is not possible to say whether a table design is right or wrong,
without knowing the primary key field(s) for that table. Some choices
are right - other choices put a competely different perspectivbe on
whether the table will work, or not.

Be that as it may ...

Couldn't the same room have different rates at different times and/or
in different circumstances? If so, the rate should not be stored in the
next table. With this design, a given room can only have one rate.

Room
Room_ID < PK ?
Room_Num
Room_Type
Room_Rate

Tell me you primary key for the next table. Regardless of that, you
should not be duplicating Room_Num in this table. You already have the
Room_ID; the Room_Num comes from the Room table.

Reservation
Reservation_ID
Guest_ID
Room_ID
Room_Num
Date_In
Date_Out

Guests
Guest_Id < PK
Guest_fname
Guest_lname
Guest_Address

I can't comment on the remaining tables without knowing more about your
information.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[TC]

kellya via AccessMonster.com wrote:

Furthermore, the next table implies a single guest per reservation. Is
that correct? Or could a single reservation (with a single
Reservation_ID) include /several/ guests? If so, you need a seperate
table to define the guests for each reservation.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[kellya via AccessMonster.com]

Furthermore, the next table implies a single guest per reservation. Is
that correct? Or could a single reservation (with a single
Reservation_ID) include /several/ guests? If so, you need a seperate
table to define the guests for each reservation.

Reservation
Reservation_ID [quoted text clipped - 3 lines]
Date_In
Date_Out

HTH,
TC (MVP Access)
http://tc2.atspace.com

Sorry heres an update. I always assumed that it woulds always be one
reservation per guest if mulitiple rooms are needed then the reservaton will
still go under one name. Is that a correct assumption?

Agent PK
Agent_Fname
Agent_Lname

Rooms
Room_Id PK
Room_Num Pk
Room_Type

Room_Rates
Room_Num PK
Room_Rates
Room_Type

Reservations
Reservation_Id PK
Guest_Id
Agent_Id
Room_Id
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

 

[TC]

I always assumed that it woulds always be one reservation per
guest if mulitiple rooms are needed then the reservaton will
still go under one name. Is that a correct assumption?

Kelly, how would I possibly know that?

You need to discuss such questions with the people who you are
designing the database for.

HTH,
TC (MVP Access)
http://tc.atspace.com

 

[kellya via AccessMonster.com]

Kelly, how would I possibly know that?

You need to discuss such questions with the people who you are
designing the database for.

HTH,
TC (MVP Access)
http://tc.atspace.com

Well I thought the concept might have been done before and the system isn't
exactly real its for a demo I am supposed to present I was just wondering if
I was going about it the right way. Newayz, thanks for all your'e help.