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