Can you help me to normalize Online Hotel Reservation System. In the database I will store information about the hotels (name, city, country, number of stars, room prices, total number of double rooms, total number of single rooms, number of available rooms, etc); customers (name, email, telephone, address, username, password, etc); reservations (username of the customer who made the reservation, Hotel ID, date of arrival, date of departure, total cost, and etc.).

My Idea:
############
Table Customer:
CustomerID
First Name
Last Name
Gender
Address
Phone
Mail


##############
Table Hotels
HotelID
Name
Address
City
Phone
Number of Stars

###############
Reservation Table
ReservationID
CustomerID
HotelID
Reservation Date
Arrival Date
Departure Date
RoomCategoryID
Status
TotalCost

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

Table Room Categories
Category ID
Category Name
Number of beds


################
Table Rooms
HotelID
Room Number
Category ID

################
Table Room Price
HotelID
Category ID
Season
Duration
Room Price
################

The normalization is okay at this point.

Is this going to be a live system, or just a test ? Because if you want to book rooms, you'll need to know what rooms are available. Suppose a room cannot be sold due to maintenance.

i have made a hotel database using microsoft access. its only basic, such as booking rooms and sees which rooms are avaliable using simple sql. you can download it and the documentation from my website http://www.e-male.me.uk/avce_unit6.html

i hope that helps you a little!

Hi,
You need to break your reservations table into 'main' & 'details': what if a family books 2 rooms? What if they change rooms during their stay? You want it to show up as one reservation, with it's various details.
Also, you have number of beds, but not number of guests...
Have fun.

Hi

I think you would have a problem with this table because it don't refer to the customer

you must make a link between the two tables

regards