query for available rooms in hotel reservation

Question

i was tasked to create an online hotel reservation system. the problem i encountered is the query to find the available rooms for a certain room category for a certain date range. My database design involves 4 tables. The database design is as follows:

tbl_reservationdetails ( stores the general details of the reservation )
 pk resrvdtl_id (primary key)
 fk client_id (insignificant for now)
    start_date (customer's check-in-date)
    end_date (customer's check-out-date)

tbl_reservation (stores the rooms reserved for a particular reservation )
 pk reserv_id (primary key)
 fk resrvdtl_id (foreign key, to know to whom and when the room should be occupied)
 fk room_id (the room reserved)

tbl_room
 pk room_id (primary key)
    room_number 
 fk room_categId (to know what category this room belongs to)

tbl_roomcategory
 pk room_categId (primary key)
    room_category (description of category.. example: Suite, Superior, Deluxe etc. in my case... there are four categories)

user input is the dates (start and end), and the category of room he wants. I'm quite new at this... how do I query to check the available room for that category for a certain date?????

any response to this would be highly appreciated... thanks

Answer 1

I would probably expand the requested date range into the corresponding date list, then checked each date from the list and each room of the requested category against the reservation records.

Here's my solution for SQL Server, which uses a recursive CTE for expanding the date range:

WITH cte_datelist AS (
  SELECT @startdate AS date
  UNION ALL
  SELECT DATEADD(day, 1, date)
  FROM cte_datelist
  WHERE date < @enddate
)
SELECT
  r.room_number,
  MIN(d.date) AS free_from,
  MAX(d.date) AS free_to
FROM cte_datelist d
  CROSS JOIN tbl_room r
  INNER JOIN tbl_roomcategory rc ON r.room_categId = rc.room_categId
  LEFT JOIN tbl_reservation rr ON r.room_id = rr.room_id
  LEFT JOIN tbl_reservationdetails rd ON rr.resrvdtl_id = rd.resrvdtl_id
    AND d.date BETWEEN rd.start_date AND rd.end_date
WHERE rc.room_category = @category
  AND rd.resrvdtl_id IS NULL
GROUP BY
  r.room_number,
  DATEDIFF(day, @startdate, d.date)

Two other RDBMS-specific elements of this query are the two date functions:

• DATEADD, used in the CTE for iterating through the date range;

• DATEDIFF, used for grouping the result set on contiguous date sequences for the same room, so instead of

  room_number  date
  1783         27/03/2011
  1783         28/03/2011
  ...          ...
  1783         02/04/2011
  1783         03/04/2011
  1785         15/03/2011
  1785         16/03/2011
  ...          
  1785         01/04/2011
  1785         02/04/2011
  1785         06/04/2011
  1785         07/04/2011
  ...          ...
  1785         10/04/2011
  ...          ...
  

  you could have the output like this:

  room_number  free_from   free_to
  1783         27/03/2011  03/04/2011
  1785         15/03/2011  02/04/2011
  1785         06/04/2011  10/04/2011
  ...          ...         ...
  

Answer 2

SELECT rooms.room_id
FROM tbl_rooms rooms
INNER JOIN tbl_roomcategory cat ON rooms.room_categId = cat.room_categId
WHERE cat.room_category = [category]
AND rooms.room_id NOT IN (SELECT t1.room_id
                          FROM tbl_room t1
                          INNER JOIN tbl_reservation t2 ON t1.room_id = t2.room_id
                          INNER JOIN tbl_reservationdetails t3 ON t2.resrvdtl_id = t3.resrvdtl_id
                          WHERE NOT ([end date] < t3.start_date OR ([start date] > t3.end_date))

So I'll explain. This finds any room that is unavailable on the dates provided.

(SELECT t1.room_id
FROM tbl_room t1
INNER JOIN tbl_reservation t2 ON t1.room_id = t2.room_id
INNER JOIN tbl_reservationdetails t3 ON t2.resrvdtl_id = t3.resrvdtl_id
WHERE NOT ([end date] < t3.start_date OR ([start date] > t3.end_date))

This selects all rooms on the specified category.

SELECT rooms.room_id
FROM tbl_rooms rooms
INNER JOIN tbl_roomcategory cat ON rooms.room_categId = cat.room_categId
WHERE cat.room_category = [category]

This selects all rooms that are of the specified category and not in the unavailable rooms.

AND rooms.room_id NOT IN (SELECT t1.room_id
                          FROM tbl_room t1
                          INNER JOIN tbl_reservation t2 ON t1.room_id = t2.room_id
                          INNER JOIN tbl_reservationdetails t3 ON t2.resrvdtl_id = t3.resrvdtl_id
                          WHERE NOT ([end date] < t3.start_date OR ([start date] > t3.end_date))

I haven't been able to test this, but should be right. If there are any problems with it, let me know and I'll fix it up.