牛逼了！万字长文解析谷歌日历的数据库是怎么设计的！

引言

这是一篇数据库设计教程，本教程将向大家展示如何为一个非常复杂的实际项目设计数据库表。

我们将设计一个 Google 日历的克隆版，尽可能全面地对与日历直接相关的功能进行建模。

本教程展示了即将于 2024 年夏天出版的《使用极简建模的数据库设计》一书中的数据库设计方法。您可以访问该书的在线网站 https://databasedesignbook.com/，留下电子邮件地址以接收有关该书和相关资料的最新消息。

首先我们会构建一个完整的逻辑模型来描述要存储的日历数据。这将占据大部分内容 (约 80％ 的文字)。完成逻辑模型后，我们将直接基于它来设计数据库表。

适用读者

本教程旨在帮助您从对需求的模糊想法 (例如：“我需要建立一个网站来管理健身房的日程安排和教练预约”)，到完整定义数据库表。

前四分之三的内容只需要对数据库及其信息存储方式有基本了解。第 1-6 部分只讨论逻辑模型，这些模型不依赖于你使用的特定数据库 (如 MySQL、PostgreSQL、其他传统关系型数据库、NoSQL 解决方案、云数据库等)。因此，大部分内容描述的是如何对业务需求进行建模。

最后四分之一的内容展示了如何从逻辑模型转化为实际数据表结构。这部分并非面面俱到，只展示了数据库表设计的众多可能方法之一。不过，这种方法对于要求不高的系统来说已经足够。许多现有系统也部分采用这种策略进行设计。这部分内容需要您对常见数据库有更多了解：如何创建表、有哪些物理数据类型、什么是主键和索引、如何查询表以及如何插入和更新数据。

本书的方法

通常情况下，大家一般都是一开始就直接设计表，但我们采取了不同的方法。本教程面向数据库设计新手，旨在回答以下几个重要问题：

• 从哪里开始设计？
• 如何确保没有遗漏任何内容？
• 如何就数据库设计寻求反馈？
• 如何修复设计错误？

我们首先创建一个用简单表格形式编写的逻辑模型。我们使用简短的格式化语句来定义数据属性和实体之间的关系。这有助于确保逻辑模型与实际业务需求一致。逻辑模型不依赖于特定的数据库实现方式。

第二步是在确定逻辑模型后设计物理表。这个过程非常直接：逻辑模型的每个元素都会对应一个表或列。物理模型可以根据需要依赖于特定的数据库实现。

问题描述

我们将实现 Google 日历的大部分功能。虽然会略过一些功能，但我们会尽可能实现所有日历功能。有些领域我们只会实现到足以讨论更有趣部分的程度。最后，您将能够通过相同的过程向架构添加缺失的功能。

Google 日历是一个多用户系统。例如，用户可以与他人共享事件。我们只会实现最基本的用户相关数据。

事件是 Google 日历的核心，我们将尽可能贴近真实情况来设计它们。事件包含标题、描述等基本信息，还有地点等次要属性。

日历事件中最复杂的部分是时间和日期设置：

• 分为 “全天” 事件和特定时间事件；
• 两种事件都可以设置重复或不重复；
• 全天事件：
  • 可以跨越多天；
• 特定时间事件：
  • 可以设置时区；
  • 有开始和结束时间；
  • 开始和结束时间可能跨越不同日期；
  • 开始和结束时间可以在不同时区；
• 两种事件都可以：
  • 每隔 N 天重复；
  • 每周重复，可选择一周中的某几天；也可以每隔几周重复；
  • 每月重复，可选择每月某天或某周几；
  • 每年重复；
  • 可以设置永久重复、重复到某日期、或重复特定次数；

对于重复事件，你可以：

• 调整某次具体事件的日期/时间
• 删除某次具体事件，比如取消某次每周例会

即使部分事件已经发生，你仍可以更改整个重复计划。例如，把每周二四的两次项目会议改为每两周一次的周五会议。

以下是日事件编辑表单的截图：

第 1 部分：基本全天事件

实体

首先我们需要确定所谓的 “实体”。实体通常是名词，如 “用户” 和 “事件”。

实体的特点是可以被计数，比如 “没有用户”、“一个用户”、“两个用户” 等。另一个特征是可以被添加，如 “在数据库中添加一条用户记录”。

在简单情况下，实体非常容易识别，但在复杂情况下可能不那么明显。为了熟悉这个过程，我们会把最明显的实体也写下来。

首先想到的两个实体是：

实体主要用于处理 ID 和计数，具体数据由下一节讨论的属性来处理。

