I am currently developing a hotel booking system for my University final year project and am having some serious problems with my query to calculate room availability.

I felt the best method to calculate room availability was to first calculate which rooms were already booked for any specific queried dates and then to subtract those results from the list of total rooms. That would then return which rooms were available on those dates.

However the query that I am using to calculate which rooms are already booked is very inefficient, in that, for example,

A booking which is for more than one night can overlap the date you are testing for availability. It's even worse when you check the availability of rooms for multiple nights. My query does not cover those scenarios.

If anyone can help me solve this problem or suggest alternative methods I would be most appreciative.

Below is the MySQL code for the relevant parts of my database, and the query that I am currently using.

Thanks!


CREATE TABLE booking
(
booking_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
e_mail VARCHAR(40) NOT NULL REFERENCES guestdetails(e_mail),
arrival_date DATE NOT NULL,
departure_date DATE NOT NULL,

PRIMARY KEY(booking_id)
);

CREATE TABLE roombooked
(
booking_id INTEGER UNSIGNED NOT NULL REFERENCES booking(booking_id),
room_id INTEGER UNSIGNED NOT NULL REFERENCES rooms(room_no),
no_of_nights INTEGER UNSIGNED NOT NULL,

PRIMARY KEY(booking_id,room_id)
);

CREATE TABLE rooms
(
room_no INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
room_name VARCHAR(11),
room_type VARCHAR(9),
single_price DECIMAL(5,2),
double_price DECIMAL(5,2),

PRIMARY KEY(room_no)
);

insert into rooms (room_name,room_type,single_price,double_price)
values ('shakespeare','principal','165','225'),
('keats','principal','165','225'),
('kipling','standard','125','165'),
('tennyson','superior','135','185'),
('shelley','deluxe','155','205'),
('brooke','superior','135','185'),
('wordsworth','deluxe','155','205'),
('milton','deluxe','155','205'),
('masefield','deluxe','155','205'),
('browning','deluxe','155','205');


///ROOM AVAILABILITY QUERY///


CREATE TEMPORARY TABLE roomsoccupied
SELECT roombooked.room_id FROM roombooked, booking
WHERE
roombooked.booking_id = booking.booking_id
AND
booking.arrival_date <= 'QueriedArrivalDate'
AND
booking.departure_date >= 'QueriedDepartureDate';