Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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):

share|improve this question
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
    
@ypercube, good link. Thanks! –  randy melder Aug 28 '12 at 22:16
    
@Leigh Riffel, we're talking about a calendar of events managed by a user via a software GUI. Their choices need to be persisted. –  randy melder 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
show 1 more comment

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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