Hotel Reservation Database - NeedComments

[arubikdoan]

Hi friends,

I am designing a hotel reservation system for practice after studying by
myself some first chapter in the book Sam Access 2002 21 Days. I have
decided the tables and relationship and hope that I can get experienced
advices for you.

############
Table Guest:

First Name
Middle Name
Last Name
Gender
Address
City
Country
Phone
Mail
Identification Type
Identification Number
Identification Country
ID City

##############
Table Agent

Agent ID
Agent Name
Contact Name
Address
City
Phone
Fax
Mail
Commission

###############
Reservation Table

Reservation ID
Guest ID
Agent ID
Reservation Date
Arrival Date
Departure Date
Service ID
Service Use Date
Room Number
Confirmation

###############
Table Service

Service ID
Service Name

################
Table Reservation-Service (junction Table)

Service ID
Reservation ID
Service Date
Service Cost

################
Table Room Categories

Category ID
Category Name
Number of beds
Bath Availability
Air-Conditioner
Picture

################
Table Rooms

Room Number
Category ID

################
Table Room Price

Category ID
Season
Duration
Room Price

################

Please tell me if there is any illogic.
Another two questions:
Can I calculate the payment for each reservation based on the above
table relationships? Do I need to have 2 tables for two types of
customers: one for those who have confirmed and one for those who not
yet confirmed?

Thanks in advance,


Arubik Doan

 

[tina]

-----Original Message-----

Another two questions:
Can I calculate the payment for each reservation based on the above
table relationships? Do I need to have 2 tables for two types of
customers: one for those who have confirmed and one for those who not
yet confirmed?

Thanks in advance,


Arubik Doan

Question 1: i don't think so. in your setup, each room can
belong to only one category (makes sense), and each
category can have several prices depending on season and
duration (also makes sense). but nothing in your
reservation table tracks the specific price for a room
based on the season and duration.

Queston 2: strongly urge you to NOT put customers in two
separate tables based on status. instead, you can query
the Confirmation field in tblReservatons to identify
customers by confirmed or unconfirmed status.

hth

 

[arubikdoan]

Great thanks Tina.

I have been thinkinbg re it since i were designing the table. At first,
I thought that i could caculate the day spent for the fields [Arrival
Date] and [Departure Date], and then multiple it with the days guest
used the room. I though I could also know the duration bases on the Arr
and Depart fields , daily, weekly, monthly, for example. Yet, it seems
that I got stuck. Can you suggest me any solution ?


Again, thanks.


Arubikdoan

 

[tina]

hi arubik.
i actually modeled your database (i usually have to "see"
something to figure it out!) if you would like to see the
model, you can email me and i'll send it to you.
to address your question below, i don't think i can offer
any suggestions without a clearer picture of your db
setup - hard to do in email or post.
sorry, and good luck.