I've been tasked to build an on-line hotel reservation system for a local resort. So far so good with the design of all necessary tables (MYSQL).
But...the room availability table has got me baffled!
Here's a breif overview of the scenario which is common to all major hotel reservation systems:
1) User clicks reservations link
2) user inputs check-in and check-out dates (to check what rooms are available during the dates input)
3) database is queried for all room categories AVAILABLE within the dates indicated
4) User is served another page showing which room categories are available during those dates.
Steps 1, 2, and 4 are no problem. But I can't figure out how to design a table that would hold all CURRENT reservations for each room in each room category.
Example: there are 9 seperate "standard" rooms in this hotel. Would this mean that I should create 9 seperate "standard room" tables with a month/day/year/room_number matrix and then check each of these 9 tables against the dates the user has input? Yuck!
Anyway, all the major hotels already have this done - and (no doubt) more efficiently/effectively than what I'm suggesting above - but I just feel like I'm getting into water that's way over my head. Not to mention that at my level of experience in db design (not much!), I don't want to re-invent the wheel.
Can anyone guide me in the right direction on how I should construct this table? Or sticky me a URL that has an example of how to accomplish this task?
I played around with some code for your problem, and here is what I suggest. If you have the following tables -
CREATE TABLE rooms (number INT, type VARCHAR(20))
CREATE TABLE reservations (reserved DATE, number INT)
Rooms contains all the room numbers and a room type for each room (eg. 'standard', 'deluxe')
Reservations contains all the dates any room is reserved and room number.
With the above tables you can do the following queries (some more useful toward your goal than others) -
// display all the room numbers and their type
$query = "SELECT * FROM rooms ORDER BY number";
// Show a count of each type of room
$query = "SELECT type, COUNT(*) FROM rooms GROUP BY type ORDER BY type";
// Show all reservation dates and room numbers
$query = "SELECT * FROM reservations ORDER BY reserved, number";
// Show the date, room number, and room type reserved for a date or range of dates
$start_date = "2006-04-06";
$end_date = "2006-04-07";
$query = "SELECT reserved, reservations.number, type FROM reservations, rooms WHERE reservations.number = rooms.number AND reserved>='$start_date' AND reserved<='$end_date' ORDER BY reserved, number";
// Show how many of each room type on a date or range of dates
$start_date = "2006-04-04";
$end_date = "2006-04-07";
$query = "SELECT reserved, type, COUNT(*) FROM reservations, rooms WHERE reservations.number = rooms.number AND reserved >='$start_date' AND reserved <='$end_date' GROUP BY reserved, type ORDER BY reserved, type";
A valid usage would be to use the results from the 2nd query listed (get a count of each type of room) and the last query (get a count of each type on a date or range of dates.) You can compare the numbers from the two results to tell you if there are any rooms available of each type on any date...
At first I disagreed with your design and thought that the reservations table should have a date range (after all, a "reservation" in real life has a check-in date and a check-out date.) But then I started thinking about how to use it and query it and realized that finding available rooms would be kind of ugly and cumbersome. Against intuition, it seemed like it would be simpler to use one entry per date.
Now I am back to my original idea. I feel like storing a record per day per reservation is a bad idea. Especially since hotel reservations are not "by day" in the sense that they do not go from midnight to midnight. Reservation check-ins usually occur at 3 or 4 in the afternoon and check-outs usually are around noon. So you can have two separate people make reservations for the same room in one day (one checking in, one checking out.) It doesn't seem like your design could handle that.
room_id NOT IN
[$my_date_range INTERSECT date_range(checkin_datetime, checkout_datetime]
And there is your ugly SQL.
I'm sure there is a better way to do this, but this seems like the most intuitive way (although intuition is not always best.) Maybe a combination of this and dbmab's suggestion? Or create a calendar lookup table? I'm not sure. The check-in/check-out rules seem to make it a bit harder because you can't just go by day. There also has to be lots of business logic that we don't know about that will affect the database design.
Is the whole check-in/check-out really that complicated??? A request check-in date should be equal to or greater than a booked check-out date. A request check-out date should be less than or equal to a booked check-in date.
OK, I have no idea what I've just written. It's been a terribly, terribly long day and I think my brain is mush. I'll go have a few beers and come back to this when my head is in the right place.
Sorry if you read all of my posts . I swear, my next ones will be better.
First of all, you can do this in a single SQL, but it ain't necessarily that easy.....
I wrote a room booking system using Mysql and have faced the same challenges you mention. You will definately need the following:
1) table of room definitions
2) table of room reservations with start and end datetime fields
jfulton is on the money with his SELECT rooms.* FROM rooms WHERE room_id NOT IN (existing reservations for required time period)
Getting the right SQL for the (existing reservation for required time period) is quite tricky because you have a start and end date for your enquiry (say @enq_start and @enq_end) and each existing reservation will have a start and end date (say dt_start and dt_end).
I decided to make a comparison of each of the existing reservations to my desired enquiry dates. To simplify this I took the three possible cases for the start enquiry date compared to the existing reservation dates which are:
a) @enq_start < dt_start
b) @enq_start = dt_start
c) @enq_start > dt_start
For each of these there are a combination of dt_end and @enq_end conditions that will determine if your enquiry dates overlap at all with existing reservations.
So for every record in my reservations table I made a SQL comparison against conditions a, b and c. I can't remember exactly the SQL, but it was something like the following:
SELECT room_id, room_type FROM rooms WHERE room_id NOT IN (
SELECT room_id id FROM reservations r WHERE
@enq_start = r.dt_start
@enq_start < dt_start AND
@enq_end > dt_start
@enq_start > dt_start AND
@enq_start < dt_end
If the above does not make logical sense, then let me know and I will try and find the actual SQL I used.
Now there are two other things I came across with room booking that caused a real problem and you should consider up front if you need them:
1) Do you need to have joined rooms e.g. two rooms that can be joined for a single booking? e.g. suppose we have rooms A and B that can be joined to create a suite C. Now we have a much more complicated requirement to return the list of (existing reservations for required time period) because we must take into account "parent" and "child" rooms. e.g. if A is booked then you should return A and C as existing reservations because C cannot be booked as A is one of its components (children). Equally if C is booked then neither A nor B can be booked. My advice on this is to allow multiple room bookings against each reservation rather than try to do complex joins on your SQL for the simple reason that it gets VERY SLOW even with a moderate number of rooms and existing reservations. One way of tackling this is to have a single record for each reservation and multiple room bookings against these e.g. reservation 1234 might have rooms A, B and C booked because the client wants room C. This way you do a simple query on the room booking table and return A,B and C. What I did was to include an additional field to indicate whether the booking was "explicit" (the room the customer asked for) or "implicit" (implied booking because a component or required room). This allowed the reservation to display just the required room, but to take into account the components required to prevent double bookings.
2) Mine was a large multi-user system so it was necessary to prevent different users from trying to reserve the same room for the same time. I did this by having two tables - a "temp" reservation and a "confirmed" reservation table. A reservation gets written to the temp table until the user is satisfied with the choices made at which point it gets written to the confirmed table. The temp table is also used to make amendments to the confirmed bookings i.e. the amendment is made within the environment of the temp table until the user is satisfied with the changes. Of course the temp booking can be abandoned at any time. As such the SQL I outlined above also needs to take into account NOT ONLY confirmed bookings, but also reservations that people might be making right now. Of course you can do the same SQL on both temp and conf tables and union the two results to give you the list of (existing reservations for required time period) and there are other ways too (probably smarter than mine!).