Menu
I am trying to develop a scheduler- and calendar-dependent event application in C#, for which a crucial requirement is to represent recurring events in the database. What is the best way to represent recurring events in a database?
More Details:
While creating the event I am also sending invites to the certain users and the invitees should be allowed to login to the meeting only during the specified window(meeting duration) or may be decline the login when the invitee attempts to login say, 5 minutes before the scheduled start of the meeting.
423
Add a recurrence domain to the database that supports a number of different values, including “daily”, “weekly”, and “monthly”. Add a recurrence column to the events table that identify how an event recurs. Add a recurrence_dates table that contains a pre-generated list of recurrences for a given date.
A recurring event is an event that happens more than once, on a repeating schedule. When a repeating event is turned into individual event instances with individual dates, it is called “expanding” the event.
In Salesforce Classic, from Setup, enter Activity Settings in the Quick Find box, then select Enable Creation of Recurring Events. If you disable this setting, users can still edit the interval of an existing event series, but they can't create a new series.
The sysjobs, sysjobsschedule and sysschedules tables in SQL Server does a pretty good job of this. I wouldn't reinvent the wheel, I'd just copy their design.
Here are some of the important fields from sysschedules
freq_type
How frequently a job runs for this schedule.
1 = One time only
4 = Daily
8 = Weekly
16 = Monthly
32 = Monthly, relative to freq_interval
64 = Runs when the SQL Server Agent service starts
128 = Runs when the computer is idle
freq_interval
Days that the job is executed. Depends on the value of freq_type. The default value is 0, which indicates that freq_interval is unused. Value of freq_type Effect on freq_interval
1 (once) freq_interval is unused (0)
4 (daily) Every freq_interval days
8 (weekly) freq_interval is one or more of the following: 1 = Sunday 2 = Monday 4 = Tuesday 8 = Wednesday 16 = Thursday 32 = Friday 64 = Saturday
16 (monthly) On the freq_interval day of the month
32 (monthly, relative) freq_interval is one of the following: 1 = Sunday 2 = Monday 3 = Tuesday 4 = Wednesday 5 = Thursday 6 = Friday 7 = Saturday 8 = Day 9 = Weekday 10 = Weekend day
64 (starts when SQL Server Agent service starts) freq_interval is unused (0)
128 (runs when computer is idle) freq_interval is unused (0)
freq_subday_type
Units for the freq_subday_interval. Can be one of the following values: Value Description (unit)
1 At the specified time
2 Seconds
4 Minutes
8 Hours
freq_subday_interval
Number of freq_subday_type periods to occur between each execution of the job.
freq_relative_interval
When freq_interval occurs in each month, if freq_interval is 32 (monthly relative). Can be one of the following values:
0 = freq_relative_interval is unused
1 = First
2 = Second
4 = Third
8 = Fourth
16 = Last
freq_recurrence_factor
Number of weeks or months between the scheduled execution of a job. freq_recurrence_factor is used only if freq_type is 8, 16, or 32. If this column contains 0, freq_recurrence_factor is unused.
121
Well, to store the recurrence rule itself, you could use a cut down version of RFC 5545 (and I really suggest you cut it down heavily). Aside from anything else, that will make it easy to export into other applications should you wish to.
After you've made that decision, for the database side you need to work out whether you want to store each occurrence of the event, or just one record for the repeated event, expanding it as and when you need to. Obviously it's considerably easier to query the database when you've already got everything expanded - but it makes it trickier to maintain.
Unless you fancy writing some pretty complex SQL which may be hard to test (and you'll want a lot of unit tests for all kinds of corner cases) I would suggest that you make the database itself relatively "dumb" and write most of the business logic in a language like Java or C# - either of which may be embeddable within stored procedures depending on your database, of course.
Another thing you need to ask yourself is whether you need to cope with exceptions to events - one event in a series changing time/location etc.
I have some experience with calendaring (I've spent most of the last year working on the calendar bit of Google Sync via ActiveSync) and I should warn you that things get complicated really quickly. Anything you can deem "out of scope" is a blessing. In particular, do you need to work in multiple time zones?
Oh, and finally - be very, very careful when you're doing actual arithmetic with calendar operations. If you're going to use Java, please use Joda Time rather than the built-in Calendar/Date classes. They'll help you a lot.
22
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
