I've been tasked with designing a system to recur events. Events may recur daily, hourly, monthly or yearly. Users should be able to schedule events "every 3rd Wednesday of the month" or "The 1st of each month" or "hourly."

Say I'm starting with a 'events' table:

id INT
notes TEXT

What fields/entities/architecture should be used to persist the recurrence rules?

fwiw, I'm using MySQL 5.5.x.

UPDATE (Aug 29, 2012):

  • 1
    Check the relative question: What is the best way to represent “Recurring Events” in database? – ypercubeᵀᴹ Aug 28 '12 at 0:08
  • Why can't you use Event Scheduler? dev.mysql.com/doc/refman/5.5/en/events.html – Leigh Riffel Aug 28 '12 at 12:28
  • @Leigh Riffel, we're talking about a calendar of events managed by a user via a software GUI. Their choices need to be persisted. – randomx Aug 28 '12 at 22:16
  • 2
    It's a tough problem (in general). Do you have to take into account (different per user) time zones? Can events be recurring for ever? Will you need to store all the occurences of an event in a separate table? – ypercubeᵀᴹ Aug 28 '12 at 22:21
  • Timezones are involved, but locations are persisted, so at least there's per-user timing. The offset is per location. Basically the plan is to generate a future/next even, store it and present it to the user for completion. – randomx Aug 28 '12 at 22:24

I think this falls under the category of things whose requirements are so varied and the solutions so complex that there aren't standardized solutions.

Ultimately you need these elements:

A system for recording the recurrence schedule. A system for generating the recurring events. A system for storing the events generated.

You probably need to spend a fair amount of time determining your requirements before you start implementing a solution, as getting it wrong up front will cost you tons of time later.

You could take a peek at the Mozilla Lightning project, as they have recurring tasks.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.