How to handle dates that repeat indefinitely

Question

I am implementing a fairly simple calendar on a website using PHP and MySQL. I want to be able to handle dates that repeat indefinitely and am not sure of the best way to do it.

For a time limited repeating event it seems to make sense to just add each event within the timeframe into my db table and group them with some form of recursion id.

But when there is no limit to how often the event repeats, is it better to

a) put records in the db for a specific time frame (eg the next 2 years) and then periodically check and add new records as time goes by - The problem with this is that if someone is looking 3 years ahead, the event won't show up

b) not actually have records for each event but instead when i check in my php code for events within a specified time period, calculate wether a repeated event will occur within this time period - The problem with this is that it means there isn't a specific record for each event which i can see being a pain when i then want to associate other info (attendance etc) with that event. It also seems like it might be a bit slow

Has anyone tried either of these methods? If so how did it work out? Or is there some other ingenious crafty method i'm missing?

Answer 1

I'd take approach b and if someone adds something to it, I'd create a "real" event entry.

Edit: How many periodic events do you expect and what kind of periodic events would that be? (eg: every monday, every two weeks etc.)

Answer 2

I would create a single record for a repeated event. Then in case more info has to be added to a specific date, I would create a record for the attachment with a reference to the repeated event.

Answer 3

Third vote for option B - rationale being that the data should only ever be queried for a limited timeframe (i.e. start and end). For performance reasons I'd suggest that, in addition to storing the date/time of the first occurrence, number of occurrences and frequency that you also maintain the last occurrence in the database.

C.

Answer 4

From my experience, generating recurring dates and checking if a specific date is in that pattern isn't all that bad performance-wise. There's only 365 days in a year. 10,000 days is already almost 30 years. which means, the size of the input/output is relatively small in a practical scenario.

This library may help (but it's javascript): http://github.com/mooman/recurring_dates