Re: How to model Recurring events?

From: --CELKO-- <joe.celko_at_trilogy.com>
Date: Sat, 21 Jul 2001 23:31:27 GMT
Message-ID: <7e67a7b3.0106181148.498a2bc8_at_posting.google.com>

>> 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. Received on Sun Jul 22 2001 - 01:31:27 CEST