Hotel Reservation Site

[Fiorentino01^]

Hi I am building this website for a small hotel. The user interface is ready and I have a form as shown below: If I click on submit I can insert the data from the form into the database.My question is where do I go from here? Is my database design right? Besides inserting the form data I should select all unoccupied rooms, how do I do that? Here is what I have so far.
TABLES

bookings:

id int11 auto increment primary key
arrival date
departure date
adults int3
children int3

customers:

id int11 auto increment primary key
first_name varchar30
last_name varchar 30
address varchar100\phone int11
city varchar30
country varchar30

rooms:

roomID int11 auto increment primary key
location varchar20
room_number int11
room_typeID int11

room_types:

room_typeID int11 auto increment primary key
room_type vatchar20
num_beds int11
price_night int20

<form name="form1" method="post" action="process.php"> <table border="1" cellspacing="5" cellpadding="4"> <tr><td colspan="2" class="txt"><h2>Book Now</h2></td></tr> <tr><td colspan="2"> </td></tr> <tr><td class="txt">Arrival:</td><td><input type="text" name="arrival" size="15"></td></tr> <tr><td class="txt">Departure:</td><td><input type="text" name="departure" size="15"></td></tr> <tr><td class="txt">Adults:</td><td> <select id="adults" name="adults"> <option <?php if ($adults == 1 ) echo 'selected'; ?> value="1">1</option> <option <?php if ($adults == 2 ) echo 'selected'; ?> value="2">2</option> <option <?php if ($adults == 3 ) echo 'selected'; ?> value="3">3</option> <option <?php if ($adults == 4 ) echo 'selected'; ?> value="4">4</option> </select></td></tr> <tr> <td class="txt">Children:</td><td> <select id="children" name="children"> <option <?php if ($children == 0 ) echo 'selected'; ?> value="0">0</option> <option <?php if ($children == 1 ) echo 'selected'; ?> value="1">1</option> <option <?php if ($children == 2 ) echo 'selected'; ?> value="2">2</option> <option <?php if ($children == 3 ) echo 'selected'; ?> value="3">3</option> <option <?php if ($children == 4 ) echo 'selected'; ?> value="4">4</option> </select></td></tr> <tr><td colspan="2"> </td></tr> <tr><td> </td><td><input type="submit" name="submit"  value="Submit"></td></tr> </table> 

[SalmiSoft]

Your bookings record tells you who the booking is for but doesn't tell you what was booked. You need to have another table to cross-reference each booking with the rooms booked. Personally I would put the booking dates into that cross-reference table, so that you can more easily cope with the odd booking which is for 2 rooms on 2 nights and 3 rooms on the third night.

To select unoccupied rooms you could then select all rooms from the room table which have no record in this new cross-reference table for the date(s) of interest.

Why do you need roomID? Each room already has a room number (or possibly room name) which uniquely identifies it, so why do you also need a room ID?

Is an integer the best choice of data type for a phone number? There are 2 potential problems. First, how do you know if a number requires a leading zero, or multiple leading zeroes? (How do you get an international line from that location? Form here it would be prefixed with 00). Second, what if the phone number is given as Nowhereville 30601? You might need alpha characters.

You have a fixed price for each room. How are you going to cope with price rises? How will you know what the price was at the time the booking was made? How will you cope with different prices for off-season or peak? Discounts for regular customers or companies?

A friend of mine did a hotel booking system many years ago. It was rejected by his customer because it would only sell each room once per night. Apparently it is common practise to take more bookings than there are rooms on the assumption that some proportion of people just won't turn up. Check if you need to cope with that scenario and consider how to do it.