Re: Recurring events

Le lundi 06 juin 2011 à 12:59 +0200, Thomas Guettler a écrit :

> how do you store recurring events in a database?
>
> Selecting all events in a week/month should be fast (comming from an index).
>
> My solution looks like this:
>
> Table event:
>
> Columns: id, name, recurring, start_datetime, end_datetime
>
> recurring is weekly, monthly, yearly or NULL.
>

Maybe you could try something like what is used in cron, the scheduling
program for GNU/Linux

The crontab file looks like this

# m h dom mon dow user command
17 * * * * root run-parts --report /etc/cron.hourly
25 6 * * * root run-parts --report /etc/cron.daily )
47 6 * * 7 root run-parts --report /etc/cron.weekly )
52 6 1 * * root run-parts --report /etc/cron.monthly )

m is minutes, h hours, dom day of month, mon month, dow day of week.
line 1 executes all scripts in /etc/cron.hourly at 17 minutes after each
hour; line 4 launches all scripts in /etc/cron.weekly every sunday at
06H47

using a similar structure for your table, you could then query for
events occuring weekly, monthly (by finding a value in the proper
column).

You would have to add fields such as first_event_date, last_event_date,
duration...

> end_datetime can be NULL (open end).
>

Then the event is not recurring, it just keeps going; I would consider
that a special case.

--
Vincent Veyron
http://marica.fr/
Logiciel de gestion des sinistres et des contentieux pour le service juridique