Best way to query calendar events?

Question

Hi,

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:

1. 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
2. Schedule Variations - If there are variations for a date, this overrides the information from the regular schedule array.
3. 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.

1. Are there any variations in the schedule (public holiday, shorter hours etc)?
2. What hours/number of staff are available for this day?
3. (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!

Thanks!

Answer 1

Create a table:

t_month (day INT)

INSERT
INTO     t_month
VALUES
         (1),
         (2),
         ...
         (31)

Then query:

SELECT   *
FROM     t_month, t_schedule
WHERE    schedule_date = '2009-03-01' + INTERVAL t_month.day DAY
         AND schedule_date < '2009-03-01' + INTERVAL 1 MONTH
         AND ...

Instead of 30 queries you get just one with a JOIN.

Other RDBMS's allow you to generate rowsets on the fly, but MySQL doesn't.

You, though, can replace t_month with ugly

SELECT  1 AS month_day
UNION ALL
SELECT  2
UNION ALL
...
SELECT  31

Answer 2

I can't think of a good reason you'd need to limit each query to one day. Surely you can just select all the values between a pair of dates.

Similarly, you could use a join to get the number of events scheduled events for a given day.

Then do the loop (for each day) on the array returned by the database query.

Answer 3

I faced the same sort of issue with http://rosterus.com and we just load most of the data into arrays at the top of the page, and then query the array for the relevant data. Pages loaded 10x faster after that.

So run one or two wide queries that gather all the data you need, choose appropriate keys and store each result into an array. Then access the array instead of the database. PHP is very flexible with array indexing, you can using all sorts of things as keys... or several indexes.