例如，我们的数据库表中可能有 ID 为 23 的用户，ID 为 100 的全天事件等。

我们暂时不讨论最后一列 (“物理表”)，会在后面的 “创建 SQL 表” 部分介绍物理模型。

为了验证是否找到了正确的实体，我们可以用实体名称构造句子。如果句子通顺有意义，那就是一个有效的实体。示例句子：

• “我们的数据库中有 200 个用户”；
• “提交这个表单后，数据库会新增一个用户”；

对于全天事件，我们也可以构造类似的句子：

• “我们的数据库中有 3000 个全天事件”；
• “点击这个按钮时，会创建一个新的全天事件”；

这些句子在处理更复杂的情况时会很有用。如果这些句子没有意义，那么它可能是一个属性：

• “我们的数据库中有 400 个价格” (这句话不合理)；
• “当提交这个表单时，一个新的价格被添加到数据库中” (这句话不合理)

用户的属性

属性存储了关于实体的实际信息。

我们应该为用户建模哪些数据？用户数据在各种系统中都很常见，不同的系统可能需要存储大量关于用户的信息。在这篇文章中，我们只会对用户数据的最基本部分——电子邮件进行建模。

我们在这里可以看到什么：

• 这个属性属于在上一节中定义的用户实体；
• 我们使用问题来描述各种属性的特征。稍后我们将讨论为什么我们更喜欢这种风格，而不是 “用户的电子邮件” 之类的表述；
• 逻辑类型非常简单。如果你期望在这里看到 “‘VARCHAR (128) ‘” 之类的内容，那么答案是否定的，我们稍后会讨论这个；
• 我们展示了一个示例值，这有助于我们确认我们的想法。再次强调，在简单的情况下这是非常明显的，但它可以帮助审阅者确认每个人都达成一致。
• 我们暂时不会处理最后两列，我们稍后会在本文中讨论物理模型。

我们稍后会看到更多逻辑类型的例子。我们在书中详细讨论了逻辑类型。

让我们稍微提前看一下关于用户的表格数据是什么样子的。我们将使用一个简单的策略来设计物理表，所以结果将完全不令人惊讶：

这只是展示最终结果的一部分，让你知道我们的目标是什么。这里，ID 为 2 的用户的电子邮件是 “cjdate＠example.org”，ID 为 3 的用户的电子邮件是 “someone＠else.com”。

除此之外，我们不会详细讨论用户。

日程事件的属性

假设我们想安排一个从 2024 年 1 月 14 日开始的为期两天的公司团建。就实体而言，这将是一个日程事件。

根据上面的描述，我们可以看到我们需要存储以下关于日程事件的数据：

• 事件的名称；
• 事件的开始日期和结束日期

让我们在表格中记录这些信息：

我们可以观察到以下几点：

• 我们定义了前三个属性；
• 我们没有给属性设置简短的名称，这可能会给我们带来一些不便。我们期望有类似 “DayEvent＿name” 这样的标识，以便在文本后面引用该属性。我们稍后会回到这个话题。
• 我们引入了一个新的逻辑类型：日期。在本节中我们不需要处理时区问题。
• 对于实际日历中的大多数事件，开始日期和结束日期可能是相同的 (大多数事件是单日事件)。我们将在两个属性中存储相同的日期。这允许我们将特殊情况 (单日事件) 作为一般情况 (多日事件) 来处理。这是一种通用的设计策略，但我们稍后会研究这种思路是否总是适用。

链接

我们在哪里存储特定用户创建了特定 DayEvent 的信息？乍一看，这可能看起来像是 DayEvent 的一个属性，对吧？(实际上，不是)

属性不能包含 ID (标识符)。相反，当涉及两个实体时，我们需要使用链接。

链接有助于为许多复杂场景确定正确的设计。要描述链接，你需要写下两个句子。如果这些句子没有意义或与业务现实不符，你就避免了一个设计错误。

我们在这个表中看到什么：

• 链接连接两个实体。实体可以不同，就像这里一样，也可以相同。 例如，“员工是另一个员工的经理” 就是这种链接的一个例子。
• 为了指定多重性，我们使用一种带有 “＜” 字符的自定义表示法 (其他可能性包括：“—”、“＝” 和 “＞”)。这与 “1:N” 相同，但还可以让你指定哪个实体是 “只有一个” 的，哪个是 “多个” 的。
• 我们使用两个规范化的句子，其中包含两个实体、一个动词和关于多重性的信息。这些句子让我们能够验证和记录我们的设计。
• 我们在单独的列中再次以更常见的方式写下多重性。确定多重性至关重要，所以我们对此非常重视。

(当然，我们稍后会更详细地讨论这些链接。)

