Hotel Room Booking Statement

Question

Essentially i'm making a basic hotel booking system for my computing a-level coursework, and have hit a stumbling block (doesn't help having a migraine either!)

I'm in the process of checking if a room is available for then the user to book (the application is all run and used by staff by the way) but trying to get my head around how to do it.

At the moment I was thinking I could just search the current booking database for the room number and dates I want to book for and if a match came back that meant that certain room was booked. That's all fine and dandy, but how do I tell it the range of dates? (sorry bad question) I was thinking an pseudo code version of the sql statement on the lines of:

If 'check in date of booking already on the database' is before 'check in date of new booking' AND 'check out date of booking already on the database' is after 'check in date of new booking' then room is already booked.

Basically if it returns a record then I know that room is booked. But I just need to figure out how to use SQL to search for records based on my check in/out dates.

But that'll allow double bookings, agh it's driving me mad :/ Any ideas?

Answer 1

It may not seem totally obvious at first, but you can actually simplify the testing of a full or partial date overlap with just two conditions.

Let's say you've got BOOKING table with existing bookings and a new booking with check in date @NEW_CHECK_IN and check out date @NEW_CHECK_OUT. To find all bookings that overlap these dates (assuming the end points are inclusive) you just need this kind of where clause:

--Select overlapping bookings
-- ...
WHERE BOOKING.CHECK_IN <= @NEW_CHECK_OUT
  AND BOOKING.CHECK_OUT >= @NEW_CHECK_IN
-- ...

I know it looks too simple, but try it out with the scenarios that drf illustrated (plus the three that were missed, (1) where the new booking is after the existing booking, (2) the new booking is strictly within the existing booking and (3) the new booking is equal to the existing booking). You'll see that this simple set of conditions covers all of these scenarios.

Answer 2

The pseudocode suggested only considers one possibility of conflict between an existing booking and a new booking. To illustrate, consider a room with one booking.

         |======================|
      Check-in date        Check out date

Suppose we want to create a new booking, and have 4 potential new bookings.

         |======================|       Existing booking

      |-----------------------------|   New booking (Scenario 1)
   |----------|                         New booking (Scenario 2)
                        |-------------| New booking (Scenario 3)
|---|                                   New booking (Scenario 4)

Of these, only Scenario 4 does not overlap an existing booking; the others conflict with the existing booking. While your pseudocode addresses Scenario 1, it does not detect Scenarios 2 or 3 and will thus allow double-bookings.

Effectively, your pseudocode might look something like this.

Let E = booking already on the database
    N = new booking,
    CID = check-in date,
    COD = check-out date

For a new booking N, N conflicts with an existing booking iff there exists a record E where:
  (CID of E is between CID of N and COD of N), or
  (COD of E is between CID of N and COD of N), or
  (CID of N < CID of E and COD of N > COD of E)

In SQL, depending on your schema, the query might resemble something like this:

-- assume @new_cid is the new checkin date and @new_cod is the new checkout date
select count(*) from bookings
where
@new_cid between bookings.checkindate and bookings.checkoutdate or
@new_cod between bookings.checkindate and bookings.checkoutdate or
(@new_cid <= bookings.checkindate AND @new_cod > bookings.checkoutdate)