在数据库中表示“重复事件”的最佳方式是什么?

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?

我正在尝试在c#中开发一个与调度程序和日历相关的事件应用程序，其中一个关键的需求是表示数据库中的重复事件。在数据库中表示重复发生的事件的最佳方式是什么?

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.

在创建活动时我也向特定的用户发送邀请和受邀者应该允许登录到会议只在指定的窗口(会议时间)或可能减少登录当受邀者试图登录说,于会议开始前5分钟。

6 个解决方案

#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.

SQL Server中的sysjobs、sysjobsschedule和sysschedule表在这方面做得很好。我不会重新发明*，我只是复制他们的设计。

Here are some of the important fields from sysschedules

下面是sysschedule中的一些重要字段

freq_type

freq_type

How frequently a job runs for this schedule.

工作在这个计划中运行的频率。

1 = One time only

1 = 1次。

4 = Daily

4 =每天

8 = Weekly

8 =每周

16 = Monthly

每月16 =

32 = Monthly, relative to freq_interval

32 =每月，相对于freq_interval。

64 = Runs when the SQL Server Agent service starts

64 = SQL Server代理服务启动时运行

128 = Runs when the computer is idle

当计算机空闲时运行。

freq_interval

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

执行任务的天数。取决于freq_type的值。默认值为0，表示freq_interval未使用。freq_type对freq_interval的影响。

1 (once) freq_interval is unused (0)

1 (1) freq_interval未使用(0)

4 (daily) Every freq_interval days

4(每天)每隔一天

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

8(周)freq_interval有以下一个或多个:1 = Sunday 2 = Monday 4 = Tuesday 8 = Tuesday 8 = Wednesday 16 = Thursday 32 = Friday 64 = Saturday

16 (monthly) On the freq_interval day of the month

16(月)在每月的间隔日

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

32(月，相对)freq_interval是这样的:1 =周日2 =周一3 =周二4 =周三5 =周四6 =周五7 =周六8 =工作日10 =周末

64 (starts when SQL Server Agent service starts) freq_interval is unused (0)

64 (SQL Server Agent服务启动时启动)freq_interval未使用(0)

128 (runs when computer is idle) freq_interval is unused (0)

128(计算机空闲时运行)freq_interval未使用(0)

freq_subday_type

freq_subday_type

Units for the freq_subday_interval. Can be one of the following values: Value Description (unit)

freq_subday_interval单位。可以是以下值之一:值描述(单位)

1 At the specified time

1在规定的时间

2 Seconds

2秒

4 Minutes

4分钟

8 Hours

8小时

freq_subday_interval

freq_subday_interval

Number of freq_subday_type periods to occur between each execution of the job.

每个作业执行之间发生的freq_subday_type周期的数量。

freq_relative_interval

freq_relative_interval

When freq_interval occurs in each month, if freq_interval is 32 (monthly relative). Can be one of the following values:

当每个月发生freq_interval时，如果freq_interval为32(月相对)。可以是下列值之一:

0 = freq_relative_interval is unused

0 = freq_相对论_interval未使用

1 = First

1 =第一

2 = Second

2 =第二

4 = Third

4 =第三

8 = Fourth

8 =第四

16 = Last

最后16 =

freq_recurrence_factor

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.

计划执行任务之间的星期或月数。freq_recurrence_factor只在freq_type为8、16或32时使用。如果此列包含0，则freq_recurrence_factor未使用。

#2

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.

好吧，为了存储这个递归规则本身，你可以使用RFC 5545的精简版本(我真的建议你大幅删减)。除此之外，如果您愿意，还可以很容易地将其导出到其他应用程序中。

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.

除非你喜欢写一些很复杂的SQL可能很难测试(单元测试,你需要很多各种角落的情况下)我建议你让数据库本身相对“哑巴”和写的大部分业务逻辑的语言像Java或c# -这可能是可嵌入在存储过程根据您的数据库,当然可以。

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?

