0

I have a hotel booking system.

I have a table Rooms with two basic columns:-

  1. Room_No (Primary key)
  2. AVAILABLE_FROM_DATE (Date)

I have a booking request with below parameters:-

  1. Booking_ID
  2. Booking_start_date (Date)
  3. Booking_end_date (Date)

So for every booking , I need to check if a room is available within booking_start_date and booking_end_date. Using somewhat below query right now:-

SELECT Room_No 
FROM Rooms 
WHERE AVAILABLE_FROM_DATE >= booking_start_date 
AND AVAILABLEFROMDATE < booking_end_date;

If available, then I need to allocate that room to that particular Booking_ID for that particular start_date, end_date pair only.

I need to update the same information in Rooms table for that particular room_no , so that a room is not booked twice for a particular date range. For now I am doing this by updating AVAILABLE_FROM_DATE column as booking_end_date + 1 .

Problem is with current implementation I can keep track of only one date range.

So , in case My room is available from 1 Jan , and a booking comes for 1 Feb- 10 Feb, I update the AVAILABLE_FROM_DATE to 11 Feb. So for another booking, say 1 Jan - 31st Jan, although my room was available but I was not able to allocate it.

Is there any way I can keep record of all the date ranges within which my room is available so that I can better allocate the rooms.

I am thinking of making a separate table to store multiple booked (start, end) date ranges for every Room_No but the Rooms table can be very big (upto 5000 rows), so I need to take care of efficiency as well.

Any suggestions on how should I proceed with my problem to achieve maximum allocation?

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
codebug
  • 1
  • 1
  • Possible duplicate of [Hotel Room Booking Statement](http://stackoverflow.com/questions/20177304/hotel-room-booking-statement) – Joel Brown Jul 03 '16 at 12:30
  • Look at my answers to these two questions, it should give you what you need: http://stackoverflow.com/questions/20177304/hotel-room-booking-statement/20184162#20184162 and http://stackoverflow.com/questions/10280333/how-to-manage-rooms-availability-based-on-days-or-months-occupation/10280605#10280605 – Joel Brown Jul 03 '16 at 12:31
  • @JoelBrown Thanks for the pointers. Will your solution work if a room is booked on multiple slots say 1Aug-31Aug and 1Oct-30Oct ,and I try to book 25Aug-2Oct? – codebug Jul 03 '16 at 14:40
  • Yes, the solution works no matter how many bookings there are and whether there are gaps in between the bookings or not. – Joel Brown Jul 03 '16 at 14:50
  • @JoelBrown That's great , Thanks a lot. But if I have a requirement that I dont care about the `checkin_date` as long as that room remains booked on its `checkout_date`. Say if a `room_101` was booked from `1Aug-1Nov`, now if I get another booking from `1July-31stAug`, I should give this room to the new booking, and my previous booking `checkin_date` should become `1Sept`. But if the new booking was `1Aug-1Nov`(checkout date conflict), I should not allocate it the `room_101`. Can it be achieved doing some modification in your solution? Sorry I am really a beginner in SQL. – codebug Jul 03 '16 at 15:45
  • I don't understand your business rules. It sounds to me like you are suggesting that if you have a booking from 1 Aug to 1 Nov you would cancel that because of a booking from 1 Jul to 31 Aug. That doesn't make sense to me. – Joel Brown Jul 03 '16 at 17:20

1 Answers1

0

First off, a table with 5000 records isn't big at all. Second, I see design flaw here. Given your data structure it's seems impossible to achieve what you're asking.

The AVAILABLE_FROM piece of data is a report - someone will ask your system someday if some room is available at some other day. So reports should not be saved in a field just to show this data afterwards. Instead, loose the report field AVAILABLE_FROM and add a foreign key in the Bookings table, pointing to the Rooms table. Next, in your code, when somebody place a reservation, add the room id to the booking (sounds natural, doesn't it?). Later when someone asks the system whether particular room is available, or before you place another reservation, you need to you run a query to see if this room isn't already booked for that period; something like this:

SELECT TOP 1 1
FROM Bookings
WHERE RoomId = room_of_interest 
    AND Booking_start_date > 'start_date_criteria' AND Booking_end_date < 'end_date_criteria'

If this query return 1, obviously the room isn't available in that period.

Bozhidar Stoyneff
  • 3,576
  • 1
  • 18
  • 28
  • Thanks @Bozhidar for your help. As of now, I don't have `Bookings` table. I get a Booking request, I query the entire `Rooms` table as per start,end date range coming from Booking request , and select one of the result (Room_No) if the result is not empty. I don't have to query a particular room_no, I just expect the system to find the available rooms for my booking. – codebug Jul 02 '16 at 19:18
  • Why not create the bookings table and fill it upon receiving the request? – Bozhidar Stoyneff Jul 02 '16 at 19:21
  • Yes I can create a table for Bookings, but I am not able to figure out how will it help me to get the available room for a start,end date range. – codebug Jul 02 '16 at 19:28
  • `select RoomId from Rooms r where not exists (select 1 from Bookings b where b.RoomId = r.RoomId and @searchStartDt <= b.EndDt and b.StartDt <= @searchEndDt)` – shawnt00 Jul 02 '16 at 19:40
  • Well the `Bookings` table will keep record of the booking requests, right? The booking request comes with the date range in which the room will be occupied (if the booking itself is accepted). You need to create a table which stores the request AND the room id for the room booked. This will help you to analyze the bookings later and figure out which room is occupied when... – Bozhidar Stoyneff Jul 03 '16 at 07:25