Prevent Overlapping Bookings

[music_al]

Hi

I am building a database in which to record hotel room bookings. I need to ensure that a room cant be double booked but I need more than a multi-field primary key for 'date' and 'room_number' as I need to ensure that if a room is booked on the 1st January for 7 nights that it cannot be booked again until the 8th January.

Does anyone have a solution for this ?

Thanks in advance.

Al

 

[spikepl]

http://www.baldyweb.com/OverLap.htm

 

[Lightwave]

So Spike just to be clear would that SQL go in the validation of the field or would it be better to run the query in a before update event and do an if then statement to produce a message box indicating to the user that that the entry was not valid?

 

[gemma-the-husky]

I am not sure.

I think perhaps a booking might need a room, and a start and end date.

Then to check a new bookings's eligibility you need to do this check.

a) find any booking with an end date between your proposed booking dates
b) find any booking with a start date between your proposed booking dates
c) find any booking with an start date before your proposed booking date AND
an end date after your proposed booking date

if all of these are false (return 0 records) then you can accept the booking.


----
alternatively, record each days booking as a separate record (even for multi-day) booking, when you can check the booking's table more easily.

again, i am not sure what hotel companies would do in practice - but I suspect the latter way may be more likely? If you have a 200 room hotel, you still only have 72000 records for a whole years booking, and with separate records, you can annotate the data more easily. charge stuff to a room on a given day - allow extra occupancy for individual days - amend check-out termsa and so on. etc etc.

--- maybe big chains do both - an overall "stay" record with a start and end date, AND a daily "accommodation" table. A bit of redundancy, but maybe it is worth it.

 

[spikepl]

#3 Before update would seem a good place. ( #4 has verbalised parts of the underlying algorithm of Paul. The point of the algorithm is to logically define what overlaps there are, and then arrive at the Eureka moment, where it dawns on you that identifying the much fewer cases of NO overlap at all is much easier in code, and then you just negate that, and arrive at Paul's recipe.

 

[ChrisO]

Hotel bookings need more than just a date they also need a time.

Generally accepted is the room is vacated by 10:00AM and not occupied until 2:00PM leaving 4 hours for maintenance. This means that a room is double booked on any given date but not between the hours of 10:00AM to 2:00PM on any day.

Another way to do it is to create a booking in all rooms in the fake name of ‘Maintenance’ between the hours of 10:00AM to 2:00PM on all days. If ‘Maintenance’ extends their booking, maybe because of other structural repairs to the room, then all you need to do is extend their departure date.

Triple bookings are also acceptable but quite a bit more complicated. The idea is to triple book where the last booking date/time range is some considerable time in the future, let’s say next Christmas. Long range books are more likely to be cancelled so you can take triple bookings some time into the future and notify the last bookers if no cancellation is made.

But if you do not allow for double bookings on the same date, irrespective of time, then you loose one night per booking. The aim of the game is to keep beds filled on the nights.

Chris.

 

[gemma-the-husky]

Hotel bookings need more than just a date they also need a time.

Generally accepted is the room is vacated by 10:00AM and not occupied until 2:00PM leaving 4 hours for maintenance. This means that a room is double booked on any given date but not between the hours of 10:00AM to 2:00PM on any day.

Another way to do it is to create a booking in all rooms in the fake name of ‘Maintenance’ between the hours of 10:00AM to 2:00PM on all days. If ‘Maintenance’ extends their booking, maybe because of other structural repairs to the room, then all you need to do is extend their departure date.

Triple bookings are also acceptable but quite a bit more complicated. The idea is to triple book where the last booking date/time range is some considerable time in the future, let’s say next Christmas. Long range books are more likely to be cancelled so you can take triple bookings some time into the future and notify the last bookers if no cancellation is made.

But if you do not allow for double bookings on the same date, irrespective of time, then you loose one night per booking. The aim of the game is to keep beds filled on the nights.

Chris.

it obviously needs a lot of careful analysis in practice. with hotel rooms in general, i think the booking generally deals with occupancy for the night, but you may be correct in saying you need to consider the time also.

dayrooms are clearly more complicated, anyway, as I assume multiple bookings could be made for a single day.

i presume normal hotels can only book rooms once per night, although bordello-type operations are probably a bit different, dare i say?

the structure needs to allow for the most complex case, doesn't it?

 

[ChrisO]

>>i presume normal hotels can only book rooms once per night, although bordello-type operations are probably a bit different, dare i say?<<

They certainly are different and need to be handled in a different fashion. It may not be in the normal realms of hotel bookings, or the database which controls them, but it needs to be considered. One approach for the database developer is to stay out of such things but explain the rationale of having a booking made in the name of ‘Maintenance’.

‘Maintenance’ holds the room unavailable for general bookings but frees it up for ‘other things’. From the point of view of taxation records must be kept but from any other point of view it is up to the hotel owner.

Chris.