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

👉这是一个或许对你有用的开源项目

国产 Star 破 10w+ 的开源项目，前端包括管理后台 + 微信小程序，后端支持单体和微服务架构。

功能涵盖 RBAC 权限、SaaS 多租户、数据权限、商城、支付、工作流、大屏报表、微信公众号、CRM 等等功能：

• Boot 仓库：https://gitee.com/zhijiantianya/ruoyi-vue-pro
• Cloud 仓库：https://gitee.com/zhijiantianya/yudao-cloud
• 视频教程：https://doc.iocoder.cn

【国内首批】支持 JDK 21 + SpringBoot 3.2.2、JDK 8 + Spring Boot 2.7.18 双版本 

建模完成了吗

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

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

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

• 可以每天 重复，或每 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/