1

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.

2
  • If you're looking for a query/sql statement, this should be moved to StackOverflow.
    – JeffO
    Jan 28, 2015 at 16:51
  • Databases aren't always the best places for calculations and data aggregates.
    – JeffO
    Jan 28, 2015 at 16:53

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.