I(m creating a calendar that displays a timetable of events for a month. Each day has several parameters that determine if more events can be scheduled for this day (how many staff are available, how many times are available etc).
My database is set up using three tables:
Regular Schedule - this is used to create an array for each day of the week that outlines how many staff are available, what hours they are available etc
Schedule Variations - If there are variations for a date, this overrides the information from the regular schedule array.
Events - Existing events, referenced by the date.
At this stage, the code loops through the days in the month and checks two to three things for each day.
Are there any variations in the schedule (public holiday, shorter hours etc)?
What hours/number of staff are available for this day?
(If staff are available) How many events have already been scheduled for this day?
Step 1 and step 3 require a database query - assuming 30 days a month, that(s 60 queries per page view.
I(m worried about how this could scale, for a few users I don(t imagine that it would be much of a problem, but if 20 people try and load the page at the same time, then it jumps to 1200 queries...
Any ideas or suggestions on how to do this more efficiently would be greatly appreciated!
posted date: 2009-04-09 09:42:00