我在日历方面有一些经验(去年大部分时间我都在通过ActiveSync开发谷歌同步的日历位)，我应该警告您，事情会很快变得复杂起来。任何你认为“超出范围”的事情都是好事。特别是，你需要在多个时区工作吗?

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.

最后，当你在做日历运算时要非常非常小心。如果您打算使用Java，请使用Joda时间，而不是内置的日历/日期类。他们会帮你很多。

#3

The accepted answer here is too convoluted. For example, if an event occurs every 5 days, the 5 is stored in freq_interval, but if it occurs every 5 weeks, the 5 is stored in freq_recurrence. The biggest problem is that freq_interval means three different things depending on the value of freq_type (number of days between occurrences for daily recurrence, day of the month for monthly recurrence, or days of the week for weekly or monthly-relative). Also, the 1,2,4,8... type sequence is used when it is unnecessary and less than helpful. For example, freq_relative_interval can only be "one of" the possible values. This lines up with a drop-down box or radio button type input, not a checkbox type input where multiple choices can be selected. For coding, and for human readability, this sequence gets in the way and just using 1,2,3,4... is simpler, more efficient, more appropriate. Finally, most calendar applications don't need subday intervals (events occurring multiple times in a day - every so many seconds, minutes, or hours).
But, having said this, that answer did help me refine my thoughts on how I am doing this. After mix and matching it with other articles and going from what I see in the Outlook calendar interface and a few other sources, I come up with this:

这里公认的答案太复杂了。例如，如果一个事件每5天发生一次，那么5将存储在freq_interval中，但是如果它每5周发生一次，5将存储在freq_recursive中。最大的问题是，freq_interval根据freq_type的值有三种不同的含义(每日复发的天数，每月复发的天数，每周或月相对的一周天数)。另外,1、2、4、8…当类型序列是不必要的和没有帮助的时候使用。例如，freq_相对论_interval只能是可能的值之一。这将使用下拉框或单选按钮类型输入，而不是选中多个选项的复选框类型输入。对于编码，对于人类的可读性，这个序列就在路上，只用了1 2 3 4…更简单，更高效，更合适。最后，大多数日历应用程序不需要子日间隔(事件在一天中多次发生——每隔那么多秒、分钟或小时)。但是，话虽如此，这个答案确实帮助我完善了我的想法。在将它与其他文章混合并与之匹配之后，根据我在Outlook calendar界面和其他一些资源中看到的内容，我得出了以下结论:

recurs
0=no recurrence
1=daily
2=weekly
3=monthly

复发0=无复发1=日2=周3=月

recurs_interval
this is how many of the periods between recurrences. If the event recurs every 5 days, this will have a 5 and recurs will have 1. If the event recurs every 2 weeks, this will have a 2 and recurs will have a 2.

递归区间这是递归之间的周期数。如果事件每5天重复一次，这将会有一个5，而recurs将有1。如果事件每两周重复一次，这将有一个2，重复将有一个2。

recurs_day
If the user selected monthly type recurrence, on a given day of the month (ex: 10th or the 14th). This has that date. The value is 0 if the user did not select monthly or specific day of month recurrence. The value is 1 to 31 otherwise.

如果用户选择每月类型的递归，在一个月的某一天(例如:第10天或第14天)。那个日期。如果用户没有选择每月或特定的月递归，则该值为0。值是1到31。

recurs_ordinal
if the user selected a monthly type recurrence, but an ordinal type of day (ex: first monday, second thursday, last friday). This will have that ordinal number. The value is 0 if the user did not select this type of recurrence.
1=first
2=second
3=third
4=fourth
5=last

如果用户选择了一个月的递归类型，但是选择了一个日的序号(例如:第一个星期一，第二个星期四，最后一个星期五)。这是序数。如果用户没有选择这种类型的递归，则该值为0。1=前2=后3=第三4=第四5=最后

