Обсуждение: Suggestions on storing re-occurring calendar events

Hello, I've got an application and there is something that I just
cannot get figured out.

I currently store a bunch of events for various calendars in related
tables that look like this:
ftr_cal_master Column   |         Type          |      Modifiers
------------+-----------------------+----------------------
calendarid | bigint                | not null
icon       | character varying(12) | not null
showpast   | boolean               | not null default 't'
headline   | text                  |
paragraph  | text                  |
Indexes: ftr_master_pkey primary key btree (calendarid),       ftr_master_caledarid_key btree (calendarid)

ftr_cal_events Column    |           Type           |                           Modifiers   
-------------+--------------------------+----------------------------------------------------------------
eventid     | integer                  | not null default
nextval('"ftr_cal_events_eventid_seq"'::text)
calendarid  | bigint                   | not null
startdate   | timestamp with time zone | not null default now()
enddate     | timestamp with time zone | not null default now()
title       | text                     |
description | text                     |
contact     | text                     |
location    | text                     |
Indexes: ftr_cal_events_pkey primary key btree (eventid),       eventid_ftr_cal_events_ukey unique btree (eventid),
 calendarid_ftr_cal_events_key btree (calendarid),       enddate_ftr_cal_events_key btree (enddate),
eventid_ftr_cal_events_keybtree (eventid),       ftr_cal_events_calendarid_key btree (calendarid),
ftr_cal_events_enddate_keybtree (enddate),       ftr_cal_events_eventid_key btree (eventid),
ftr_cal_events_startdate_keybtree (startdate),       startdate_ftr_cal_events_key btree (startdate),
 

I'd like to figure out a way to add simple re-occurrances of events.

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.

You see, no matter how hard I think about it, I can't come up with a
*simple* way to do this.  Everything I come up with has become
extremely complex.

The two simplest cases to accommodate are events that re-occurr on a
given day of the month (i.e. the 3rd of each month) and events thht
re-occurr on a given day of the week (i.e. every Tuesday).

As simple as those are, I can't come up with a database structure that
accommodates both.

From a user-interface stand point, my target users expect simplicity
so I'm not trying to re-invent Outlook with it's myriads of options.

I've been googling and I've seen several interesting ideas, but none
use relational SQL for storage and retrieval.

I'm not asking for people to do my work for me, but does someone have
any interesting suggestions that would help me get started?  I'll
admit that I'm lacking in my knowledge of the date/interval handling
functions of postgres, so maybe there's some there of great benefit.

Thanks for any advice,

-- 
Matthew Nuzum     | Makers of "Elite Content Management System"
www.followers.net     | View samples of Elite CMS in action
matt@followers.net     | http://www.followers.net/portfolio/

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

>> I'd like to figure out a way to add simple re-occurrances of
events. 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. <<

No, not really; go with [a].  This is SQL and it is designed for
tables, not computations.  For example, to pull out one I recently
looked at, say I am setting out a payment plan.  I generate a list of
100 payments made up of (client_id, payment_due_date, payment_due_amt,
actual_payment) rows by some simple temporal math -- "give me $100
every 30 days!"

Now I go to my Calendar table (lots of posting about why you need a
Calendar table on Google).  If a payment date falls on a holiday, I
move it up to the next business day.  I do not try to calculate Easter
or Chinese News Years on the fly; I cannot calculate somethings like
national emergencies and disasters.

I can print the whole plan out for the guy; his future actual payments
are defaulted zero, and I can easily recompute his future amounts due
from his past payments.

Other advantages: portable data instead of proprietary temporal
computations. The rows in this payment schedule are pretty small, so
it runs fast. You can immediately see conflicts by having all the
enterprised events in the same place and in the same format.  For
example, summing up the expected payements for any given date is
trivial with a full payment schedule on everyone.