Calendar recurring events logic

[Shabam]

I'm having a calendar feature developed whereby users can add recurring
events. These recurring events can have start and end dates, meaning they
will recur only within a date range. For instance, user A creates an event
that recurs every Tuesday, from 1/1/05 - 3/2/06.

The developer has decided that it would be good to have another table that
stores each individual recurring event in a separate record. His logic is
that this will help with the display of calendar months. His logic is, if
the application has to dynamically iterate through every event record in the
database and logically map out which days have recurrence, that it would not
be good for performance.

My take though, is that if one recurring event can literally add in 10,000
records, then that's even worse.

What are your thoughts on the proper way to implement this?

 

[Uri Dimant]

Hi
It has always been hard to suggest for design issues,because you and only
you know exactly the business requrements.
CREATE TABLE Req_Events
(
EventId INT NOT NULL PRIMARY KEY,
UserId INT NOT NULL FOREIGN KEY ------Users Table,
StartDate DATETIME NOT NULL,
EndDate DATETIME NOT NULL -----you can create here a check constraints that
enddate must be less than startdate
)

 

[Guest]

Hi

From my perspective.
Create and entry for each occurrence in the main table, and link all the
occurrences together with an occurrence ID. This will enable the user to
remove a single occurrence (like an event that falls on a public holiday that
nobody would attend), without upsetting any logic. This may sound expensive,
but when you query for a specific user "Give me the events between today and
Sunday", it is a single query to a single table with a between operator. No
complicated logic to find out if there is a recurring event in the pile too.

An occurrence can also be changed. If a meeting moved from tomorrow onwards
to a new time, the old records for events that have occurred in the past
remain, and only the records dated tomorrow and later would be updated with
the new time. History can't change.

An occurrence is just a quick UI way for a user to do something that is
repetitive. It should not be handled in a different manner, compared to a
single event, in a DB just because of this. Separating Presentation and Data
logic.

Regards
Mike

 

[Tibor Karaszi]

sp_msforeachdb 'EXEC sp_helpfile'

Note that sp_msforeachdb is not documented, not supported etc. Also, if you don't like the result
structure, you can easily change that by taking the source for from sp_helpfile and write your own
version that presents the information in a way that you like it.

 

[Shabam]

From my perspective.

Create and entry for each occurrence in the main table, and link all the
occurrences together with an occurrence ID. This will enable the user to
remove a single occurrence (like an event that falls on a public holiday that
nobody would attend), without upsetting any logic. This may sound expensive,
but when you query for a specific user "Give me the events between today and
Sunday", it is a single query to a single table with a between operator. No
complicated logic to find out if there is a recurring event in the pile too.

An occurrence can also be changed. If a meeting moved from tomorrow onwards
to a new time, the old records for events that have occurred in the past
remain, and only the records dated tomorrow and later would be updated with
the new time. History can't change.

An occurrence is just a quick UI way for a user to do something that is
repetitive. It should not be handled in a different manner, compared to a
single event, in a DB just because of this. Separating Presentation and Data
logic.

Sounds fine, but what the system will allow many users to set up their own
calendars. If a user sets up an event that literally has 1000 individual
recurrences, then it won't take many users to bloat the database to an
unacceptable level wouldn't it? Also, the thought of a user clicking a
single button that generates 1000 records just doesn't seem right to me.

 

[Tibor Karaszi]

Oops. Replied to wrong thread. Please ignore.