Re: Suggestions on storing re-occurring calendar events - Mailing list pgsql-sql

From Josh Berkus
Subject Re: Suggestions on storing re-occurring calendar events
Date
Msg-id 200408091039.41555.josh@agliodbs.com
Whole thread Raw
In response to Suggestions on storing re-occurring calendar events  (Matt Nuzum <matt.followers@gmail.com>)
List pgsql-sql
Matt,

> I can think of two ways,
> [a] Using application logic, create a finite number of future
> occurrences --- for example, for 10 occurrences, 10 entries into
> ftr_cal_events will be created.  This seems like an ugly hack.
>
> [b] Create some new table that will be unioned onto my query to list
> events.  This seems more elegant and manageable in the long run, and
> is specifically what I'm asking for some suggestions on.

Joe Celko talks about this a bit.  Either approach is valid, but one way or 
another you're going to end up creating a lot of rows procedurally.

First, what's wrong with approach [a]?

The second option [b] is to add a "reference calendar table".   This "refcal" 
will contain all calendar dates for the next 100 years; for convenience, you 
may also wish to add flags for weekends and holidays (though keep in mind 
that holiday dates change from year to year in the US).

Then, for your repeats, you can query against the refcal for all dates that 
suit a particular set of circumstances.  For example, if something is to 
repeat every 7 days for 10 times, then reduce the days difference between the 
parent event and the repeats to an integer, and any date where the modulo is 
0 and is less than 70 is a re-occurance.

Overall, though, I've found approach [a] to be easier and more convenient.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco


pgsql-sql by date:

Previous
From: Vlad Dimitriu
Date:
Subject: Re: Exception handling from trigger
Next
From: Vlad Dimitriu
Date:
Subject: Re: Exception handling from trigger