recurs_weekdays
for weekly and monthly-ordinal recurrence this stores the weekdays where the recurrence happens. 1=Sunday
2=Monday
4=Tuesday
8=Wednesday
16=Thursday
32=Friday
64=Saturday

每周和每月的递归式复发日存储复发发生的工作日。1=星期日2=星期一4=星期二8=星期三16=星期四32=星期五64=星期六

So, examples:
So, every 4 weeks on Saturday and Sunday would be

所以，例子:所以，周六和周日的每4周。

• recurs = 2 ==> weekly recurrence
• 复发= 2 =>周复发
• recurs_interval = 4 ==> every 4 weeks
• 递归区间= 4 =>每4周
• recurs_weekdays = 65 ==> (Saturday=64 + Sunday=1)
• 递归工作日= 65 =>(周六=64 +周日=1)
• recurs_day and recurs_ordinal = 0 ==> not used
• 递归日和递归序号= 0 =>不使用

Similarly, Every 6 months on the first Friday of the month would be

同样的，每六个月的第一个星期五也是如此

• recurs = 3 ==> monthly recurrence
• 复发组= 3 =>月复发
• recurs_interval = 6 ==> every 6 months
• 递归区间= 6 =>每6个月
• recurs_ordinal = 1 ==> on the first occurrence
• 递归序数= 1 =>
• recurs_weekdays = 32 ==> of Friday
  
• 递归一周= 32 ==>周五。

None of this business of having a field that means three entirely different things depending on the value of another field.

拥有一个字段并不意味着三个完全不同的东西，这取决于另一个字段的值。

On the user interface side of things, I let the user specify a date, start time, end time. They can then specify if they want a type of recurrence other than none. If so, the app expands the relevant section of the web-page to give the user the options required for the stuff above, looking a lot like the Outlook options, except there is no "every weekday" under daily recurrence (that is redundant with weekly recurrence on every mon-fri), and there is no yearly recurrence. If there is recurrence then I also require the user to specify an end-date that is within one year of today (the users want it that way, and it simplifies my code) - I don't do unending recurrence or "end after ## occurrences."

在用户界面方面，我让用户指定一个日期，开始时间，结束时间。然后，它们可以指定是否需要除none以外的递归类型。如果是这样,网页的应用程序扩展了相关部门给用户所需的选择上面的东西,看起来很像前景的选择,除了没有“每个工作日”在日常复发(即冗余与每周每个mon-fri复发),每年也没有复发。如果有递归，那么我还要求用户指定一个在今天一年内结束的日期(用户希望如此，它简化了我的代码)——我不做无止尽的递归或“#出现后结束”。

I store these fields with the user selections in my event table, and expand that out in a schedule table which has all occurrences. This facilitates collision detection (I am actually doing a facility reservation application) and editing of individual occurrences or refactoring of future occurrences.

我将这些字段与事件表中的用户选择一起存储，并将其扩展到具有所有事件的调度表中。这有助于冲突检测(我实际上是在做一个设备预订应用程序)和编辑单个事件或重构未来的事件。

My users are all in CST, and I thank the good Lord for that. It is a helpful simplification for now, and if in the future the user base is going to expand beyond that, then I can figure out how to deal with it then, as a well separated task.

我的用户都在CST，我为此感谢上帝。现在这是一个有用的简化，如果将来用户基础将扩展到这个范围之外，那么我就可以知道如何处理它了，作为一个独立的任务。

UPDATE Since I first wrote this, I did add daily occurrence with "Every weekday". Our users had a bit of a hard time with thinking that you could use Weekly recurrence for events happening from Thursday one week to Tuesday the next week and only on weekdays. It was more intuitive for them to have this, even if there was already another way that they could do it.