物理模型预览

再次跳到前面：如果我们现在停下来，试图为到目前为止的模式写下物理设计，这就是我们将看到的。这只是为了确认我们正朝着熟悉的方向前进。

我们将在 “创建 SQL 表” 部分稍后讨论物理模型。

第二部分：时间事件

在上一节中，我们讨论了基本的非重复日期事件。来看看我们的建模方法是如何处理时间事件的。

我们将日期事件建模为具有以下属性的 DayEvent 实体：

• 这个 DayEvent 的名称是什么？
• DayEvent 的开始时间是什么？
• DayEvent 的结束时间是什么？

此外，我们还定义了用户和 DayEvent 之间的链接：“用户创建多个 DayEvents”

让我们快速草拟一下时间事件，看看它与日期事件有何不同。引用 “问题描述” 部分：

• “时间事件：
  • 可以有关联的时区；
  • 有开始和结束时间；
  • 开始和结束时间可以发生在不同的日期；
  • 开始和结束时间可以在不同的时区；“

时区

每个国家和地区都采用一个或多个时区。时区定义偶尔会发生变化。每个国家作为主权国家，可以决定改变其时区定义。

时区可能使用夏令时，也可能保持统一。新的时区可能会被引入或废除。在本文中，我们不会深入探讨处理时区定义的复杂性。如果你真的要实施一个严肃的全球日历解决方案，你可能需要一个专门的团队来处理这些问题。

然而，在本教程中，我们将实现完全支持时区的事件，这在实践中是可用的。

我们有一个生动的例子：飞机票。飞机经常跨越时区边界，你的机票上的起飞和降落时间会在不同的时区。比如，有一个从阿姆斯特丹到伦敦的航班，于 12 月 24 日 16：50 (阿姆斯特丹时间) 起飞，17：05 (伦敦时间) 降落。因此，飞行时长为 1 小时 15 分钟。

时区激发了许多程序员之间流传的故事。有许多博客文章、恐怖故事、“每个程序员都应该知道的事情” 和其他与时区相关的文本，特别是在数据库上下文中。此外，许多系统在夏令时转换期间以各种方式出现故障。我们将只讨论我们目的所需的内容，并简要提及一些需要考虑的重要事项。

实体

话虽如此，我们似乎需要添加两个实体：

世界上有几十个时区。我们可以通过写下示例句子来确认时区实体的有效性：

• “我们的数据库中存储了 120 个时区”；
• “当这个导入脚本完成时，一个新的时区被添加到我们的数据库中”

(时区数据结构在下面讨论)

时间事件的句子也很直接：

• “我们的数据库中有 2500 个时间事件”；
• “当点击这个按钮时，一个新的时间事件被创建”；

时区的属性

就本文而言，我们只会对时区进行非常简单的建模。基本上，我们只会引入一个属性：

我们不会深入讨论时区实际是如何定义的。我们假设有一个配套的逻辑模型来描述时区的结构。此外，我们假设有一个函数，它接受指定时区的本地时间并返回 UTC 时间，或将 UTC 时间转换为指定时区的本地时间。这将在下一节中详细讨论，届时我们将谈论重复事件。

为清晰起见，以下是时区定义中还应包含的内容：

• 该时区的 UTC 偏移量是多少？
• 该时区是否有夏令时？
• 夏令时何时开始，何时结束？
• 夏令时生效时的 UTC 偏移量是多少？
• 我们还需要对时区的先前定义进行建模。例如，政府可能决定更改夏令时生效的日期，或取消夏令时等
• 该时区是否仍在使用或已废弃？

这是一个不完整的列表。使用我们的方法对所有这些数据进行建模是可能的，但这是一个单独的、相当技术性的练习。

让我们回到事件。

TimeEvent 的属性

注意，我们在这里使用的是本地时间。你可能读到过，时间应该以 UTC 时间 (不带任何时区) 存储，然后使用首选时区格式化以供人类阅读。

这里我们面临不同的情况。时区可能会发生变化。假设我们安排了一场台球赛，定于 2058 年 9 月 6 日，科隆当地时间 09：30 到 11：00。目前我们不知道那时该时区的 UTC 偏移量会是多少。这样做的好处是，我们必须按用户输入的原样存储数据，然后随着当地法规的变化进行调整。

链接

我们这里有两个非常相似的链接

这两个链接的定义仅在一个词上有所不同 (“开始” 与 “结束”)

大多数时间事件在开始和结束时间都会使用相同的时区。我们将其设计为通用情况：即使时区相同，我们也总是同时指定两者。这种方法将有助于我们习惯处理更复杂的情况。

