Reservation System Design

My client has 12 work areas. She wants her clients to be able to sign up for sessions at these areas on-line. The clients will sign up for either 1.5 or 2 hours, and she only wants 4 people to be able to start every half hour.

I would like to avoid having to iterate through every half hour of every day to determine if there are available opennings that the client could sign up for.

My initial thought was to have a table of sessions, which had a field for start time and end time, then use a COUNT query to find out how many users had a start time at that half hour, but we need to present the user with a view of which time slots are available for each half hour. Since the interface is via the internet, I am afraid that this will require a lot of data to be sent back and forth.

I also thought, from a performance point of view, it may be more efficient to de-normalize the database and create a table with a record for each half hour that would store the number of people who have signed up for that half hour. This would make the query for the calander a lot simpler, but could possibly make syncronizing the data more difficult, and could the table get rather large. I could possibly purge records from this table for dates in the past, since only future time slots would have any importance.

If anyone has any experience doing a database like this, I would really appreciate some assistance.

Thanks in advance.

Gary Holt Received on Wed Dec 07 2005 - 12:43:30 CET