Re: How to model Recurring events?

On 12 Jun 2001 20:38:37 -0700, satish_at_marathimitra.com (Satish Kamatkar) wrote:

>1...In this application events (Appointments - like in Outlook) can be
>created. These events can be one time or they can be recurring. The
>recurrance can be finite or infinite. i.e an event can occur every
>Monday for current year OR an event can occur every day until end of
>time.

When does time end? Have the appropriate deities been notified?

>2...Also user should be able to manage (modify/delete) each occurance
>of a recurring event indivisually. The modification/updates to any
>particular occurance of an event must not affect other occurances.

[Random thoughts . . .]

The simple, straightforward approach is to store each occurrance. A more compact, but more complicated approach is to store the rule from which each occurance can be derived. I'm not sufficiently motivated to find middle ground.

When you store each occurance, changing one or deleting one is trivial. But storing each occurance can make the database real big, real fast. A single daily task that recurs from now until 31-Dec-9999 would take almost 3 million rows. A single daily task that recurs from now until I'm a hundred years old would take less than 20,000 rows, but that's still a lot of rows to devote to, say, "Empty my cubicle's trash can." <g>

Storing only the rule makes querying more complex. You can't directly answer a question like "What events will happen on 12-Dec-2003?". I'm not sure whether you can answer that question at all without using procedural code; I can't work that one out in my head this morning.

You could argue that storing only the rule is not in keeping with the relational model. Although you could consider each occurance to be derived data, it seems like this kind of derived data is subtly different than, say, the product of Quantity and UnitPrice.

If you don't store each occurance, you have to store each exception. And you might need to do that anyway. You might need to know that one of the events for next Tuesday started as an avent that recurs every Monday.

>My question would be what kind of tables/logic would be best to achive
>this functionality?

I'd start with one table designed to store each occurrance, and one table to store each exception. Then I'd play with it until I understood the implications. I think you'll find this is more complicated than you'd like it to be.

-- 
Mike Sherrill
Information Management Systems