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

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.

I would disagree on that. What if a task repeats every saturday for the next 7 years... And what if there were a lot of these repeating tasks? That would cost you a lot of waste space. Therefor, I think it's better to save a recurring task as just one record + one record for every exception (since there are less exceptions than recurrences).

Well, the only problem left is how to set up a query to select each task (still thinking abou that)

Answer 2

I went through the same problem a while back and instead of reinventing the wheel, I used Google Calendar APIs. (http://code.google.com/apis/calendar/data/2.0/developers_guide.html)

You create a Google Account and access the calendar information. There are APIs to create/edit/delete a recurring entry. Also, you can specify a date/time information and query for matching events.

When you create an event on Google Calendar, you will receive a token/id which you can store in your own database and reference it within the context of the application.

Answer 3

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 4

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.