Trying to Move Logic to DB - Reservation System

Question

So I'm planning a basic resource reservation system. Basically I'm going to have a room (resource) that has X number of open spots per 30 minute increment. When someone reserves a resource I would store it as resource A is reserved from start time to end time.

Now in order to output to the end user if any open spots are available for a resource at a given time. I need to check to see if the number of reservations (Y) in that 30 minute window are less than the number of open spots (X).

The only way I can think of to do this is inefficient. That is query the reservations for the day, loop through the times of the day, and for each 30 minute block, count the number of reservations in that block (via server side code instead of in the DB). I'm less proficient at SQL.

Is there a way I can accomplish this all on the DB end in a query so the query returns something like a row for each resource and a column for each 30 min increment with the values being the number of open/taken seats?

I'm open to a different DB schema, but the way I see it I have a table or resources which state the max start-end time for reservations, number of possible openings per time period; a table of reservations stating which resource it was and the start-end time of the reservation.

Answer 1

I would create a table to represent the room, perhaps:

Room
------------
RoomId (PK)
RoomNumber

Then a table to represent a reservation:

Reservation
------------------
ReservationId (PK)
RoomId (FK)
StartTime
EndTime

For any given period, you just need to search for all reservations that are valid for that period, e.g to find how many reservations between 10am and 11am, you would do:

SELECT COUNT(*) FROM Reservations 
WHERE 
    RoomId = Room AND
    (StartTime >= 1000 AND StartTime <= 1100) OR
    (EndTime >= 1000 AND EndTime <= 1100)

This SQL query should execute extremely quickly, so looping over each 30 minute period would not take that long.