日期事件和时间事件的相似之处

全天事件和时间事件看起来很相似。是否有必要考虑将它们统一？

例如，两种事件都有名称。此外，“日期” 和 “日期时间” 之间有多大区别？我们还可以观察到，这两种类型的事件会有更多共同的数据，如 “地点”、受邀嘉宾列表、“描述” 等。也许我们可以将这些提取到这两个 anchor (锚点) 之间共享的某个组件中。

幸运的是，逻辑建模允许我们在做出决定之前稍作等待。毕竟，这只是一些简单的表格，我们可以在确定物理表实现之前重新调整。

不过，目前我们想收集更多关于我们迄今为止看到的两种类型事件的相似性 (更重要的是，差异性) 的信息。此外，我们还想看看是否会有更多类型的事件，以及它们有哪些属性和链接。

第 3 部分：重复的全天事件

正如我们可能记得的初始问题描述：

“全天事件和时间事件都：

• 可以每天重复，或每 N 天重复一次；
• 可以每周重复，在一周中的某些天；同样，它可以每两周或更多周重复一次；
• 可以每月重复，在某一天或某一周的某一天；
• 可以每年重复；
• 重复事件可以无限期进行，或持续到某个特定日期，或重复特定次数；“

在本节中，我们将只讨论全天事件。稍后我们将看到最小建模方法如何处理不同 anchor 之间的共性，在这种情况下是时间事件。此外，我们还将看到逻辑模式是如何变化的：我们将以此为例，说明在引入更好的设计方法时如何编辑设计草案。请记住，所有这些都发生在我们开始考虑数据库表之前，所以这是一个非常顺畅的过程：你不需要担心表的迁移问题。

如果你考虑上面列出的要点，你的反应可能是：“我们可能需要使用 JSON 来处理这个问题。” 这可能是对的，但 JSON 属于物理表设计，所以我们现在不会讨论这一点。我们将在逻辑层面设计所有需要的内容，之后再看看有哪些物理选项。

属性#1，频率

让我们问第一个问题，希望它能帮助我们找到一个属性：“这个事件多久重复一次？” 看看事件编辑表单，我们可以看到这个问题的可能答案：a)从不；b)每天；c)每周；d)每月；e)每年。

我们说这样的属性具有 “多选一” 类型。让我们把它写下来作为一个属性：

对于多选一属性，我们在 “示例值” 列中显示所有可能值的完整列表。

对于从不重复的事件我们该怎么办？在逻辑层面，一个属性要么被设置为特定值，要么不设置：这是最小建模的基本原则。所以如果这个属性没有设置，那么这个事件就不会重复。

属性#2，关联属性

继续阅读，我们看到对于所有四种频率，还有一种额外的可能性。事件可以每 N 天、每 N 周、每 N 月和每 N 年重复一次。我们把它写下来：

这是我们第一个关联属性的例子。它的值只有在另一个属性被设置时才有意义。我们通过在问题中添加 “对于重复事件而言：” 来指定这一点。

注意，这只是一个人类可读的表示法，我们不会在这里讨论如何编写机器可读的逻辑模式。

属性#3

当你指定一个每月事件时，你有两个选项：每月固定日期重复 (比如每月 16 日)，或在每月相同的星期几重复 (比如每月第二个星期二)。基准日期取自前面讨论过的普通 “DayEvent 何时开始” 属性。

我们来尝试定义这个属性：

星期几：微实体

事件可以每周重复，在一周的特定几天。例如，我们可以有一个每周在周一、周三和周五重复的事件。我们在哪里存储这个信息？

我们可以从一个这样的属性开始：

这种方法可行吗？现代数据库系统如 Postgres 和 MySQL 原生支持存储数组：Postgres 有一个数组类型，它们都有 JSON 类型，所以你可以在单个表列中存储整个数组。尽管我们讨论的是逻辑层面，这种类比至少可以帮助确认这种方法的合理性。此外，当我们讨论物理表模式时，我们完全有可能决定以这种方式存储该信息。

然而，极简建模采用的方法要求我们引入一个名为 DayOfTheWeek 的新实体。

这是一个常见实体的例子，类似于货币、语言和国家。我们在 https://minimalmodeling.substack.com/p/currencies-countries-and-languages 中讨论了众所周知的实体。

DayOfTheWeek (星期) 这个实体看起来可能有点奇怪，因为它非常小。这里只有七个可能的 ID，而且永远不会增加新的。此外，这些 ID 不同寻常，因为它们是字符串，而不是常用的整数。但引入它有助于保持建模方法的简单性。

