From: Donald Ball <donald.ball@...>
Date: 2008-06-05T00:07:19+09:00
Subject: Re: Commercial Calendar Calculations

On Tue, Jun 3, 2008 at 7:59 PM, James Herdman <james.herdman@gmail.com> wrote:
> Donald,
> I'm definitely interested in hearing about the details.  I'll be having to
> deal with an RDBMS as well.

Sure thing, I'll describe briefly here and you can feel free to email
me if you have further questions. My data model included a table of
events, a table of occurrences for events on specific dates, and a
table of recurrences for events that recur. The recurrences table has
columns for event id, weekday, monthweek, and monthday; an event
occurring on all Mondays would have 1 for weekday and null for the
others, while an event occurring on the last Friday of a month would
have 5 for the weekday and -1 for the monthweek. I created a view
joining these data (events, occurrences, recurrences) against a table
of dates, excluding holidays, and end up with a virtual table
consisting of a row for every valid event-date pair.

Given that the date functions in SQL aren't standardized, I'd thought
this would have been practically difficult to bundle up as a rails
plugin, but it now occurs to me that I could store the date parts
(e.g. weekday) in the table of dates, eliminating the need for any
date functions in the database. I may give that a whirl this weekend.

- donald