Double booking problem with hotel db

[chrlwhttkr]

I'm creating a Hotel Database and I have a form which allows users to book a room. I have ran into a problem where users can double book a room. The fields are as follows:
room_ID
customer_ID
from_date
to_date
Is there any way to check if the room is booked between these dates to not allow the room to be booked again?
Any helped would be appreciated
Thank you.

 

[theDBguy]

Hi. Welcome to AWF!

You could use either DCount() or a multi-field unique index to prevent double booking.

 

[plog]

I don't think this can be accomplished at the table level, you will need VBA.

My vision would be an unbound form where the user selects a room and dates and clicks a button to submit the data. The code on that button does a DCOUNT() to see if any records in the table have booked dates that encompass the submitted dates for a room. If yes, you message the user and say you can't book. Otherwise the code saves the submitted data to the table.

 

[The_Doc_Man]

The trick is that if you have a booking of more than two days in duration, you have the problem that the dates might be in conflict even though none of the dates you have are equal to each other. You have FOUR dates to consider. Each room has a potential set of dates, call it ROOM_FROM and ROOM_TO. But a customer will have a CUST_FROM and CUST_TO date as well.

You want to avoid these cases:
1. Eliminate potential rooms where CUST_FROM BETWEEN ROOM_FROM AND ROOM_TO
(Eliminate case where customer's stay starts in the middle of a room's booking.)
2. Eliminate potential rooms where CUST_TO BETWEEN ROOM_FROM AND ROOM_TO
(Eliminate case where customer's stay ends in the middle of a room's booking.)
3. Eliminate potential rooms where ROOM_FROM BETWEEN CUST_FROM AND CUST_TO
(Eliminate case where customer's stay completely overlaps room's booking.)
If any rooms are left over after these eliminations, they are candidates for booking.

The "X BETWEEN Y AND Z" syntax uses greater or equal for Y and less or equal for Z so would work in a query for this.

My approach would be to make an empty temporay table of room numbers. (If this is a split DB, put the temp table in the front end.) Then using the above ideas, do an INSERT INTO of the room numbers where you found a conflict using each of the 3 queries. Then from your list of rooms, select the subset of rooms that are NOT IN the temp table, which gives you the answer in 4 realitively fast queries. However, your bookings table structure has to be something like

Rooms: Room_ID, description, anything room-specific but not time-specific
RoomBooked: Room_ID, Cust_ID, Cust_From, Cust_To (OR Room_From, Room_To) since in this junction table, those two pairs of dates are equal
Cust: Cust_ID, name, anything person-specific but not time-specific

TempBooked: Room_Used - this is the temp table you erase before starting a booking. Insert each list of booked rooms as detected in the three "Between" tests. Then find a list of ROOM_ID FROM ROOMS WHERE ROOM_ID NOT IN (SELECT ROOM_USED FROM TEMPBOOKED) ;

 

[jdraw]

You may get some ideas from this older thread

 

[MajP]

In the form's before update

Public Function IsAvailable(dtFrom As Date, dtTo As Date, RoomID As Long) As Boolean
  Dim strFrom As String
  Dim strTo As String
  Dim strSql
  Dim cnt As Integer
  strFrom = SQLDate(dtFrom)
  strTo = SQLDate(dtTo)
  strSql = "((From_Date <= " & strFrom & " AND To_Date >= " & strFrom & ") "
  strSql = strSql & "OR (From_Date <= " & strTo & " AND To_Date >= " & strTo & ") "
  strSql = strSql & "OR (From_Date >= " & strFrom & " AND To_date <= " & strTo & ")) "
  strSql = strSql & "And Room_ID = " & RoomID
  Debug.Print strSql
  cnt = DCount("*", "tblBookings", strSql)
  'Debug.Print cnt
  IsAvailable = (cnt = 0)
End Function

as mentioned there are three checks.
from date does not fall in any range
to date does not fall in any range
and you do not span any existing range.

