Suggestions on storing re-occurring calendar events

Поиск
Список
Период
Сортировка
От Matt Nuzum
Тема Suggestions on storing re-occurring calendar events
Дата
Msg-id 27c475ec04080907562c370b3a@mail.gmail.com
обсуждение исходный текст
Ответы Re: Suggestions on storing re-occurring calendar events  (Josh Berkus <josh@agliodbs.com>)
Список pgsql-sql
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/


В списке pgsql-sql по дате отправления:

Предыдущее
От: Christoph Haller
Дата:
Сообщение: Re: select
Следующее
От: Vlad Dimitriu
Дата:
Сообщение: Exception handling from trigger