Preventing Hotel double booking

[jonathan.paul.7543]

Hi,
I am working on Hotel Application and I need your help on how to prevent booking a room twice. That is, if a room is booked for 3 days(24/2/2015(18.00) to 27/2/2015(7.00)), no room can be booked withing that period. Am developing the project with Asp.Net(VB) ans Sql Database.
Thanks in advance.

[Minimalist]

You are probably better off moving the question to asp web development in Dani

[Santanu.Das]

You can use a flag for Booking/Non-booking, like True/False. Make your querry on that flag.

[Mr.M]

Or retrieve the dates and match with the current dates to see if the booked room is available or not.

[Nutster]

You will need a table with your bookings.

Room_Number, Customer, Booking_Start, Booking_End. Primary key is Room_Number + Booking_Start. Unique key is Room_Number + Booking_End. Foreign keys are Room_Number (to table Rooms), Customer (to table Customers).

To check if a room has already been booked for a new booking do a SELECT against the Bookings table, looking for overlaps in the date range and hope for no records returned. This code assumes you have already validated that the start dates/times are before the ending dates/times, both in the database table and the new query.

SELECT Customers.Name, Booking_Start, Booking_End
FROM Bookings INNER JOIN Customers ON Bookings.Customer = Customers.Customer_Number
WHERE Bookings.Room_Number = [New Booking Room]
  AND (Bookings.Booking_Start <= [Start New Booking]
  AND Bookings.Booking_End >= [Start New Booking])
   OR (Bookings.Booking_Start <= [End New Booking]
  AND Booking.Booking_End >= [End New Booking])

[jonathan.paul.7543]

@Nutster,
I want a situation where if guest is expected to check out by 10.00am, another guest can be booked for 11.00am that same day.
How do I deal with that please?

[Mr.M]

If you have already verified the date then you will also need to verify the times as well so for instance suppose you also have a field to hold the time a current guest will leave the apartment then you can check the time and then if you wish to give certain time maybe 30 min or 1 hour for room cleaning the you can determine this by retrieving the time of the current guest checkout then from that time add up the desired time needed for cleaning then that will give you the exactly time the room will be available then you can verify the time the second guest is booking the room if its equals or greater then the room availability time at that day of first guest checkout.

[David_50]

You are talking about keeping the book, just like a stock exchange or a hotel clerk. The book is a durable database of rooms and for each room, an ordered list of times booked. The book needs to be cleaned periodically to remove/archive expired data. The book needs locks so only one user can update a room at a time. If you organize time into blocks, like hours for the next 2-3 months, you can lock hour by hour, or just lock by room, or you can simply lock the whole for every update, or use a single updating thread that reads an ordered, multiple access message queue. How much churn bandwidth do you need? How much query bandwidth? You might need to segment the database for high activity levels, to multiply the max bandwidth using separate resources for each segment. First, tune up your requirements.

[Nutster]

Date fields also include the times. So a few records might look like:

Room_Number, Customer, Booking_Start, Booking_End
102, 1095, #April 4, 2015 16:00#, #April 7, 2015 10:00#
105, 27, #April 6, 2015 21:00#, #April 7, 2015 10:00#
102, 1022, #April 7, 2015 11:00#, #April 8, 2015 10:00#

The Customer number maps back to the Customer table, which stores all the information about a customer, like name, address, phone number, etc. Using the SQL statement above, you could check if there is a booking for the desired date/time already.

As you go to create a new booking, run that query to see if the time is already booked. If you get no records returned by the query, you can go ahead and record the booking. If you do get some records back, you can go ahead and record the booking.

Another thing you could do is search for rooms available in the given time frame.

SELECT Rooms.Room_Number
FROM Rooms LEFT JOIN Bookings ON Rooms.Room_Number = Bookings.Room_Number
WHERE Bookings.Room_Number IS NULL
  AND (Bookings.Booking_Start <= [Start New Booking]
  AND Bookings.Booking_End >= [Start New Booking])
   OR (Bookings.Booking_Start <= [End New Booking]
  AND Booking.Booking_End >= [End New Booking])
ORDER BY Rooms.Room_Number

I just realized that you were asking for SQL Server and I was answering for Access. You will need to change the parameter definitions. I am not sure ASP.Net would work with that.

[Nutster]

I just noticed a bug in what I told you. I will get back to you shortly after I test it on my copy of Access.

[jonathan.paul.7543]

Ok

[Nutster]

Okay, I found my mistakes. Here we go.
First, the table structure is as I described above, but the queries needed some help.
First, let's see what rooms are being used during a given time. In other words, given a time frame, which rooms are booked for anytime in that time? So I calling this Booked_Rooms:

