database 为日历应用程序布置数据库架构

Laying out a database schema for a calendar application

提问by Anthony D

I want to write a calendar application. It is really recurring items that throw a wrench in the works for the DB schema. I would love some input on how to organize this.

我想编写一个日历应用程序。它确实是经常出现的项目，这会给 DB 模式的工作带来麻烦。我希望就如何组织这个问题提供一些意见。

What if a user creates an event, and inputs that it repeats everyone Monday, forever? How could I store all that in the database? I can't create infinite events. Do I simply put a table in there that holds the relevant info so I can calculate where all the events go? If so, I would have to calculate them every time the user views a new part of the calendar. What if they page through the months, but they have a ton of recurring items?

如果用户创建一个事件，并输入它在每个星期一永远重复，会怎样？我如何将所有这些存储在数据库中？我无法创建无限事件。我是否只是在其中放了一张包含相关信息的表格，以便我可以计算出所有事件的去向？如果是这样，每次用户查看日历的新部分时，我都必须计算它们。如果他们翻阅几个月，但他们有大量的重复项目怎么办？

Also, the schema needs to handle when a user clicks an item and says "Edit this one in the sequence" not all items in the sequence. Do I then split the one item off of the sequence?

此外，当用户单击一个项目并说“在序列中编辑这个”而不是序列中的所有项目时，模式需要处理。然后我是否将一项从序列中分离出来？

Update 1

更新 1

I have not looked at iCal at all. To be clear, I think saving the info that allows you to calculate the recurring items, and splitting off any that differ from the sequence is a great way to store it to be able to transfer it. But I think that in an application, this would be too slow, to do the date math all over the place.

我根本没有看过 iCal。需要明确的是，我认为保存允许您计算重复项的信息，并分离出与序列不同的任何信息是存储它以便能够传输它的好方法。但是我认为在应用程序中，这太慢了，无法到处进行日期数学运算。

采纳答案by JasonV

I recently created a calendar application and this was one of the many challenges that I faced.

我最近创建了一个日历应用程序，这是我面临的众多挑战之一。

I eventually came up with a semi hack-ish solution. I created an event_typecolumn. In that column, I had either: daily, weekly, monthly, or yearly. I also had a start_dateand an end_datecolumns. Everything else was handled in the actual backend code.

我最终想出了一个半黑客的解决方案。我创建了一个event_type专栏。在此专栏中，我有两种：daily，weekly，monthly，或yearly。我也有一个start_date和一个end_date列。其他一切都在实际的后端代码中处理。

I never tried to split an event if a user edited only one event. It wasn't necessary in the situation. However, you could split an event by changing the end_date of the first, creating a new event with a new start_dateand the end_dateof the original, and finally, a new event for the one you just chose to edit. This process would end up creating 3 events.

如果用户只编辑一个事件，我从未尝试拆分事件。在这种情况下没有必要。但是，您可以通过更改第一个事件的 end_date 来拆分事件，使用新事件start_date和end_date原始事件创建一个新事件，最后为您刚刚选择编辑的事件创建一个新事件。此过程最终将创建 3 个事件。

Hack-ish, I know. I couldn't think of a clever way to handle this problem at the time.

我知道。我当时想不出一个聪明的方法来处理这个问题。

回答by Justin

I have been struggling with the same problem, and I was actually toying with the "cache table" idea suggested above, but then I came across an alternative (suggested here) that doesn't seem to have been represented yet.

我一直在为同样的问题苦苦挣扎，实际上我在玩弄上面建议的“缓存表”的想法，但后来我遇到了一个似乎还没有代表的替代方案（此处建议）。

Build a table containing all events

建立一个包含所有事件的表

EventID (primary key)
Description
StartDate
PeriodType - days, weeks, months, years
PeriodFreq - # of days, weeks, etc between events
EndDate
... other attributes that can be modified

Then add a table for exceptionsto these events. This table uses a composite key, made up of the EventID that maps to the event table, and an instance ID to pick the particular event in the series.

然后为这些事件的例外添加一个表。该表使用一个复合键，由映射到事件表的 EventID 和一个实例 ID 组成，用于选择系列中的特定事件。

EventID (key)
InstanceID (key)
InstanceDate - the modified date of the exception 
IsCancelled - a flag to skip this date when traversing the series
... other attributes that can be modified

It seems to keep the event table normalised, and avoids splitting up series to handle exceptions.

它似乎使事件表保持规范化，并避免拆分系列来处理异常。

回答by Brendorien

Why not use Google Calendar as a database for this calendar application by relying on Google Calendar's APIfor storing and retrieving calendar events?

为什么不使用 Google Calendar 作为此日历应用程序的数据库，依靠Google Calendar 的 API来存储和检索日历事件？

The Calendar API is a REST API that can be accessed through explicit HTTP calls; the API exposes most of the features available in the Google Calendar Web interface, so your calendar application can as much functionality as Google Calendar does (a lot of functionality!!!).

