带异常的重复事件的数据库设计 [英] Database design for recurring events with exceptions

问题描述

我正在建立一个需要储存/管理不同类型活动的系统。为了简单起见，我将专注于设计一个日历（我建立的东西略有不同，但日历是一个很好的类比，很容易推理）。我想了解可能的数据库/模式设计思路。

问题描述

我有一个日历与不同类型的事件（为了简单起见，说只有一种类型的事件：任务）。用户可以为特定日期添加新活动，编辑（更改某些详细信息，如标题或移至其他日期）或删除。可以有一次性事件和周期性事件（具有不同类型的复发：每X天，每月的第15天，星期一的每周;有点像简单的cron）。当用户移动重复事件时，此事件的所有其他实例以相同的方式移动（例如：+3天）。重要部分：重复事件可能有异常。因此，例如，假设我有一个每7天重复的重复事件A.但我想改变它的下一个星期的日期，所以，而不是星期二，它被分配到星期五，之后它仍将发生在星期二。

此外，每个重复事件都可以有额外的信息，只与1个特定的事件相关。实例，例如：我有相同的重复事件A每7天重复一次，我想为这个星期的实例添加一个说X的注释，并且我想为下一个月的事件添加另一个注释Y -

$ b 与常规的一次性事件是相当直接的，所以我不会讨论，只关注重复事件。

1。一个可能的解决方案是类似于OOP：我可以有一个事件class包含 start_date ， end_date （可以 null ）， recurrence_type （类似枚举的可能值 EVERY_X_DAYS ， DAY_OF_WEEK ， DAY_OF_MONTH ）和 recurrence_value （例如 code>）。当用户添加新的重复事件时，我只是在数据库中创建这样的 Event 。当用户想要更改此事件的1个事件时，我向类型/类 MovedEvent 的DB添加继承从事件不同的日期，并有指向 ID （或<$ c）的附加字段 related_to $ c> UUID ，如果你愿意的话）与它相关的 Event 。但同时，我需要跟踪所有 MovedEvent （否则我会在同一周显示2个事件），所以我需要有一个指向所有 MovedEvent 的 $ moved_events 。
缺点：每次我想显示日历时，我需要获取事件，并从 move_events ，如果我有很多已移动的活动，这不是最佳的。

2。想法是将每个事件存储为单独的记录。 IMO这是一个可怕的想法，但我只是提到它，因为它是一种可能性。 缺点：每次我想编辑主要事件（例如：我想将事件从每7天更改为每9天），我需要更改每次发生的事件。 异常（更改单个实例）更容易。

SQL / NoSQL？比例细节

我在我的项目中使用PostgreSQL，但我有NoSQL数据库的基本知识，如果他们更适合这种问题，我可以使用它。

规模：假设我有5k个用户，每个平均有150个事件/周，其中40％可以是异常 。因此，我想设计这个系统是高效的。

类似问题&其他资源

我刚刚开始阅读Martin Fowler的日历周期活动（ http://martinfowler.com/apsupp/recurring.pdf ）但我不知道它是否适用于我的问题，如果是这样，如何设计数据库

有类似的问题，但我没有看到任何提到的异常（更改1个事件实例而不影响其他），但也许有人会发现这些链接有用：

• 设计问题：如何设计周期性事件系统？




• 具有重复性的数据库的最佳设计




• 重复任务的设计选项




• 日历循环/重复事件 - 最佳存储方法




• 表示周期性事件的最佳方式是什么？

对不起，很长的问题，我想很好地描述问题。然而，我觉得很混乱，所以如果你有其他问题，我会很乐意提供更多的细节。再次，我想听听可能的数据库/模式设计思想以及任何其他建议。

解决方案

使用 iCalendar RRules和ExRules。

如果是重复事件，只需存储事件的开始/结束数据时间以及RRules和ExRules。 / p>

使用物化视图预计未来30天或365天内的实际事件。

由于您正在使用Postgres，您可以使用现有的python，perl或javascript RRule库（例如 dateutil ）inside pg函数用于根据规则和规则计算未来事件

更新：检出pg_rrule扩展： https://github.com/petropavel13/pg_rrule

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.

Problem Description

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.

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.

Ideas

System with regular, one-time events is pretty straightforward so I won't discuss that and focus only on recurring 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. 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? Scale details

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.

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.

Similar Questions & Other Resources

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

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:

• Design question: How would you design a recurring event system?

• Optimal design for a Database with recurring event

• Design option for 'recurring tasks'

• Calendar Recurring/Repeating Events - Best Storage Method

• What is the best way to represent "Recurring Events" in database?

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!

解决方案

Use iCalendar RRules and ExRules.

If it's a recurring event, just store the start/end datetimes and RRules and ExRules for the event.

Use a Materialized View to pre-calculate upcoming actual events, say for the next 30 days or 365 days.

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 exrules

UPDATE: check out pg_rrule extension: https://github.com/petropavel13/pg_rrule

这篇关于带异常的重复事件的数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！