设计一个表来保存数据库中的历史变化

1. 使用生效日期和生效日期字段

2. 使用历史记录表

3.使用审计表

结论

访问我的博客查看原文：设计一个表来保存数据库中的历史更改

几个月前，我遇到了一个经典问题：如何设计一个表以便它可以在数据库中保留历史变化？

假设您有一个交易表，其中包含以下字段：

交易

业务需求如下：用户希望保留交易表的历史变化以供将来使用，例如数据分析/审计

如何设计这样一个可以保留历史变化的表？

我有三种不同的方法来解决这个问题。

1. 使用生效日期和生效日期字段

我不喜欢这种方法，但我见过有人实践过，虽然很挣扎，但仍然坚持这样做。因此，我想解释一下为什么这种方法不好。

这个想法是在表格里面有EffectiveFrom和EffectiveTo字段，看起来像这样：

EffectiveFrom和字段EffectiveTo用于验证记录的有效性。例如，今天是2019-09-17，如果记录包含EffectiveFrombeing2019-09-01和EffectiveTobeing 2019-09-30，则该记录处于活动状态。如果今天的日期超出范围，则该记录处于非活动状态/已禁用状态。

在这种情况下，记录的有效性由这两个字段间接决定。因此，可能会发生以下情况

一旦值发生改变，该字段将被设置EffectiveTo为之前的日期，并将重复的记录插入到包含最新信息和不同ID 的表中。Disabledtrue

这样的设计虽然满足了在数据库中保留历史变更的需求，但实际上却很糟糕。由于复制了记录，ID 会从 变为100。101作为开发人员，我们知道这两个事务是同一个事务，只不过一个更新，一个更旧而已。但现在却有两个 ID 来代表一条记录，这对于链接到该记录的其他表来说简直是灾难。

例如，如果你有一个TransactionDetails包含交易项目的表，需要通过交易 ID 来建立关联，那么你最终会遇到麻烦。假设我在交易 100 中购买了一台 iPad 和 Apple Pencil。现在交易更新为 101，但 iPad 和 Apple Pencil 仍然链接到 100。

为了解决这个问题，您需要进行更多查询来确定哪个是最新的活动ID，然后链接到它，这大大增加了复杂性。

这确实很糟糕。

2. 使用历史记录表

如果我想保留整条记录作为历史记录，但又不想影响实际的表，该怎么办？那么您可能需要为 创建一个历史记录表Transaction，该表具有与表本身相同的字段Transaction。

交易历史

同样的例子也发生在这里：有一笔交易如下：

然后，id 为 的用户20将描述修改为 ，A not long text并将DeliveryDate修改为2019-10-01。

现在，您只需将一条Transaction记录（该记录是表中记录的重复记录）插入到TransactionHistory表中，如下所示：

Transaction最后，将表中的原始记录更新为

在这种情况下，您尝试将旧记录完整保存到另一个表中，然后在原始表中应用更改。如果用户希望全面了解记录的变化情况，那就更好了。

然而，这种方法的缺点是会存储冗余信息。例如，如果你有大量字段，但每次只更新一两个字段，这实际上会浪费大量的空间。

3.使用审计表

更好的解决方案是创建一个审计表来记录每个字段的每一次更改，这样可以通过消除冗余信息来节省空间。该表如下所示：

审计

例如，有一笔交易如下：

现在，另一个 ID 为 的用户将20描述修改A not long text为DeliveryDate2019-10-01

因此，有 3 个字段发生了更改，分别Description是 、ModBy和ModDate。审计表中应分别添加 3 条记录，如下所示。

Transaction最后，将表中的原始记录更新为

这样，查询字段变更就变得非常容易。例如，如果我想查找 ID 为 100 的交易的所有描述变更，我只需查询

Select * from Audit where RecordId = 100 and Field = "description"。

这种方法的缺点是记录数量可能会大幅增加。由于不同字段的每次更改都会在审计表中生成一条记录，因此审计记录可能会急剧增长，例如数十条更改会导致数百条审计记录。在这种情况下，表索引对于增强查询性能至关重要。

结论

在本文中，我讨论了在数据库中保存历史更改的 3 种方法，即有效起始和有效结束字段、历史表和审计表。

不建议使用生效日期和生效日期字段，我建议根据情况使用历史表或审计表来解决问题。

以下是比较结果。

审计表

• 仅记录发生变化的字段的数据
• 优点：
  • 不影响实际表
  • 无冗余信息
• 缺点：
  • 记录数量可能会大幅增加
• 适用于：实际表中有很多字段，但经常只有少数字段发生变化

历史表

• 记录整个旧记录
• 优点：
  • 简单查询即可获得完整的历史记录
  • 不影响实际表
• 缺点
  • 存储冗余信息
• 适用于：
  • 很多字段一次性更改
  • 需要生成具有完整记录历史记录的变更报告

特色图片由 Pexels 的 Joshua K. Jackson提供