Calendar API 是一个可以通过显式 HTTP 调用访问的 REST API；该 API 公开了 Google 日历 Web 界面中可用的大部分功能，因此您的日历应用程序可以提供与 Google 日历一样多的功能（很多功能！！！）。

Your application need only implement OAuth 2.0 for Google APIs, which can be made easy using a single sign-on service like Auth0to provide the appropriate access tokens. Then, your calendar application can use these tokens in conjunction with the Calendar API to provide seamless storage and retrieval of calendar events in a JSON format.

您的应用程序只需要为 Google API 实现 OAuth 2.0，使用Auth0等单点登录服务提供适当的访问令牌可以轻松实现。然后，您的日历应用程序可以将这些令牌与日历 API 结合使用，以 JSON 格式提供日历事件的无缝存储和检索。

Users create events within their own "New Calendar." This calendar is shared with you in the form of a gmail account dedicated to this application - the application's gmail account.

用户在他们自己的“新日历”中创建事件。此日历以专用于此应用程序的 gmail 帐户的形式与您共享 -应用程序的 gmail 帐户。

Basically, Google Calendar becomes your database, whereby you can have the application's gmail accountnot only store all of your application's events, but also allow you to view and edit these events with an intuitive interface.

基本上，Google 日历成为您的数据库，您可以通过该应用程序的 gmail 帐户不仅存储您应用程序的所有事件，还允许您使用直观的界面查看和编辑这些事件。

回答by ChristianLinnell

Hold the recurring item in the events table as normal, but flagged as recurring with the appropriate start/ end dates.

像往常一样将重复项目保留在事件表中，但标记为带有适当开始/结束日期的重复项目。

If the user modifies a single instance of the appointment, just create a new event, perhaps with a 'parentId' equal to the recurring event's id.

如果用户修改了约会的单个实例，只需创建一个新事件，可能“parentId”等于重复事件的 id。

Build logic that causes the calendar to override any recurring events on a particular day with events with matching parent IDs.

构建逻辑，使日历使用具有匹配父 ID 的事件覆盖特定日期的任何重复事件。

Your question about performance is basically the old speed vs. storage issue. I really don't think the calculation required would exceed the space requirement for storing so many appointments. Just read up on database optimization- indexing etc.

您关于性能的问题基本上是旧的速度与存储问题。我真的不认为所需的计算会超过存储这么多约会的空间要求。只需阅读数据库优化 - 索引等。

回答by Ben Dunlap

Could you bridge the two worlds with a "cache" table, in which you pre-compute the next X days worth of events?

你能用一个“缓存”表来桥接这两个世界，你可以在其中预先计算接下来 X 天的事件吗？

So three tables:

所以三个表：

recurring_event_specs
one_time_events
cached_recurring_events

For any part of the calendar within X days of today, your query will UNION one_time_eventsand cached_recurring_events.

对于今天 X 天内日历的任何部分，您的查询将为 UNIONone_time_events和cached_recurring_events。

Then you would only have to do on-the-fly date calculations if the user tried to look at a part of the calendar more than X days in the future. I imagine you could find a sane X that would cover the majority of normal use.

然后，如果用户在未来 X 天之后尝试查看日历的一部分，则您只需进行动态日期计算。我想你可以找到一个可以覆盖大部分正常使用的理智的 X。

The cached_recurring_eventstable would need to be updated whenever a user adds a new recurring event -- and possibly once a day offline, by a cron-job/scheduled-task. But only on days when no new recurring event has been created.

cached_recurring_events每当用户添加新的重复事件时，都需要更新该表 - 并且可能每天离线一次，通过 cron-job/scheduled-task。但仅在没有创建新的重复性事件的日子里。

回答by Daniel Bardi

The best way to do this is to store a standards based recurrence pattern string (iCal).. and leave blank if it's a single event. There are a few APIs that can parse the recurrence pattern and create event objects that you can bind to UI elements.... none of the occurrences need ever be stored in the database, only the initial event (occurrence)..

执行此操作的最佳方法是存储基于标准的重复模式字符串 (iCal)。如果它是单个事件，则留空。有一些 API 可以解析重复模式并创建可以绑定到 UI 元素的事件对象......没有任何事件需要存储在数据库中，只有初始事件（发生）。

回答by Pepster

Couldn't you store the events per day with start and end time? It will generate a lot of data for events that happen everyday (maybe go non-relational for this) but it will make querying easier and it will be possible to make exceptions (f.e. the event place burned down, or employees are striking). To generate the days for the event I would suggest to implement that in the front-end derived on some ICal-ish pattern.

你不能用开始和结束时间存储每天的事件吗？它将为每天发生的事件生成大量数据（可能与此无关），但它会使查询更容易，并且可能会出现异常（例如，事件发生的地点被烧毁，或员工罢工）。为了生成事件的天数，我建议在基于某些 ICal 模式派生的前端中实现它。