Re: repeating calendar events

Originally posted by Ben
> I'm sure this is a common question. I've looked around but couldn't
> get a satisfactory answer, so here goes.
>
> I am designing a database for a calendar. This can be done in several
> ways.
>
> Method #1:
>
> The traditional, and easy way of adding events is to add them 1 data
> at a time. So if I wanted to add "myEvent" all week from 5/21/03 to
> 5/28/03, I would have to add 7 different events.
>
> a table in this would look like this:
>
> Create Table event(
> date as Date
> event as varchar
> }
>
> Method #2:
>
> The next logical step is to make the program do the work. Instead of
> manually adding 7 events, create a user interface with dropdowns so
> that the user can select from to
> . The program will then figure out what is needed, and add
> those 7 records on its own.
>
> This works, but it seems that the database will get overloaded with
> unecessary data. Also, often people want to schedule an event "every
> day forever". There is now way that the current design can handle
> something like this.
>
> Method #3:
>
> So the next step is to just store the data that the user would picked
> in method #2 directly in the database, so the table would look like
> this:
>
> create table events(
> start as date //when the repeating records start
> end as date //when they end
> intervalType as varchar //day, month, year, etc...
> interval as number //every 1,2,3 days ect...
> event as varchar
> )
>
> So now, one record:
>
> ('5/21/03','5/28/03','day',1,'myEvent')
>
> actually represents 7! or better put:
>
> ('5/21/03',null,'day',1,'myEvent')
>
> Each record can now represent infinately many records.
>
> Is this a good design? Does anybody know some good sources that talk
> about problems/solutions related to this kind of thing? Like I said,
> it seems very common, so I'm sure its been done before.
>
> Some specific problems that I can see:
>
> *What if you want to change only 1 of the 7 records that are derived
> from the sample record, say, the 3rd one? You would need to create
> three new records:
>
> ('5/21/03','5/22/03','day',1,'myEvent')
> ('5/23/03','5/23/03','day',1,'myEvent')
> ('5/24/03','5/28/03','day',1,'myEvent')
>
> This seems messy.
>
> *Also, a more important problem, is that it is impossible to check for
> scheduling conflicts. If I add a record that repeats every day, and
> then another one that repeats every week but starts in a month, I have
> to run the sproc that derives the actual dates and extrapolate both of
> my entries into the future to see if any of the dates overlap. One
> problem is that I can't extrapolate to infinity, so I have to choose
> something like 1 year into the future. This means conflicts can occur
> 1 year from now. The other, more important problem is if you want to
> check for confilts when adding to a table, you have to derive the
> dates for every single row that COULD conflict, which is probobly most
> of the table. It seems that this sort of operation would take
> forever.
>
Seems to me that you need to make a distinction between the actual calendar (known dates or date ranges to which an "event" is applicable) and a calendar generator like your Method #3.

A calendar generator can be used to generate calendar dates arbitrarily far into the future. However, as you have said, it's not possible to actually generate those dates into the infinite future, there has to be a cut off point.
Also, until you have actually generated the dates, you can't check for conflicts.

You probably need both: the calendar generator can be used to set up recurring events, which must then be generated into actual calendar dates. You would have to have a periodic process to generate calendar dates up to e.g. 1 year ahead. Only when the dates are generated can you really check for conflicts, so of course you cannot know about conflicts that are further into the future than you have currently generated your calendar.

If you tend to have events that span 2 or more contiguous days, then rather than generate a record per day you might want to generate records with a start date and an end date. A one-day event would have end date = start date. In this case, to check for conflicts you would have to look for overlapping records like this:

(r1.start_date <= r2.end_date AND r2.start_date <= r1.end_date)

--
Posted via http://dbforums.com