Message-ID: <27c475ec04080907562c370b3a@mail.gmail.com>
Date: Mon, 9 Aug 2004 10:56:21 -0400
From: Matt Nuzum <matt.followers@gmail.com>
Reply-To: matt@followers.net
To: pgsql-sql@postgresql.org
Subject: Suggestions on storing re-occurring calendar events
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

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_key btree (eventid),
        ftr_cal_events_calendarid_key btree (calendarid),
        ftr_cal_events_enddate_key btree (enddate),
        ftr_cal_events_eventid_key btree (eventid),
        ftr_cal_events_startdate_key btree (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.