Here is what the string would look like if I try to book 1/1/2021 to 1/26/2021
((From_Date <= #01/01/2021# AND To_Date >= #01/01/2021#) OR (From_Date <= #01/26/2021# AND To_Date >= #01/26/2021#) OR (From_Date >= #01/01/2021# AND To_date <= #01/26/2021#)) And Room_ID = 1

 

[MajP]

Now in truth I doubt you want to put in the dates for a room to learn that it is not available. I would think you would want to put the dates in and the return the available rooms. So you could modify the function to instead return the strSql (where clause). Then you can build the query from that and return the available rooms for that date.

 

[Sun_Force]

Several months ago, as the final test in databases course, we had to offer a database for a hotel.

I know everyone who has suggested his idea in this thread is far more experienced than me, but as a novice in databases I had to find something simple within my abilities. It's what I ended to, to prevent double booking.

I made a child table like this:
tblOccupied
OccupiedPK - RoomFK - OccupiedOn

In booking form, when a room was booked, I inserted a record for each day a room was occupied (from date_From to a day before Date_To.)
Then it was easy to check if a room is occupied in a certain date. Just DCount on this table could prevent double booking.

This method helped me to have a graph in Excel to show how busy the hotel has been during a specified date and another graph with a pivot table as its source could show a chart for each room.

Note: I didn't insert a record for the last stay day. Because normally check in is several hours later than check out.

I'd like to hear what you think about this. Or is this method actually usable in real life?

 

[theDBguy]

Here's a similar approach.

A practical use of a Cartesian Query

As promised in my previous blog on Cartesian Query , I will now try to describe in detail one example of a practical use of a Cartesian Quer...

thedbguy.blogspot.com

 

[Sun_Force]

Here's a similar approach.

A practical use of a Cartesian Query

As promised in my previous blog on Cartesian Query , I will now try to describe in detail one example of a practical use of a Cartesian Quer...

thedbguy.blogspot.com

@theDBguy I'm reading your blog and haven't finished it yet. Just a quick question.
When a guest checks out a room and another guest checks in the same room on the same day, doesn't Cartesian query count it as two days?
Because the same room has two records for the same date under two different guests.

thank you.

 

[CJ_London]

Note: I didn't insert a record for the last stay day. Because normally check in is several hours later than check out.

what about accounting for number of guests in for breakfast?

In reality you need a time element as well for check in and out.

 

[Sun_Force]

what about accounting for number of guests in for breakfast?

Sorry, but I don't understand your question. I don't know how hotel business in US/UK works, but here nobody books a room for having breakfast. If it's what you mean. Otherwise a more detailed explanation of what you mean is appreciated.

 

[CJ_London]

Assuming you are talking about a hotel, in the UK you would book full board (bed, breakfast, lunch and evening meal), half board (bed, breakfast and evening meal) or bed and breakfast. Occasionally hotels offer bed only.

 

[Sun_Force]

Assuming you are talking about a hotel, in the UK you would book full board (bed, breakfast, lunch and evening meal), half board (bed, breakfast and evening meal) or bed and breakfast. Occasionally hotels offer bed only.

I think I need another cup of coffee. That's exactly the same as here. But still I can't relate it to your question above. why time is a concern and date is not enough to take care of double booking.

 

[CJ_London]

If the records are set as last date being the day before they actually leave, you have a more complex requirement to determine catering numbers. And cleaning schedules for that matter

Anyway, discussion is academic

 

[chrlwhttkr]

Thanks everyone for the replies! I am a beginner in all of this so I will have a read through the comments and see what is in my capabilities haha! Thanks again

 

[theDBguy]

@theDBguy I'm reading your blog and haven't finished it yet. Just a quick question.
When a guest checks out a room and another guest checks in the same room on the same day, doesn't Cartesian query count it as two days?
Because the same room has two records for the same date under two different guests.

thank you.

Hi. To answer your question... As written, the cartesian query in my blog won't count the same room/day combo twice for two separate bookings.

As an experiment, I added a new booking to the sample table where the next person checks in on the same day the last person checks out.

And when I ran the query, that date for that room was only counted once.

 

[oleronesoftwares]

Hi,
So here is how you can handle one of the scenarios, i will think through the other areas.

My idea is based on your question, emphasis is on Booking dates, but in practice, a system should include booking dates and checking dates as this might differ.

So to solve this, most of the logic has to be placed around the start date because, on the booked start date, the room must be available.

Two things to check for are

A. Is there the same booking start date for the room in the system.

B.Are there in the system a booking start date before the new booking start date and an End booking date
(e.g someone wants to book for January 2nd 2022, a booking start date of January 1st 2022 and End date of January 3rd 2022 exists for same room).

My solution for now handles scenerio A alone, you can create a field in table end, make it an indexed field, and set it to "No duplicates), then
concatenate the startdate field and room nos,(check the vba code), this will prevent duplicate start dates for same room to be entered in the system.