你还可以为这个实体引入一些属性，比如星期的人类可读名称。这留给读者作为练习。

现在回到我们的任务。我们有两个实体：DayEvent (日事件) 和 DayOfTheWeek (星期)。要连接它们，我们需要一个链接。

这是一个相当正常的链接，但是相互关联的，就像我们之前看到的属性一样。这个链接只有在对应于每周重复的 DayEvent 时才有意义。

建模完成了吗

我们如何确保建模已经完成？如果没有完成怎么办？

让我们重新审视一下原始需求，并突出显示我们已经涵盖的部分：

“既包括全天事件，也包括基于时间的事件：

• 可以每天重复，或每 N 天重复；
• 可以每周重复，在某些星期；同样，它可以每两周或更多周重复；
• 可以每月重复，在某一天或某个星期；
• 可以每年重复；
• 重复事件可以永远持续，直到某个特定日期，或重复特定次数；“

好的，现在我们可以看到我们忘记了事件重复的次数。需求中相应的部分没有被标记。让我们来修复这个问题。

重复限制：更多相互关联的属性

“重复性事件重复多长时间？” 这看起来是一个合理的句子来定义属性。有三种可能的答案：“永远”、“直到特定日期” 和 “特定重复次数”。

现在我们可以添加最后两个缺失的部分：具体日期和重复次数。

完事。

现在快速总结一下这里定义的数据片段：

• 锚点：DayEvent
  • 属性：(直到某个日期) 重复何时结束？
  • 属性：(重复特定次数) 重复多少次？
  • 属性：(对于每月重复) 它在哪一天发生？
  • 链接：(对于每周重复) 发生在某些 DaysOfTheWeek；
  • 属性：重复频率是多少？
  • 属性：重复的步长是多少？
  • 属性：重复持续多长时间？
• 锚点：DayOfTheWeek

一个额外的锚点；六个属性，其中一些相互交织；一个链接。

第 4 部分：渲染日历页面

到目前为止，我们讨论的是日历的记账部分。我们对整个事件系列只有一条记录。十次每周项目状态会议对应一条单一的数据库记录。我们朋友无限次数的生日对应一条单一的数据库记录。

让我们回到我们正在开发的应用程序：一个日历。我们需要显示用户日历的每周视图：比如说，从 2 月 26 日开始到 3 月 3 日的七天。我们需要在那一页上显示哪些事件？假设那周有一次每周项目状态会议 (共十次中的一次)。如果生日 (年度事件) 落在那周，我们需要显示它。

所以，我们需要编写某种 SQL 查询，看起来像这样的伪代码：

SELECT …
FROM …
WHERE <date> BETWEEN ‘2024-02-26’ AND ‘2024-03-26’;

这可能需要多个 SQL 查询，甚至是一些编程语言的代码。我们目前考虑的数据结构相当复杂。要找出某一周需要显示的事件，你需要考虑很多因素。这可能很快就变得不切实际。

关于节奏的说明

我花了几个月的时间来写这一章。我一直在深入思考这个问题很多。我对我心目中的最终状态已经有了相当清晰的理解，只需要把它写下来。

但如果我现在就向你展示完整的表格设计，对于我们学习数据库设计这个目标来说并不有用。如果我直接向你展示最终的表格设计，你可能无法理解我做出这些决定的原因。

同时，我也不想展示太多细小的渐进式变化，以免文章过长。所以我们需要找个折中的方案。

我们从如何渲染周视图这个问题开始讨论。现在让我们考虑日历应用的另一个必备功能：修改或取消系列事件中的某些事件。比如说，你安排了十次每周项目会议，但因为某天天气特别好，你想取消其中一次。我们之前设计的数据模型不需要改变，但我们需要为显示和修改功能增加一些新的锚点、属性和关联。

总体思路

我们要引入一个新的锚点，用来存储系列中每个具体事件的信息。比如说，如果我们有 10 次每周项目例会，我们就会在某个表中创建十行数据。每条记录对应一个具体日期 (如 2024-02-12、2024-02-19 等)。

这样做有两个好处：

1. 这会使渲染变得非常简单。你可以轻松找出某天要发生的所有事件。
2. 这允许我们重新安排或取消系列中的某些事件。比如原定每周四 12：00 开会，某周想改到 14：00 (甚至换个日子)，这都不是问题。而且不会影响我们之前定义的 TimeEvent 锚点中的原始数据。

另外，如果只想跳过某一次例会，我们可以直接把那天标记为 “跳过”。

日期槽位

首先得给这些东西起个名字，这有时候还真不容易。就在五分钟前喝茶时，我突然想到可以用 “槽位” (slot) 这个词。

