表示“重复事件”的最佳方式是什么？在数据库中？
我正在尝试用 C# 开发一个依赖于调度程序和日历的事件应用程序，其一个关键要求是表示数据库中的重复事件。 表示数据库中重复发生的事件的最佳方式是什么？
更多详细信息：
在创建活动时，我还向某些用户发送邀请，并且应仅允许被邀请者在指定的窗口（会议持续时间）内登录会议，或者当被邀请者尝试登录时可能会拒绝登录，例如，会议预定开始前 5 分钟。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
sysjobs，sysjobsschedule 和 sysschedules 表在这方面做得非常好。我不会重新发明轮子，我只是复制他们的设计。
中的一些重要字段
以下是
sysschedulesfreq_typefreq_interval
freq_subday_type
小时freq_subday_interval
freq_relative_interval
freq_recurrence_factor
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
sysschedulesfreq_type
freq_interval
freq_subday_type
freq_subday_interval
freq_relative_interval
freq_recurrence_factor
那么，要存储重复规则本身，您可以使用 RFC 5545 的简化版本（我真的建议你大幅削减它）。除此之外，如果您愿意，这将使您可以轻松导出到其他应用程序。
做出决定后，对于数据库端，您需要确定是否要存储事件的每次出现，还是仅存储重复事件的一条记录，并在您需要时扩展它需要。显然，当您已经扩展了所有内容时，查询数据库会变得更加容易，但这使得维护变得更加困难。
除非你喜欢编写一些相当复杂的 SQL，这可能很难测试（并且你需要针对各种极端情况进行大量的单元测试），否则我建议你让数据库本身相对“愚蠢的”并用 Java 或 C# 等语言编写大部分业务逻辑 - 当然，这两种语言都可以嵌入到存储过程中，具体取决于您的数据库。
您需要问自己的另一件事是您是否需要应对事件的例外 - 一系列事件中的一个事件改变时间/地点等。
我在日历方面有一些经验（我花了大部分时间）去年，我通过 ActiveSync 开发了 Google Sync 的日历部分），我应该警告您，事情会变得复杂< em>真的很快。任何你认为“超出范围”的事情都是一种祝福。特别是，您需要在多个时区工作吗？
哦，最后 - 当您使用日历运算进行实际算术时要非常非常小心。如果您要使用 Java，请使用 Joda Time 而不是内置
Calendar/Date类。他们会给你很多帮助。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/Dateclasses. They'll help you a lot.这里接受的答案太复杂了。例如，如果事件每 5 天发生一次，则 5 存储在 freq_interval 中，但如果每 5 周发生一次，则 5 存储在 freq_recurrence 中。最大的问题是 freq_interval 意味着三种不同的东西，具体取决于 freq_type 的值（每日重复发生的事件之间的天数，每月重复发生的月份中的天数，或每周或每月相对的一周中的天数）。此外，当没有必要且没有多大帮助时，会使用 1,2,4,8... 类型序列。例如，freq_relative_interval 只能是可能值的“其中一个”。这与下拉框或单选按钮类型输入对齐，而不是可以选择多个选项的复选框类型输入。对于编码和人类可读性来说，这个序列会妨碍并且仅使用 1,2,3,4... 更简单、更高效、更合适。最后，大多数日历应用程序不需要子日间隔（一天中多次发生的事件 - 每隔几秒、几分钟或几小时）。
但是，话虽如此，这个答案确实帮助我完善了我对如何做这件事的想法。将其与其他文章混合搭配，并根据我在 Outlook 日历界面和其他一些来源中看到的内容，我得出了以下结论：
重复
0=不复发
1=每日
2=每周
3=每月
recurs_interval
这是重复之间的周期数。如果事件每 5 天重复一次，则值为 5，重复 值为 1。如果事件每 2 周重复一次，则值为 2，重复 值为 1。 2.
recurs_day
如果用户选择每月类型重复，则在该月的给定日期（例如：10 日或 14 日）。这有那个日期。如果用户未选择每月或每月的特定日期重复，则该值为 0。否则值为 1 到 31。
recurs_ordinal
如果用户选择每月类型重复，但选择日期的序数类型（例如：第一个星期一、第二个星期四、最后一个星期五）。这将具有该序数。如果用户未选择这种重复类型，则该值为 0。
1=第一个
2=第二个
3=第三个
4=第四个
5=最后
recurs_weekdays
对于每周和每月顺序重复，这存储重复发生的工作日。
1=周日
2=星期一
4=星期二
8=星期三
16=星期四
32=星期五
64=星期六
所以，例子：
因此，每 4 周的周六和周日就会
，每 6 个月的第一个星期五将
一个字段根据另一个字段的值意味着三个完全不同的事物，这与此无关。
在用户界面方面，我让用户指定日期、开始时间、结束时间时间。然后，他们可以指定是否需要除无之外的重复类型。如果是这样，该应用程序会扩展网页的相关部分，为用户提供上述内容所需的选项，看起来很像 Outlook 选项，除了每日重复下没有“每个工作日”（这与每周一至周五复发），并且没有每年复发。如果存在重复，那么我还要求用户指定今天起一年内的结束日期（用户希望这样，并且它简化了我的代码）-我不会做无休止的重复或“在#之后结束” # 事件。"
我将这些字段与用户选择一起存储在事件表中，并将其展开到包含所有事件的明细表中。这有利于碰撞检测（我实际上正在做一个设施预订应用程序）以及编辑单个事件或重构未来事件。
我的用户都在 CST，为此我感谢上帝。目前这是一个有用的简化，如果将来用户群将扩大到超出这个范围，那么我可以弄清楚如何处理它，作为一个单独的任务。
更新
自从我第一次写这篇文章以来，我确实添加了“每个工作日”的日常发生。我们的用户很难想象您可以使用“每周重复”来处理从一周星期四到下周星期二发生的事件，并且只能在工作日进行。对他们来说，这样做更直观，即使他们已经有另一种方法可以做到这一点。
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:
recurs
0=no recurrence
1=daily
2=weekly
3=monthly
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.
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.
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
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
So, examples:
So, every 4 weeks on Saturday and Sunday would be
Similarly, Every 6 months on the first Friday of the month would be
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."
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.
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.
我也一直在考虑这个问题，虽然还没有实现，但这些是我对一个简单解决方案的想法。
设置重复发生的事件时，让用户指定“结束日期”并为每个事件创建单独的事件（基于重复选项）。由于它是重复事件，因此请为每个事件设置唯一的“重复 ID”。然后，此 ID 将用于将事件标记为重复发生，如果您更改未来事件，您可以提示用户通过删除并使用新的“重复 ID”重新创建重复事件来将此应用到其余的未来事件。还将将此重复发生的事件与之前已更改的事件区分开来。
希望这是有道理的，并希望有任何评论。
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.
Hope this makes sense and would like any comments.
我会将重复发生的事件记录为数据库中的两个单独的事件。首先，在事件表中记录事件的每次发生。其次，有一个重复表，您可以在其中记录设置重复事件所需的详细信息。开始日期、周期、发生次数等。
然后您可能会考虑通过将重复的 PK 作为 FK 放入每个事件记录中来将它们捆绑在一起。但更好的设计是将事件表规范化为两个表，一个表只是事件的准系统，另一个表包含详细信息，现在可以引用多个事件。这样，每个事件记录（无论是否重复）都具有与 eventdetails 表的 PK 相关的 FK。然后在事件详细信息中，将重复的 PK 与议程、受邀者等一起记录。重复记录不会驱动任何内容。例如，如果您想要所有重复事件的列表，您可以通过 eventdetails 查找所有具有非空 FK 的重复事件。
您需要小心同步所有这些内容，以便在重复数据更改时插入或删除事件。
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.
You'll need to be careful to synchronise all of these things, so that you insert or delete events when the recurrence data changes.
“除此之外”
这是否包括“最根本的要求”？
“如果您愿意，这将使您可以轻松导出到其他应用程序。”
规定的要求是否包括“必须很容易将日历导出到其他应用程序”？我的印象是，问题仅在于构建FIRST应用程序。
也就是说，我自己的回应是：
您需要限制您自己/您的用户系统能够支持的“重复性”类型。如果您/您的用户希望最终获得可用的应用程序，“以上所有”或“无限制”将不是有效的答案。
"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.