How do I persist recurring events?

Question

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:

events 
-------
id INT
name VARCHAR
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):

• Considering iCalendar standards
• Reviewed this SO link
• Still mulling a better path.

Answer 1

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.