Best way to develop/manage/design recurring tasks/calendar

Question

An example of what I'm talking about is similar to Google Calendar. When a new recurring task is created.

After creating the recurring task "template" - which all of the individual tasks are based on, do you create all of the individual tasks and store them in the database? or do you just store the "template" recurring events and their exceptions?

If the user requests a "month" view, and you want to display all of the events/tasks, it seems like creating the output in real time from the template, and including all of the exceptions would be a lot more resource intensive then if each individual recurring tasks was created from the template and inserted into the database.

This would make searching/sorting, etc, a lot more easier too.

Anybody create something like this before? ideas?

Answer 1

Store it all in the database.

You want to have a "Task Template" table and a "Task" table where there is a one->many relationship.

When the user indicates they want a task to reoccur, create a "Task Template" record and then create as many "Tasks" as the user has indicated (don't allow a user to create tasks too far into the future). Each Task is linked to the Task Template via a Foreign Key. The idea is that SQL is going to be more efficient at managing these records than trying to do this all in code based on one template. This way, you will have more option when your sorting and filtering your data. After all, writing a SQL query is easier than writing, testing, and maintaining a PHP function that manipulates the data.

Some other tips I would give you is:

• Try to get a lot of information in your "Task Template" record. Keep the number of tasks the Template covers, the date the last task ends, the time elapsed between the first task and the last, etc.. This "Meta Data" can help save you query time when you're looking to sort and filter tasks.
• Put an index on the Date and FK field, this will help query time as well.
• I just built two calendar apps at work that were pretty well received by the bosses. I used the "FullCalendar" JQuery plugin (http://arshaw.com/fullcalendar/). I used JQuery AJAX to handle most of my events, and it had built in support for Month, Day, and Week view.

Answer 2

For recurring events I did the following a while back:

1. When a user entered an event I stored the event's date pattern GNU date style - the keyword for PHP is relative date formats.

2. Then I started off by creating events for e.g. the next year. And created actual records where I converted the relative date to an actual -- e.g. "every first Monday" to a "mm-dd-YYYY". This allowed me to display them and also allow the user to e.g. move a single event or cancel one, etc..

3. Then figure out how far to go into the future - my idea was to create events when the actual pages were browsed. E.g. if I had created events through June 2011 and someone skipped all the way to July 2011, I would iterate on my events and set them up transparently.

4. When the user changes the relative pattern, offer to update all following events -- unless they have a custom pattern already. Relative patterns make it super easy to calculate all that.