sql - 存储历史数据的数据库结构

Question

前言:
前几天我正在考虑为新应用程序使用新的数据库结构，并意识到我们需要一种以有效方式存储历史数据的方法。我想让其他人看看这个结构是否有任何问题。我意识到这种存储数据的方法很可能以前已经被发明过(我几乎可以肯定它已经发明了)但我不知道它是否有名字，我尝试过的一些谷歌搜索没有产生任何结果。

问题:
假设您有一个订单表，订单与下订单的客户的客户表相关。在正常的数据库结构中，您可能会期待这样的事情:

orders
------
orderID
customerID


customers
---------
customerID
address
address2
city
state
zip

非常简单，orderID 有一个customerID 外键，它是customer 表的主键。但是如果我们要在订单表上运行报告，我们将把客户表连接到订单表，这将带回该客户 ID 的当前记录。如果下订单时客户地址不同并且随后更改了该怎么办。现在，我们的订单不再反射(reflect)下订单时该客户地址的历史记录。基本上，通过更改客户记录，我们只是更改了该客户的所有历史记录。

现在有几种方法可以解决这个问题，其中一种方法是在创建订单时复制记录。不过，我想出的是，我认为这是一种更简单的方法，可能更优雅一点，并且在进行更改时可以随时记录日志。

如果我改用这样的结构怎么办:

orders
------
orderID
customerID
customerHistoryID


customers
---------
customerID
customerHistoryID


customerHistory
--------
customerHistoryID
customerID
address
address2
city
state
zip
updatedBy
updatedOn

请原谅格式，但我认为您可以看到这个想法。基本上，这个想法是，无论何时更改、插入或更新客户，customerHistoryID 都会增加，并且客户表会更新为最新的 customerHistoryID。订单表现在不仅指向 customerID(它允许您查看客户记录的所有修订)，而且指向 customerHistoryID，它指向记录的特定修订。现在订单反射(reflect)了订单创建时的数据状态。

通过向 customerHistory 表中添加 updatedby 和 updatedon 列，您还可以看到数据的“审计日志”，这样您就可以看到谁进行了更改以及何时进行了更改。

一个潜在的缺点可能是删除，但我并不真正担心这一需求，因为不应该删除任何内容。但即便如此，根据数据的域，使用 activeFlag 或类似的东西也可以实现相同的效果。

我的想法是所有表都会使用这种结构。任何时候检索历史数据时，都会使用 customerHistoryID 将其与历史表连接，以显示该特定订单的数据状态。

检索客户列表很容易，只需连接到 customerHistoryID 上的客户表即可。

任何人都可以从设计角度或性能原因看出这种方法有什么问题。请记住，无论我做什么，我都需要确保保留历史数据，以便对记录的后续更新不会更改历史记录。有没有更好的办法？这是一个已知的有名字的想法，或者任何关于它的文档？

谢谢你的帮助。

更新:
这是我真正将要拥有的一个非常简单的例子。我的实际应用程序将具有带有其他表的几个外键的“订单”。始发地/目的地位置信息、客户信息、设施信息、用户信息等。 已经有几次建议我可以将信息复制到订单记录中，我已经多次看到这样做了，但这会导致记录有数百列，这在这种情况下确实不可行。

Answer 1

当我遇到此类问题时，一种替代方法是将订单设置为历史记录表。它的功能相同，但更容易遵循

orders
------
orderID
customerID
address
City
state
zip



customers
---------
customerID
address
City
state
zip

编辑:如果您喜欢的列数变得很高，您可以根据需要将其分开。

如果您确实使用其他选项并使用历史记录表，您应该考虑使用 bitemporal数据，因为您可能必须处理需要更正历史数据的可能性。例如，客户将其当前地址从 A 更改为 B，但您还必须更正当前正在履行的现有订单的地址。

此外，如果您使用 MS SQL Server，您可能需要考虑使用索引 View 。这将允许您将小的增量插入/更新性能降低换为较大的选择性能增加。如果您不使用 MS SQL 服务器，您可以使用触发器和表来复制它。