重复性事件的数据库设计与异常 [英] Database design for recurring events with exceptions
问题描述
我正在构建一个需要存储/管理不同类型事件的系统.为简单起见，我将专注于设计日历(我正在构建一些稍微不同的东西，但日历是一个很好的类比，而且很容易推理).我想听听可能的数据库/模式设计想法.
I'm building a system that needs to store/manage different types of events. For simplicity, I will focus on designing a calendar (I'm building something slightly different, but calendar is a good analogy and it's easy to reason about). I'd like to hear about possible database/schema design ideas.
问题描述
我有一个包含不同类型事件的日历(为简单起见，假设只有一种类型的事件:任务).用户可以为特定日期添加新事件、编辑(更改一些细节，如标题或移动到另一个日期)或删除.可以有一次性事件和重复事件(具有不同类型的重复:每 X 天、每月第 15 天、每周星期一；有点像简单的 cron).当用户移动重复事件时，该事件的所有其他实例都以相同的方式移动(例如:+3 天).重要部分:重复事件可以有例外.因此，例如，假设我有一个周期性事件 A，它每 7 天重复一次.但是我想将它的日期更改为下周，所以它被分配到星期五而不是星期二，之后它仍然会在星期二发生.移动父"事件时不应影响此异常"事件.
I have a calendar with different types of events (for simplicity sake, say there is only 1 type of event: Task). User can add new event for a particular date, edit (change some details, like title or move to another date) or delete. There can be one-time events and recurring events (with different types of recurrence: every X days, every 15th day of the month, every week on Monday; kind of like simple cron). When user moves recurring event, all other instances of this event are moved in the same manner (e.g: +3 days). Important part: recurring events can have exceptions. So, for example, let's say I have an recurring event A which is repeated every 7 days. But I want to change it's date for next week, so instead of Tuesday, it's be assigned to Friday, after that it'll still occur on Tuesday. This "exception" event shouldn't be affected when "parent" event is moved.
此外，每个重复事件都可以有附加信息，仅与 1 个特定实例相关，例如:我有相同的重复事件 A 每 7 天重复一次，我想为本周实例添加一条注释，上面写着X"，并且我想为下个月的事件 A 添加另一个注释，上面写着Y" - 这些字段仅对该单个实例可见.
Also, every recurring event can have additional info, that is related only to 1 particular instance, e.g: I have the same recurring event A repeated every 7 days, I want to add a note for this week instance that says "X", and I want to add another note for the event A next month that says "Y" - those fields are only visible to that single instances.
想法
具有定期、一次性事件的系统非常简单，因此我不会讨论这个问题，而只关注重复性事件.
System with regular, one-time events is pretty straightforward so I won't discuss that and focus only on recurring events.
1. 一种可能的解决方案是类似于 OOP 的解决方案:我可以拥有一个带有 start_date 等字段的 Event 类"，end_date(可以是 null)、recurrence_type(类似于枚举，可能值为 EVERY_X_DAYS、DAY_OF_WEEK、DAY_OF_MONTH)和 recurrence_value(比如 7).当用户添加新的重复事件时，我只是在数据库中创建这样的 Event.当用户想要更改此事件的 1 次发生时，我将新条目添加到类型/类 MovedEvent 的数据库中，该条目从 Event 以不同的日期继承"并具有额外的related_to 字段指向与其相关的 Event 的 ID(或 UUID，如果你愿意的话).但同时，我需要跟踪所有 MovedEvent(否则我会在同一周显示 2 个事件)，所以我需要一个数组 moved_events 的 ID 指向所有 MovedEvent.缺点:每次我想显示日历时，我都需要获取 Event 并从 moved_events 中选择所有事件，如果我这样做不是最优的'会有很多感动的事件.
1. One possible solution is the one that resembles OOP: I can have an Event "class" with fields such as start_date, end_date (can be null), recurrence_type (something like enum with possible values of EVERY_X_DAYS, DAY_OF_WEEK, DAY_OF_MONTH) and recurrence_value (say 7). When user adds new recurring event, I just create such Event in the database. When user wants to change 1 occurrence of this event, I add new entry to the DB of the type/class MovedEvent that "inherits" from Event with different date and has additional field related_to that points to the ID (or UUID, if you will) of the Event that it's related to. But at the same time, I need to keep track of all the MovedEvents (otherwise I'd have 2 events displayed in the same week), so I need to have an array moved_events of IDs that point to all MovedEvents.
Disadvantage: every time I want to display the calendar I need to get Event and select all events from the moved_events, which is not optimal if I'll have a lot of moved events.
2. 另一个想法是将每个事件存储为单独的记录.IMO 这是一个糟糕的主意，但我只是提到它是因为它是一种可能性.缺点:每次我想编辑主要事件(例如:我想将事件从每 7 天"更改为每 9 天")时，我都需要更改事件.不过，例外"(更改单个实例)更容易.
2. Another idea is to store every event as a separate record. IMO it's a terrible idea, but I just mentioning it because it's a possibility. Disadvantages: every time I want to edit the main event (e.g: I want to change the event from occurring "every 7 days" to "every 9 days") I need to change every single occurrence of the event. "Exceptions" (changing single instance) is easier, though.
SQL/NoSQL?比例细节
我在我的项目中使用 PostgreSQL，但我对 NoSQL 数据库有基本的了解，如果它们更适合此类问题，我可以使用它.
I'm using PostgreSQL in my project, but I have basic knowledge in NoSQL databases and if they are better suited for this kind of a problem, I can use it.
规模:假设我有 5000 个用户，每个用户平均每周有 150 个事件，其中 40% 可能是例外".因此，我想将这个系统设计得更高效.
Scale: Let's say I have 5k users, and each will have on average 150 events/week, 40% of which can be "exceptions". Therefore I want to design this system to be efficient.
类似问题&其他资源
我刚刚开始阅读 Martin Fowler 的日历的重复事件"(http://martinfowler.com/apsupp/recurring.pdf)，但我不确定它是否适用于我的问题，如果适用，如何根据本文档设计数据库模式(欢迎提出建议).
I've just started reading Martin Fowler's "Recurring Events for Calendars" (http://martinfowler.com/apsupp/recurring.pdf) but I'm not sure if it applies to my problem and if so, how one would design database schema according to this document (suggestions are welcome).
有类似的问题，但我没有看到任何提及异常"(更改 1 个事件实例而不影响其他事件实例)，但也许有人会发现这些链接很有用:
There are similar questions, but I didn't see any mention of "exceptions" (changing 1 event instance without affecting other), but maybe someone will find these links useful:
抱歉，问题很长，我想很好地描述问题.然而，我觉得这很混乱，所以如果你有其他问题，我会很乐意提供更多细节.同样，我想听听可能的数据库/模式设计理念以及任何其他建议.谢谢！
Sorry for a long question, I wanted to describe the problem well. Yet, I feel that's pretty chaotic, so if you have additional questions, I will happily provide more details. Again, I'd like to hear about possible database/schema design ideas plus any other suggestions. Thank you!
推荐答案
使用 iCalendar RRules 和 ExDates
Use iCalendar RRules and ExDates
如果是重复事件，只需存储该事件的开始/结束日期时间以及 RRules 和 ExDates.
If it's a recurring event, just store the start/end datetimes and RRules and ExDates for the event.
使用物化视图预先计算即将发生的实际事件，例如未来 30 天或 365 天.
Use a Materialized View to pre-calculate upcoming actual events, say for the next 30 days or 365 days.
当您使用 Postgres 时，您可以使用现有的 python、perl 或 javascript RRule 库(例如 dateutil) 在 pg 函数中，用于根据 rrules 和 exdates 计算未来事件
As you are using Postgres, you can use existing python, perl, or javascript RRule libraries (such as dateutil) inside pg function for calculating future events based on the rrules and exdates
更新:查看 pg_rrule 扩展:https://github.com/petropavel13/pg_rrule
UPDATE: check out pg_rrule extension: https://github.com/petropavel13/pg_rrule
这篇关于重复性事件的数据库设计与异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