和之前一样，我们还是要把按天的事件和按时间的事件分开处理。所以我们会有 DaySlot (日期槽位) 和 TimeSlot (时间槽位) 两个锚点。我们先来看看日期槽位：

DaySlot 只需要很少的几个属性就够了：

注意，用户可以更改特定槽位的日期。这样我们就能轻松满足之前提到的需求了。

最后，我们还需要把 DaySlot 和对应的 DayEvent 关联起来。

需要注意的几点：

1. 我们为每个 DayEvent 创建 DaySlot，包括非重复事件。这样做可以简化渲染代码。
2. “” 无限 “重复事件” 带来了一个有趣的问题。例如，添加朋友的年度生日时，应该创建多少个 DaySlots？一种方法是设定一个合理的期限 (如 100 年)，一次性创建所有时间段。另一种方法是按需创建，当用户查看远期日历时再生成相应的 DaySlots。
3. 每个时间段可能有不同的信息。比如，会议地点可能变化，参会人员可能增减，实际出席情况肯定会有差异。这些细节很容易建模，只需添加更多链接将 DaySlot 与其他相关信息连接即可。
4. 此外，日期计算需要格外小心。例如，2 月 29 日出生的人的生日如何处理？我们可能会禁止创建这样的事件，或让用户选择提前或延后一天。同样，每月 31 日的重复事件在短月也需要特殊处理。

练习：TimeSlots

给坚持不懈的读者的练习：思考如何为 TimeSlot 设计锚点、属性和链接。按照前面介绍的格式，填写相应的表格。别忘了考虑时区的影响。

设计时需要考虑多长远

有时候，考虑更多相关需求能帮助我们做出更好的设计。就像本章所做的：我们不仅考虑了页面渲染，还考虑了修改系列事件中个别事件的需求。

有时候，独立考虑各个需求也能带来更好的设计。在前面的章节中，我们分开处理了基于时间和基于日期的事件。

到目前为止，我们只关注已知的需求，没有考虑假设的未来需求。我们是否应该预想一些可能有用的功能，并提前设计呢？只考虑已知需求可能会导致设计过于局限。

另一方面，有时人们会考虑一些实际上永远不会用到的功能。这样可能会引入不必要的复杂性，增加开发和维护的难度。

在设计时，我们总是面临着这两种倾向的风险。要找到平衡并不容易，需要经验和判断力。基于极简建模的逻辑设计坚持 “只建模必要部分” 的原则。我们之所以能这样做，是因为极简建模将需求变化视为常态。同时，极简建模也有意避免引入过多抽象概念。

我们将在本书后续章节中深入探讨物理设计的这一方面。我们会介绍 “Game of Tables” (表格游戏) 的概念，以及 Date’s Demon (Date 的恶魔) 的思想。

第 5 部分：日历页面中时间事件的呈现

对于重复性的时间事件，我们采用与全天事件相同的处理方法。我们将引入一个名为“时间段（TimeSlot）”的锚点。时间段（TimeSlot）对应特定日期和时间的具体事件。一个重复事件可能对应多个时间段（TimeSlots）。

时间段可以像全天事件一样手动调整或取消。

以下是锚点定义：

时间段的属性如下：

单个时间段通常可以移动到不同日期，因此我们需要保留这一信息。对于开始/结束时间，我们应使用哪个时区？正如您可能还记得第 2 部分所提到的，在 Google 日历中，您可以为开始和结束时间设置不同的时区。仔细想想，为时间段保留这一特性也是有意义的。

此外，我们需要将时间段与时间事件关联起来，就像我们处理全天事件段/全天事件那样：

与全天事件段（DaySlot）一样，即使对于非重复的时间事件，我们也会创建时间段（TimeSlot）。

第 6 部分：目前完整的逻辑模型

让我们回顾一下到目前为止我们设计的所有内容。这是一个数据库设计中的实体-关系模型。

首先，完整的实体类型列表（共 7 个）：

注意：表格中的空白单元格为原文所有。

其次，属性列表（按实体类型排序）：

第三，链接列表（无特定顺序）：

最后，这里是一个展示所有实体和链接（但不包括属性）的示意图：

第 7 部分 创建 SQL 表

在前面的章节中，我们已经定义了完整的逻辑模型，所以大部分工作实际上已经完成了。剩下的工作就相当简单明了了。

出于教学目的，我们将使用一种特定的表设计策略：“每个锚点对应一张表”。这是物理表设计中最常见的方法之一。还有几种其他可能的策略，我们会在书中讨论它们。

