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.