启用/禁用/删除数据库行及其引用的最佳实践是什么？

Question

如何处理行的删除或启用/禁用以及其引用表的最佳实践？

例如，假设我有一个非常简单的“论坛”应用程序。

我有一个包含我的 Web 应用程序帐户的表 users，一个包含用户创建的主题的表 threads，以及一个包含用户对主题发表评论的表 comments。

现在，假设在注册时，我想在激活其账户之前验证用户的电子邮件。这样做的最佳方式是什么？有哪些最佳实践？也许通过一个视图，只返回一个具有字段 is_active=true 的行？使用 2 个分开的表，如 pre_users（包含仍需验证的用户）和 users（已验证的用户）？

同样，您将如何处理要暂停其账户的用户？还有他们的主题和评论呢？您会添加另一个标志 is_suspended，并更新该视图以考虑该标志吗？如果不是视图而是 2 个分开的表，我该如何处理引用？

此外，如何删除主题？假设在删除主题时，我不想实际删除主题，因为我不希望发表评论的用户突然看不到他们的评论。您将如何处理这个问题？

这些问题有哪些最佳实践？

- smellyarmpits

Answer 1

对于那些活动状态不影响唯一性的表格（例如，用户通过用户名或电子邮件地址进行识别，没有同一事物的活动和非活动版本），我使用可空日期时间字段表示状态。例如，对于您的用户表，我将具有一个verified_at列，最初设置为null，并在用户验证其帐户时设置为当前日期/时间。同样，用户帐户暂停也可以这样做。如果用户重新激活其帐户，则只需将suspended_at字段设置为null。

如果需要更多的状态值而不仅仅是简单的yes/no，我会使用单独的字段来表示状态值和日期/时间更改。

如果要跟踪更新历史记录（例如激活/暂停），最好在单独的表中执行此操作。在这种情况下，您可以从用户表引用当前激活记录，这将比查询状态列更有效地适用于表的索引。

在某些情况下，状态会影响唯一性。例如，在论坛中，用户可能能够存储任意数量的签名或头像，但只能同时有一个是活动状态。在这些情况下，最好将当前数据与历史数据分开。例如，一个专门用于签名的表格，在用户表中有一个外键来引用活动签名。您应该能够更新任何行的状态，而不必担心其他行中的值。

避免级联状态，它们破坏了子项的先前状态，并且在许多情况下使状态字段的使用几乎变得无意义。相反，在查询时与父表连接以针对顶层状态进行筛选。

最后，数据中的状态是迈向时间数据建模的一步。我建议阅读相关主题。

- reaanb

---

谢谢你的回答。在处理可能存在未激活行的表格上，你是如何进行数据检索的？比如说用户表。每次查询时，你都需要记得添加“where status=active”。这不是非常容易出错吗？我应该通过测试来解决这个问题，而不是深入研究过滤只有活跃用户的视图吗？此外，假设有一个由用户发表评论的评论表。你如何处理它们？当你禁用一个用户时，你也必须禁用他们的评论，因此同样会出现数据检索的问题。 - smellyarmpits

实际上，我没有太多记得添加“where status=active”的问题，但视图应该能够很好地简化这些情况。对于评论表，我建议加入用户以便根据用户状态进行过滤，而不是将用户状态传播到评论中。原因是评论可能受到多种状态的影响 - 自身、用户、线程、板块等。不同的查询将有不同的要求，例如在某些情况下，我们可能想显示不活动的线程，但仅包括来自活动用户的评论。最好将该逻辑留给查询/开发人员处理。 - reaanb

当你有很多用户和很多帖子时，连接操作会变得非常昂贵。特别是当用户和评论之间有许多表格（例如评论没有外键到用户，而是通过许多其他表格相关联）时，这一点尤为明显。 - smellyarmpits

