How to model Recurring events?

[Satish Kamatkar]

Hi -

I am about design a data model for a Calendar application. Here are
some basic requirements:

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.

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.

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

Any thoughts?

Thanks,

Satish

[Brian Inglis]

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

An event either happens on a given date or is a recurring event.
A recurring event either has a definite or an infinite end date.
A recurring event either happens on certain days of the week or
month or every so many days or weeks or months.
A recurring event may be represented by multiple events with the
same event id.
A modified occurrence of a recurring event happens on a given
date.
Consider the number of events to be generated for each set of
parameters of a recurring event and the columns to be stored in a
table which will identify that the recurrence matches properties
of a given date, including deltas or modulus (remainder after
division) from the start date to a given date of number of days,
weeks, months.
The mapping of a recurrence to events should be invertable to
recover recurrences from events.
Evaluate a function of a given date and event parameters to
determine if an event happens on that date.

OR

Dump and decode the external data from an existing appointment
manager.

Which you choose may depend on your skill set.

Thanks. Take care, Brian Inglis Calgary, Alberta, Canada
--
Brian....@CSi.com (Brian dot Inglis at SystematicSw dot ab dot ca)
fake address use address above to reply
tos...@aol.com ab...@aol.com ab...@yahoo.com ab...@hotmail.com ab...@msn.com ab...@sprint.com ab...@earthlink.com ab...@cadvision.com ab...@ibsystems.com u...@ftc.gov
spam traps

[Thomas Muller]

Hi Satish,

I've had similar problems before, and I find that the Unix crontab
representation works very well. If you're not familiar with the crontab
pattern, here is a short explanation

A crontab consists of five fields each specifying an integer pattern as
follows:
* minute (0-59)
* hour (0-23)
* day of the month (1-31)
* month of the year (1-12)
* day of the week (0-6 with 0=Sunday)

Each of these patterns may be either an asterisk (meaning all legal values)
or a list of elements separated by commas. An element is either a number or
two numbers separated by a minus sign (meaning an inclusive range). Note
that the specification of days may be made by two fields (day of the month
and day of the week). Both are adhered to if specified as a list of
elements.

I've added a couple of fields: timezone (the timzone the crontab operate
within), and valid timeset (the timeset the crontab is valid).

Actually the Crontab is a class that implements the more general TimeSet
interface which basically is a set of explicit moments and/or periods of
time.

The Crontab leaves you with complete flexibility for modelling recurring
events.

Now have do you store this in a relational database? Well, you could just
keep is as a text value in a column relevant for the events. In order to
speed up queries you would definately have the code running in the database
itself, e.g PL/SQL / Java for Oracle. You might want to split that various
field to different columns, but I'm not sure how much you would gain.

--

Thomas

"Satish Kamatkar" <sat...@marathimitra.com> wrote in message
news:20df3b70.01061...@posting.google.com...

[MShe...@compuserve.com]

On 12 Jun 2001 20:38:37 -0700, sat...@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

[--CELKO--]

>> I am about design a data model for a Calendar application. Here are
some basic requirements:
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.

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.

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

First, you might want to get Rick Snodgrass's book on temporal queries
and structures in SQL (Morgan-Kaufmann Publishers, www.mkp.com). It
is the best one on this topic.

I am inclined toward a table like this:

CREATE TABLE Appointments
(event_id INTEGER NOT NULL,
event_time TIMESTAMP NOT NULL,
event_description VARCHAR(30) NOT NULL DEFAULT '??',
reoccurs INTEGER NOT NULL,
PRIMARY KEY (event_id, event_time));

This puts all the work in front end or the stored procedures. Each
appointment is a separate row, created by a dialogue with the user.
The reoccurs column is a flag that tell you how many times this event
will happen. If it is zero, we do it just this one time and delete
it, if it is greater than zero, we call up the maximum reoccurs number
and delete that row. When the reoccurs number is set to one, we ask
about extending the event again.

INSERT INTO Appointments VALUES ('2001-02-24', 'My Birthday', 5);
INSERT INTO Appointments VALUES ('2002-02-24', 'My Birthday', 4);
INSERT INTO Appointments VALUES ('2003-02-24', 'My Birthday', 3);
INSERT INTO Appointments VALUES ('2004-02-24', 'My Birthday', 2);
INSERT INTO Appointments VALUES ('2005-02-24', 'My Birthday', 1);

Then you would have simple statements like this to find an
appointment:

SELECT A1.*
FROM Appointments AS A1
WHERE A1.reoccurs
= (SELECT MAX(reoccurs)
FROM Appointments AS A2
WHERE A1.event_id = A2.event_id);

To mark it as completed:

UPDATE Appointments
SET reoccurs = 0
WHERE reoccurs
= (SELECT MAX(reoccurs)
FROM Appointments AS A2
WHERE Appointments.event_id = A2.event_id);

etc.

[Michael Bonfert]

Is there any decoder availabe to decode an event in crontab syntax?
somthing like a shell command?

thanks in advance
Michael

"Thomas Muller" <t...@online.no> schrieb im Newsbeitrag
news:9gg463$dol$1...@news6.svr.pol.co.uk...