到目前为止，我们有 7 个锚点、21 个属性和 10 个链接。鉴于我们使用“每个锚点对应一张表”的策略，我们将会有 7 ＋ 2 ＝ 9 张表（锚点数量 ＋ M:N 链接数量），总共 21 ＋ 8 ＝ 29 个列（属性数量 ＋ 1:N 链接数量）。 如果我们的逻辑设计正确描述了业务需求，那么数据库表就会自动正确。我们将在书中讨论不断变化的需求。此外，我们还会讨论设计错误以及如何修复它们。

我们将重新审视上一节的表格，并填写我们的设计选择：

• 对于实体，填写“数据库表”列；
• 对于每个属性，填写“物理列”，并选择“物理类型”；
• 对于每个多对多关系，选择数据库表的名称；
• 对于每个一对多关系，在对应 N 端实体的表中填写列名；

实体：为数据库表选择名称

这里我们只是为每个表选择一个简单明了的复数名称

某些公司或应用程序可能会采用不同的命名规范（如单数形式、驼峰命名法等）。在这种情况下，你只需使用符合该规范的名称即可。

属性：选择列名和物理类型

对于物理列名，我们选择一些合理的名称。例如：

• day＿events.end＿date 将是 “When does the DayEvent begin?” 属性的列名；
• time＿slots.is＿skipped 将是 “Is this TimeSlot skipped?” 属性的列名。

依此类推。由于关系数据库的工作方式，你必须选择一个非常简短的名称作为列名。在许多情况下，这个名称本身不足以充分解释数据的含义。这是我们从逻辑模式开始，并使用更长的人类可读问题来定义属性语义的原因之一。

对于物理类型，我们选择一个合理的类型，不做过多讨论。这个主题在书中有广泛的介绍。书中还有每种逻辑类型的推荐数据类型列表，我们可以直接使用它。

如果你正在使用现有系统，可能需要为列选择替代的物理数据类型。例如，你的数据库服务器可能支持更适合的数据类型；或者可能有一些工程准则使你选择不同的数据类型。

我们将在书中讨论这种差异。然而，对所有物理设计问题的全面讨论远远超出了任何书籍的范围。这是你在职业生涯中需要不断学习的内容，而且会随着新技术的出现而变化。

在这个问题中，我们使用了约一半的逻辑属性类型，包括：

• 字符串：4 个属性；
• 日期：4 个属性；
• either/or/or（多选一）：3 个属性；
• 整数：2 个属性；
• 日期/时间（本地）：4 个属性；
• 是/否：2 个属性。

你可以看到，同一逻辑类型的属性的物理定义几乎相同。主要区别在于：a) 字符串的最大长度；b) ‘NULL‘ 与 ‘NOT NULL‘。

我们选择“NOT NULL”用于业务要求必须有值的属性。例如，事件名称或全天事件的开始日期。对于可选属性，我们选择允许空值的物理类型（“NULL”）。我们在书中讨论了可空性，但请注意，“NULL”仅存在于物理模式中。

与 NULL 类似，所谓的“哨兵值”（sentinel values）在逻辑建模中也不存在。

1:N 关系

对于 1：N 关系，我们在 N 端的锚点表中添加一列。例如：

选择列名通常很简单。唯一的复杂情况是当两个锚点之间存在两个或更多不同的关系时。我们在时区的情况下遇到了这种情况，我们将使用两个不同的列。

M:N 关系

对于 M：N 关系，我们必须为每个关系创建一个单独的中间表。每个这样的表结构几乎相同，只是列名不同。

我们只需为这样的表找一个好名字。没有一种适用于所有情况的命名方法，你需要尝试一些组合，寻求可读性。对于关系来说，这尤其困难，因为不清楚两个锚点中哪个更重要应该放在前面。

与属性一样，由于关系数据库的工作方式，表名需要相当简短。在许多情况下，仅凭名称本身不足以完全解释数据的含义。这是我们准备逻辑模式并使用人类可读的句子来定义关系语义的原因之一。

无论如何，这里是完整的关系表，其中包含为表和列选择的名称（见最后一列）。

最后：数据表

正如我们在上一节中提到的，我们将会有 8 个 SQL 表：6 个用于主要实体，2 个用于多对多关系。有一个主要实体（星期几）比较特殊，所以我们不为它创建物理表。我们使用了一种非常常见的物理表设计方法。其他方法也是可能的，但这个讨论超出了本文的范围。那么，让我们直接写下所有的表，并添加我们拥有的所有属性。

这个阶段的过程非常直接，甚至可能有些枯燥。以下是创建这些表的 SQL 语句：

CREATE TABLE users (
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(64) NOT NULL
);

CREATE TABLE timezones (
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(64) NOT NULL
);

