SQL历史表设计

Question

我需要设计一张历史记录表，以跟踪特定记录在编辑时更改的多个值。

示例:
用户打开编辑页面。

称号：Mr.
姓名：Joe
电话：555-1234
DOB: 1900-10-10

如果用户更改了这些值中的任何一个，我需要记录旧值并记录新值。

我考虑使用以下表格：

历史记录表
---------------

id
modifiedUser
modifiedDate
tableName
recordId
oldValue
newValue

这个表有一个问题就是每次编辑都会有多个条目。

我也考虑过将行的副本保存在历史记录表中，但那似乎也不太有效率。

您有什么想法吗？

谢谢！

- hebime

---

我可以说我们在系统中实现了历史记录表，它运行得非常好。是的，每次编辑可能会有多个条目，但如果您需要出于审计原因跟踪这些详细信息，这是最好的方法。 - Taryn

这和我在我的项目中实现的方式几乎相同，只是在你的例子中我缺少一个 columnName 列。我仅记录实际发生了更改的值（而不是一行中的所有列），对于这个解决方案，我认为很好。 - rabudde

Answer 1

我建议对于每个要跟踪历史记录的表格，您都需要一个第二个具有相同格式的表（例如tblCustomer和tblCustomer_History），并且还需要一个日期列。

每当进行编辑时，您就会将旧记录插入到历史记录表中，并附带日期/时间。这很容易做到，只需要少量代码更改（通常只需触发器）。

这样做的好处是使您的“真实”表尽可能小，同时为您提供了所有更改的完整历史记录。

然而，最终决定将取决于您如何使用这些数据。如果仅用于审计目的，那么此方法简单易行，除了额外的磁盘空间消耗和对主系统几乎没有影响之外，几乎没有缺点。

- E.J. Brennan

---

谢谢！我想我会选择这条路线。它似乎更符合我的当前系统模型。 - hebime

Answer 2

您需要确定您感兴趣的效率类型：您可以有存储空间效率，记录历史所需的工作效率（交易成本）或以特定方式查询记录历史的时间效率。

我注意到您在提议的历史记录表中有一个表名，这意味着您打算记录不止一个表的历史记录，这将排除在历史记录表中存储记录的确切副本的选项，除非您跟踪的所有表始终具有相同的结构。

如果您单独处理列，即为每个历史记录仅记录一列值，则必须设计一种多态数据类型，能够准确地表示您遇到的每个列值。

如果存储空间的效率是您的主要关注点，则应将历史记录分成多个表。这意味着新的列值表将链接到编辑事件表和列定义表。编辑事件表将记录用户和时间戳，列定义表将记录表，列和数据类型。正如@njk所指出的那样，您不需要旧的列值，因为您总是可以查询以前的编辑以获取旧值。这种方法预计可以节省空间的主要原因是通常情况下，用户将只编辑可用字段的一小部分。

如果查询效率是您的主要关注点，则应为您跟踪的每个表设置一个历史记录表，并在每个历史记录表中添加用户和时间戳字段。这在编辑的交易成本方面也应该是有效的。

- Paul Keister

---

我还想指出的是，一个审计表用于许多其他表将成为锁定痛点。 - HLGEM

@HLGEM 如果所有内容都在一个表中，它可能会成为痛点。我们实现审计表的方式是按模式拆分它。您的数据库使用模式来保持表格分离，每个模式都有一个审计表。 - Taryn

谢谢你的帮助。你的假设是正确的，多个表将在一个表中进行跟踪。我考虑为每个其他表使用不同的历史记录表，因为只有一小部分字段是可编辑的。这似乎是最合适的选择。 - hebime

关于多态数据类型和空间、事务成本以及查询时间的权衡，您提出了一个很好的观点。 - allenwlee

Answer 3

不需要在历史记录表中记录旧值和新值，只需记录最新值、作者和日期。然后根据记录的日期仅获取某个user_id的最新记录即可。如果你将要处理大量数据，这可能不是最佳方法。

用户（id，user_id，datetime，author，...）

示例数据

id user_id datetime author user_title user_name user_tele...
1 1 2012-11-05 11:05 Bob
2 1 2012-11-07 14:54 Tim
3 1 2012-11-12 10:18 Bob

- Kermit