c# - What is the best way to represent "Recurring Events" in database?

Question

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.

Answer 1

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.