Amount of rooms available over a given period?

Question

I need to be able to check whether a date range given by the user is available. I have a table called booked in my database which holds a start date, end date, and the amount of rooms that have been booked over that period.

Lets say my booked table looks like this:

room_id | start      | end        | amount_of_rooms_booked
1       | 2012-12-12 | 2012-12-15 | 2
1       | 2012-12-13 | 2012-12-16 | 3
1       | 2012-12-18 | 2012-12-19 | 1

If there are 8 rooms available in total for room_id 1, and the user asks for this room between 2012-12-10 and 2012-12-20, is it possible to do a query that checks whether the SUM of rooms that have been booked for each day does not exceed 8.

The other way I could do this would be to store each day individually, and have the booked table like this instead:

room_id | start      | amount_of_rooms_booked
1       | 2012-12-12 | 2
1       | 2012-12-13 | 5
1       | 2012-12-14 | 5
1       | 2012-12-15 | 3
1       | 2012-12-18 | 1

That way would use more space in the database but would probably be easier. Which should I do? And if the first idea how would I do the query? Are there any other better ways to achieve what I'm trying to do?

What's the meaning of `room_id`? – Branko Dimitrijevic Jan 06 '13 at 10:20 — Branko Dimitrijevic, Jan 06 '13 at 10:20

score 0 · Answer 1 · answered Jan 05 '13 at 20:15

0

To answer your original question, you would do:

SELECT SUM(amount_of_rooms_booked) FROM booked
WHERE (start BETWEEN '2012-12-10' AND '2012-12-20' OR
       end BETWEEN '2012-12-10' AND '2012-12-20')
AND room_id = 1

I think how you're storing it is fine, although I would probably store a single row per room booking, rather than a row with an 'amount_of_rooms_booked' column. Then the query above would be a COUNT query instead of a SUM.

answered Jan 05 '13 at 20:15

wless1

3,489
1
16
12

1

The thing is it needs to be separated by day because if there was a row with `1, 2012-12-14, 2012-12-16, 5` and another row with `1, 2012-12-17, 2012-12-18, 4` then the SUM would exceed 8 but there is still 3 rooms available for the first row and 4 for the second – John Smith Jan 05 '13 at 20:43

score 0 · Answer 2 · edited May 23 '17 at 11:43

To your first option, you can always use a function just like this.

Then you have a query like this (i'm using SQL Server) that returns the rooms having less than 8 bookings in a given date:

SELECT D.theDate, SUM(amount_of_rooms_booked) AS [amount_of_rooms_booked]
FROM
          booked
INNER JOIN
          dbo.ExplodeDates('20121210','20121220') D ON D.thedate BETWEEN booked.start AND booked.end
WHERE
          booked.room_id = @room
GROUP BY
          booked.room_id, D.thedate
HAVING
          SUM(amount_of_rooms_booked) < @bookingLimit

However, if this practice is the best, I don't know. I would rather use your second option, due to being less complex to interpret and I never know if i'm gonna need a another kind of query on it.

score 0 · Answer 3 · answered Jan 10 '13 at 14:19

I would recommend to have a row for each booking day per room.

Every possible calculation is easier with that structure. Yes you will have the downside of having more data in the table, but it will not affect your database as much as you think. I event think most queries will be faster because you will avoid complex calculations with dates.

So here are my pros for the second method:

it matches users thinking model (mostly)
SQL is simpler! and faster (mostly)
it is easier to maintain
easier to keep data clean (possibly)

Amount of rooms available over a given period?

3 Answers3