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.
>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
[EMAIL PROTECTED] | http://www.followers.net/portfolio/
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
