如何存储复杂记录的历史

Question

我的关系数据库中有三个表，如下所示:路由-> RouteStore -> Store。因此，我可以每隔一段时间修改哪些存储可以成为路由的一部分(添加或删除)，并且需要保留这些更改的历史记录。我认识的人建议将数据格式化为文本格式(本例为JSON)，并将其保存到一个新表中。

我对使用JSON策略如此怀疑的原因是，如果我要使用历史数据，那么在我能够使用它之前，我总是要做一些编程来解码数据。由于数据库是关系型的，所以使用任何类型的查询都不会有任何优势。这样做，我意识到保存数据非常简单，但是使用它就不那么简单了。

这些数据的使用是至关重要的，因为我必须从历史数据中创建一组不同类型的报告，这些报告最终会变成一场噩梦。

是否有更好的方法来保存路线修改的历史？我应该考虑什么替代方案？

Answer 1

您似乎要寻找的是每个表的历史表。

首先，我将假设一些关于您的表的内容来说明这种模式：

Route
- Id
- Name
- DateCreated
- DateUpdated
- CreatedByUserId
- UpdatedByUserId

您将创建包含完全相同列的“影子”表，但它们将有一个额外的“版本”或“历史序列”列作为主键，以及另一列定义所执行的操作(插入、更新或删除)。

如果您正在执行delete的操作，则不需要真正的表的外键，因为您将无法删除历史记录。这实现了完全的更改跟踪：

RouteHistory
- VersionNumber (Primary key)
- Action (string)
- Id
- Name
- DateCreated
- DateUpdated
- CreatedByUserId
- UpdatedByUserId

路由表中的插入应该与插入到RouteHistory对应：

• VersionNumber: 1
• 行动:插入
• Id：(新的路径记录Id，例如723)
• 名称：(新路线纪录名称)
• DateCreated：...
• CreatedByUserId：...

现在，当更新路由记录#723时，您将得到另一个插入到RouteHistory中的内容：

• VersionNumber: 2
• 行动:更新
• 编号: 723
• 名称：(现时路线名称)
• DateCreated：(目前的DateCreated路线)
• DateUpdated：(目前的DateUpdated路线)
• ..。(路线上的每一列重复)

这可以在数据库中为事务处理较重的表生成大量数据，但它确实对这些表进行了每次修改。这对于审计目的尤其有用。

将主表中的数据与历史表中的数据同步的最简单方法是在数据库中使用触发器，但是如果有一些数据库事务不希望触发器执行.再一次，如果您要进行完全更改跟踪触发器，保证准确性。