我首先使用最简单的方法进行开发，然后在遇到问题时进行调整/优化。如果需要，我会从评论到用户添加一个FK。我经常进行涉及十几个表的连接操作，其中一些表有数百万条记录，仍然可以在20毫秒内获得结果。 - reaanb

Answer 2

您可能需要更详细地研究您的业务需求，然后我们才能回答所有这些变化。

一般来说，正如您在评论中与@reaanb辩论的那样，我不会担心连接的性能 - 现代硬件和现代数据库可以处理大量记录。

我还将重点放在关系模型中建模问题上，不要担心“包括此检查是否更复杂”。关注可理解性（设计与需求的接近程度）。我真的不喜欢使用视图来捕获这些内容，因为当基础业务要求发生变化时，它们会使变更变得更加困难。

根据我的经验，您需要回答的最大问题是“业务需求是否关心随时间的变化”？

如果答案是否定的，则可以使用任何有意义的状态标志。例如，在用户表上，您可能有一个带有“已注册/已验证/已停用/已删除”状态列。这相对简单的编码 - 但您无法轻松回答诸如“哪个日期该停用的用户已验证？”或“发布此评论时发布者的状态是什么？”。

如果要求关注时间，我喜欢的模型是在需要理解时间的行中添加“valid_from”和“valid_until”。 “当前”行具有一个空的“valid_until”列。这使您可以了解数据在任何时间点的状态-但它确实使查询更加复杂，特别是如果您跨多个表进行连接。

然而，这意味着，例如，您可以允许尚未验证的用户发布评论，但在他们验证其帐户之前隐藏这些评论。

这也意味着您可以创建报告，显示过去每个日期的每个状态下有多少用户，有多少非活动用户重新激活等等。

通过将“valid_from”和“valid_until”添加到您的“posts”表中，您可以包括版本控制-也许您想显示评论是在旧版本的帖子上发布的？

最后，在一些复杂的应用程序中，我使用有限状态机来管理状态之间的有效转换。这可能对您的系统来说有点过度设计。

- Neville Kuyt

Answer 3

数据建模中的状态模式
在这种情况下，我们可以使用一种我称之为数据建模中的状态模式的模式。

假设我们有一个具有许多状态的实体。例如，一个用户实体，它具有预注册、普通用户、已删除用户、暂停用户等状态。

另一个例子是用户帖子（类似于Stackoverflow的帖子），它有许多状态，如正常、用户删除、管理员删除、重复、关闭等。

假设我们想为用户实体建模状态。在这种情况下，我们可以使用一个实体来保存所有状态类型（如预注册、普通用户、已删除用户、暂停用户）。我们可以将其命名为User_Status_Types并将所有用户状态类型放入其中。

所以，另一个实体需要持有任何用户状态。让我把它命名为 User_Statuses。它具有User和User_Status_Types的F.Ks。

通过这种模式，我们可以保存所有用户的状态。

为了改善模式，我们可以在User中拥有User_Statuses的F.K，显示用户的最终状态。（请注意，这不是循环依赖陷阱）

问题1：如果实体有两种不同类型的状态？在这种情况下，应使用两次此模式。

问题2：如果子实体的状态（任何具有此实体的F.K的实体）取决于实体的状态？例如，如果我们想要不显示已删除用户的评论。在这种情况下，我们有两个选择：

选项1：我们可以编写触发器来更改用户状态。如果用户状态更改为删除，则我们的触发器也会将所有用户评论状态更改为删除。 选项2：在这个选项中，评论状态不会改变。但是，我们可以在评论的选择命令中使用额外的条件，并显示它们的父状态为正常的评论。 问题3：如果状态更改的顺序很重要，我们想在数据模型中对它们进行建模。在这种情况下，我们可以添加一个名为User_Status_Types_Sequence的新实体，它有两个来自User_Status_Types的外键作为源和目标。意思是，源状态可以更改为目标状态。因此，我们可以从数据库中选择序列有效性，然后将其执行到我们的数据库中。

- Gholamali Irani