自从我第一次写这篇文章以来，我确实添加了“每个工作日”的每日事件。我们的用户遇到了一些困难，他们认为你可以利用每周复发的时间来处理从周四到下周二的事件，并且只在工作日。对他们来说，更直观的是，即使他们已经有了另一种方法。

#4

I have been thinking about this too, although have not implemented it but these are my thoughts for a simple solution.

我也一直在思考这个问题，虽然还没有实现，但这是我对于一个简单解决方案的想法。

When setting up an event thats recurring, have the user specify the "end date" and create individual events for each one (based on the recurring options). Because its a recurring event, set a unique "recurring ID" for each of these. This ID will then be used to mark an event as recurring and if you change a future event, you can prompt the user to apply this to the rest of the future events by deleting and recreating the recurring events with a new "recurring ID" which will also differentiate this recurring event from the previously ones that have changed.

当设置重复发生的事件时，让用户指定“结束日期”并为每个事件创建单独的事件(基于重复发生的选项)。因为它是一个循环事件，所以为每个循环事件设置一个唯一的“循环ID”。这个ID将被用于事件标记为反复出现的,如果你改变未来事件,可以提示用户把这个应用到其余的删除和重新创建重复事件未来事件的一个新的“重复ID”也将区分重复事件和之前那些已经改变了。

Hope this makes sense and would like any comments.

希望这是有意义的，并希望有任何评论。

#5

I would record recurring events as two separate things in the database. First of all, in an events table, record each and every occurence of the event. Secondly, have recurrences table in which you record the details that you ask for to set up the recurring event. Start date, periodicity, number of occurences, etc.

我将把重复发生的事件记录为数据库中的两个独立的东西。首先，在事件表中，记录事件的每一个事件。第二，要有递归表，在这个表格中，你要记录你所要求的建立重复事件的细节。开始日期、周期、出现次数等。

Then you might think of tying it all together by putting the PK of recurrences into each of the event records as an FK. But a better design would be to normalise the event table into two tables, one which is just the barebones of an event, and one which has the details, which could now be referring to multiple events. That way every event record, recurring or not, has an FK to the PK of the eventdetails table. Then in eventdetails, record the PK of recurrences somewhere along with agenda, invitees, etc. The recurrence record does not drive anything. For instance, if you want a list of all recurring events, you look through eventdetails for all events with a non-null FK to recurrences.

然后你可能会想要把它们结合起来把递归的PK作为FK放到每个事件记录中。但是更好的设计是将事件表规范化为两个表，一个只是事件的骨架，另一个包含细节，现在可能指的是多个事件。这样，每个事件记录(无论是否重复)都有一个FK到eventdetails表的PK。然后在eventdetails中，记录递归的PK以及日程、受邀者等。递归记录不会驱动任何东西。例如，如果您想要一个所有重复事件的列表，那么您可以查看所有事件的eventdetails，其中包含一个非空FK以进行递归。

You'll need to be careful to synchronise all of these things, so that you insert or delete events when the recurrence data changes.

您需要小心地同步所有这些东西，以便当递归数据发生变化时插入或删除事件。

#6

"Aside from anything else"

“除了别的”

does this include "the very requirements" ?

这是否包括“真正的需求”?

"that will make it easy to export into other applications should you wish to."

“如果你愿意，这将使你很容易出口到其他应用程序。”

Do the stated requirements include "it must be easy to export the calendars to other applications" ? My impression was that the problem consisted solely of building the FIRST application.

声明的要求包括“必须很容易将日历导出到其他应用程序”吗?我的印象是，问题仅仅是构建第一个应用程序。

that said, my own response :

我的回答是:

You need to limit yourself/your user on the types of "recurrency" your sytem will be able to support. And "All of the above" or "No Limitations" will not be a valid answer if you/your user want(s) to end up with a usable application.

您需要将自己/您的用户限制在系统能够支持的“递归”类型上。如果您/您的用户希望最终得到一个可用的应用程序，那么“上述所有”或“无限制”将不是一个有效的答案。