I am about to begin development on a website which manages the availability of a cello teacher and lessons that students have signed up for. Currently, I have two tables:
Availability (date, startTime, endTime) - This table stores time spans that people can sign up for lessons Lessons (date, startTime, endTime) - This table stores each lesson. A lesson can begin at times of 5 minute intervals (5:30, 5:35, etc) and will vary in length between 30 and 60 minutes. I will be accessing the database with PHP. I want to get a list of all available time slots (spots at least 30 minutes long which fall within a time span in Availability and are not occupied by Lessons.) I will need to determine open slots for 2-4 weeks in one page call. Ideally I'd like to keep the processing within mySQL because if I have to load all the lessons into arrays in PHP to determine openings, it will slow it down considerably. You may assume that I will be using mySQL 4.1.x (in case you need subselects for the solution). Thanks so much! -Greg