SELECT Customer_List.Customer_Name, Bookings.Room_Number, Bookings.Booking_Start, Bookings.Booking_End
FROM Customer_List INNER JOIN Bookings ON Customer_List.Customer_Number=Bookings.Customer
WHERE Bookings.Booking_Start BETWEEN [Start Booking] AND [End Booking] OR Bookings.Booking_End BETWEEN [Start Booking] AND [End Booking]
ORDER BY Room_Number, Booking_Start, Customer_Name;

Next, I wanted to see which rooms were available, so I looked for rooms that weren't booked, using an outer join. I called this query Available_Rooms

SELECT DISTINCT Rooms.Room_Number, [Booked_Rooms].Room_Number
FROM Rooms LEFT JOIN Booked_Rooms ON Rooms.Room_Number=[Booked_Rooms].Room_Number
WHERE [Booked_Rooms].Room_Number Is Null
ORDER BY Rooms.Room_Number;

The last query was asking, is this room available? I called this Room_Check. If this returns any records, the time and room you are looking at are already used. In order to make a new booking for the room, you need to get no records returned.

SELECT Customer_List.Customer_Name, Bookings.Room_Number, Bookings.Booking_Start, Bookings.Booking_End
FROM Customer_List INNER JOIN Bookings ON Customer_List.Customer_Number = Bookings.Customer
WHERE Bookings.Room_Number=[New Booking Room]
  AND (Bookings.Booking_Start BETWEEN [Start Booking] AND [End Booking] OR Bookings.Booking_End BETWEEN [Start Booking] AND [End Booking])
ORDER BY Booking_Start;

[Mr.M]

@Paul seem straight forward, but I didn't see where after the end time has been reached where you remove the booking from the database to support your statement "In order to make new room booking you need to get no records returned"

Just asking because I didn't see you mention the removal of expired booking so that there will be no record returned for that particular room.

[jonathan.paul.7543]

There's a field called Status, once the booking expire, the status will change from ACTIVE to INACTIVE.
My select statement condition will include "Where Status='Active'"

[jonathan.paul.7543]

@MR.M,
I try the above and it worked but there's only one problem.
The search range is adding one day. for instance, instead of searching from 07/03/2015 to 10/3/2015, it will search from 07/03/2015 to 11/3/2015. What could the problem be?
Thanks!

[Mr.M]

Please quote the "For" statement or codes that searches for dates, so that we will check why this is happening.

[David_50]

The quicker query is where . . . testing_start <= book_end and testing_end >= book_start. This covers bookings that are within, without, overlapping start, overlapping end and identical. Since end is always > start, this takes care of it all. No between, simpler, better for index use (range scan).

If you insert your booking with such a where, the atomic nature of insert precludes the need for a transaction. If the booking is tenative, you might want to have an indexed flag or expire date, so a periodic scan can remove them even if the booking is abandoned in the middle. The same scan might archive/delete/move history, so the next exporations are at the nose of the index, making the check very cheap and fast.

[jonathan.paul.7543]

Here is the code I tried to use:

Dim Dt As DateTime = DtIn.Text '2015/03/16 18:00
Dim Dt2 As DateTime = DtOut.Text 2015/03/18 10:00

Dim cmd As New SqlCommand("Select RoomNum from tblreserve Where ReservationId = '1722015005' And CheckinDate Between '" & DT & "' And '" & DT2 & "' Or CheckoutDate Between '" & DT & "' And '" & DT2 & "'", con)

The problem is that if I search from 2015/03/19 10:00, it will exist. It will not only exist from 2015/03/20 10:00, 2015/03/21 10:00...........
I've tried converting the date to same format used by sql, I tried using other sql statements I know but I still have same result.
What can I do please?

[David_50]

If you have a reservation id, why do you need any other keys?

[Mr.M]

I'm a bit confused with your code, 2015/03/16, 2015/03/18, 2015/03/20, 2015/03/21.

Also let's say in your text box for start booking is (2015/03/16) and end date is (2015/03/16) are you saying it will give you 2015/03/17 ?

Please try en-lighting us there.

[Mr.M]

Meant the start day is 2015/03/15 and end date is 2015/03/16 will it give you 2015/03/17?

[jonathan.paul.7543]

DateIn= 2015/03/16 18:00
DateOut=2015/03/18 10:00

Using the previous code, if I search from 2015/03/19 and above, no record should be found. But record is usually found. Record will not only be found if I search from 2015/03/20 upward.

[David_50]

Consider a proposed short booking sliding down the schedule (forward in time) across a prior booking. The collison begins when the end date-time of the proposed exceeds the start date-time of the prior, and continues as the proposed is slid forward in time until the start date-time of the proposed equals or exceeds the end date-time of the prior.