CREATE TABLE day_events (
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  user_id INTEGER NOT NULL,
  name VARCHAR(128) NOT NULL,
  begin_date DATE NOT NULL,
  end_date DATE NOT NULL,
  repeated VARCHAR(24) NULL,
  repetition_step INTEGER NULL,
  repeated_monthly_on VARCHAR(24) NULL,
  repeated_until VARCHAR(24) NULL,
  repeated_until_date VARCHAR(24) NULL,
  repeated_reps INTEGER NULL
);

CREATE TABLE time_events (
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  user_id INTEGER NOT NULL,
  start_timezone_id INTEGER NOT NULL,
  end_timezone_id INTEGER NOT NULL,
  name VARCHAR(128) NOT NULL,
  begin_local_time DATETIME NOT NULL,
  end_local_time DATETIME NOT NULL
);

CREATE TABLE day_slots (
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  day_event_id INTEGER NOT NULL,
  the_date DATE NOT NULL,
  is_skipped TINYINT UNSIGNED NOT NULL DEFAULT 0
);

CREATE TABLE time_slots (
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  time_event_id INTEGER NOT NULL,
  begin_local_time DATETIME NOT NULL,
  end_local_time DATETIME NOT NULL,
  start_timezone_id INTEGER NOT NULL,
  end_timezone_id INTEGER NOT NULL,
  is_skipped TINYINT UNSIGNED NOT NULL DEFAULT 0
);

CREATE TABLE day_event_dows (
   day_event_id INTEGER NOT NULL,
   day_of_week VARCHAR(3) NOT NULL,
   PRIMARY KEY (day_event_id, day_of_week),
   KEY (day_of_week)
);

CREATE TABLE time_event_dows (
   time_event_id INTEGER NOT NULL,
   day_of_week VARCHAR(3) NOT NULL,
   PRIMARY KEY (time_event_id, day_of_week),
   KEY (day_of_week)
);

就是这样吗？大体上就是这样了。不过我们还需要聊聊索引和那些为了精简而略过的属性。

资深数据库开发人员一眼就能看出上面的模式缺少了一些显而易见的索引。比如说，day＿events.user＿id 这一列肯定得加索引。可惜的是，对于哪些列（或列组合）需要索引，并没有一个通用规则。这得看应用程序会怎么查询这些表。关于数据库索引，强烈推荐阅读 “Use The Index, Luke”[1] 这本书。我们的书中也会对索引进行更详细的讨论。

在讨论逻辑模式时（特别是在开始部分），我们跳过了一些属性，因为它们和其他属性太相似了。比如，我们可能想加上用户名，还有存储用户密码哈希值的列。有些数据元素对本文并无新意，像是事件地点或邀请名单。作为练习，你可以把我们没讨论到的、你感兴趣的元素加进去。在目录表里添加几行，填写每个单元格的内容，然后修改上面的模式定义，把漏掉的数据补上。

总结

以下是整个设计过程的简要概述：

• 首先，用自由格式文本描述你要解决的业务问题；
• 按上面说的方法列出锚点清单；可以用 Google Docs 之类的协作工具；
• 按上面说的方法列出属性清单，要特别注意问题；
• 按上面说的方法列出链接清单，要特别注意句子，因为它们能帮你确保基数正确；
• 如果可视化表示有助于你的思考，就根据逻辑模型创建图形模式；
• 填写物理模型：表名、列名、物理数据类型；
• 用上一步的信息，把 SQL 模式写成一串 CREATE TABLE 语句；
• 将模式提交到数据库服务器，修正任何错误后重新提交；
• 把逻辑模型分享给你的团队。

引用链接

[1]

“Use The Index, Luke”: https://use-the-index-luke.com/

独立开发最好的出路是出海，而出海的第一步就是学好英语！

如何学好英语？当然是看英文视频啦！英语基础薄弱的同学可以先从动画片看起，这里推荐一部超级好看的动画片叫《神奇校车》。神奇校车 (The Magic School Bus) 是一部美国学龄儿童教育动画片，故事围绕一位名叫瓦莱莎·芙里兹尔 (Valerie Frizzle) 的三年级老师和她的学生们展开。芙里兹尔老师开着一辆会变形的神奇校车，带领学生们进行一系列的科学探险。

这辆神奇校车会变成潜水艇、宇宙飞船、时光穿梭机等等，小朋友们也可能变大变小、变成各种动物，用各种科学、物理知识去探险。以新颖活泼、好玩易懂的形式，带领孩子们进入浩瀚的科学领域，畅游在地球科学、生物科学、太空科学、气象学、古生物学等学科中。