From: jcelko212@earthlink.net (--CELKO--)
Subject: Re: Suggestions on storing re-occurring calendar events
Date: 12 Aug 2004 12:45:01 -0700
Organization: http://groups.google.com
Lines: 30
Message-ID: <18c7b3c2.0408121145.2a035b1f@posting.google.com>
References: <27c475ec04080907562c370b3a@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
To: pgsql-sql@postgresql.org

>> 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.