用于存储历史数据的数据库结构

Question

前言： 最近我在想一个新应用的数据库结构，意识到我们需要一种有效的方法来存储历史数据。我想让其他人看看这个结构是否有任何问题。我认识到这种存储数据的方法很可能已经被发明了（我几乎肯定是这样），但我不知道它有没有一个名称，我尝试了一些谷歌搜索，但没有找到任何东西。

问题： 假设您有一个订单表，并且订单与放置订单的客户表相关联。在正常的数据库结构中，您可能会期望像这样：

orders
------
orderID
customerID


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

相当直接，orderID具有customerID的外键，该外键是客户表的主键。但是，如果我们运行订单表上的报告，我们将连接客户表和订单表，这将带回该客户ID的当前记录。如果在下订单时客户地址不同并且后来已更改怎么办？现在我们的订单不再反映下订单时该客户的历史地址。基本上，通过更改客户记录，我们刚刚更改了该客户的所有历史记录。

现在有几种方法可以解决这个问题，其中之一是在创建订单时复制记录。然而，我想出了一种更容易做到并且更加简洁的方法，还有一个额外的好处，就是每次更改都会进行日志记录。

那如果我像这样构建结构呢：

orders
------
orderID
customerID
customerHistoryID


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


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

请原谅格式问题，但我认为您可以理解这个想法。基本上，每当更改客户时（插入或更新），就会递增customerHistoryID，并使用最新的customerHistoryID更新customers表。现在，order表不仅指向customerID（允许您查看客户记录的所有修订版本），还指向customerHistoryID，该ID指向记录的特定修订版本。现在，订单反映了创建订单时数据的状态。

通过向customerHistory表添加updatedby和updatedon列，您还可以查看数据的“审计日志”，因此您可以查看谁何时进行了更改。

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

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

检索客户列表很容易，只需要在customer table中使用customerHistoryID连接即可。

有人能否看到这种方法存在任何问题，无论是从设计角度，还是从性能方面考虑，这是否不好。请记住，无论我做什么，都必须确保历史数据得以保留，以便对记录进行的后续更新不会更改历史记录。是否有更好的方法？这是一种已知的想法吗？或者有关于它的任何文档？

感谢您的任何帮助。

更新： 这是我真正拥有的非常简单的示例。我的实际应用程序将具有与其他表的几个外键相关联的“订单”。起始/目标位置信息、客户信息、设施信息、用户信息等。有几次建议我可以在那时将信息复制到订单记录中，并且我已经看到过很多这样做的情况，但是这将导致具有数百列的记录，在这种情况下确实行不通。

- Ryan Guill

---

所以基本上你的意思是：“我的订单表中有太多列了。因此，我想把订单地址放在客户表中。为了支持这一点，我想用一个复杂的历史跟踪方案来妥协客户数据。” 对我来说听起来不是个好主意。 - Jeffrey L Whitledge

不，完全不是。我的意思是我需要能够跟踪地址的变化，并能够将订单与地址的特定状态（修订版）联系起来。订单可能不是唯一与地址相关的表，更不用说我们想知道谁何时更改了地址了。 - Ryan Guill

顺便提一下，永远不要假设什么都不会被删除。要为不可避免的删除做好计划，或者创建一个触发器来防止删除。 - HLGEM

我有一个类似的解决方案，但是我使用所有查找表中名为“ver”的版本列，而不是使用CustomerHistoryId。因此，在所有查找表中都有一个id和ver列，它们组成一个复合键。如上所述，永远不会删除任何内容，但可以将isActive标志设置为false。对于每个查找表，主表中有两列比复制所有数据要好得多。 - Thomas Fonseca

@RyanGuill 你还记得你是怎么解决的吗？我也遇到了同样的问题，而且对这个话题的回答都一般般啊... - undefined

Answer 1

当我遇到这样的问题时，一个替代方案是将历史表按顺序排列。它的功能相同，但更容易理解。

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



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

编辑：如果您认为列数过多，可以随意分离。

如果您选择使用历史表中的其他选项，则应考虑使用bitemporal数据，因为您可能需要处理历史数据需要更正的情况。例如，客户将其当前地址从A更改为B，但您还必须纠正正在执行的现有订单上的地址。

此外，如果您正在使用MS SQL Server，您可能需要考虑使用索引视图。这将允许您将小的增量插入/更新性能下降换成较大的选择性能提高。如果您没有使用MS SQL服务器，可以使用触发器和表来复制此操作。

- Conrad Frix

---

是的，我以前也见过这种做法。但这只是一个非常简化的例子，在我考虑的实际应用中，“订单”将有许多外键和其他表中的大量数据。最终，我将拥有一个具有数百列的“订单”记录。 - Ryan Guill

这是正确的方法，因为地址是订单的一个函数，而不是客户在下订单时的函数。如果您想简化订单表，我建议使用一个外键指向一个地址表。实际上，客户和订单都可以将他们的地址存储在同一个地址表中，毫无困难。这样做也会使包括分别的送货地址和账单地址等变得容易。 - Jeffrey L Whitledge

@Jeffrey L Whitledge在这里提出了一个非常重要的观点，即地址、客户姓名、价格等现在是订单的功能，而不是客户或价格表的功能，这就是为什么除了将它们放在与订单相关的表中，没有其他好的解决方案。 - HLGEM

Answer 2

当你设计数据结构时，一定要非常小心地存储正确的关系，而不是类似于正确关系的东西。如果需要维护订单地址，则这是因为地址是订单的一部分，而不是客户的一部分。同样，单价是订单的一部分，而不是产品的一部分等等。

尝试像这样安排：

Customer
--------
CustomerId (PK)
Name
AddressId (FK)
PhoneNumber
Email

Order
-----
OrderId (PK)
CustomerId (FK)
ShippingAddressId (FK)
BillingAddressId (FK)
TotalAmount

Address
-------
AddressId (PK)
AddressLine1
AddressLine2
City
Region
Country
PostalCode

OrderLineItem
-------------
OrderId (PK) (FK)
OrderItemSequence (PK)
ProductId (FK)
UnitPrice
Quantity

Product
-------
ProductId (PK)
Price

etc.

如果你真的需要存储某个东西的历史记录，比如跟踪订单随时间变化的情况，那么你应该使用日志或审计表来完成，而不是使用事务表。

- Jeffrey L Whitledge

---

我使用的表格只是为了举例说明。我们实际上要做的就是按照您所描述的将地址与客户（我们称之为位置）分开。我的问题只涉及存储数据的方案。 - Ryan Guill

这个回答并没有帮助到原问题。他需要关于时间点的帮助，而这个解决方案继续使用一个地址表，如果地址被更新，它将更新任何订单，因为你有一个外键关系。 - maguy

@maguy - 从我的回答中并不清楚，但我的意图是地址数据永远不会被更新。相反，如果客户地址发生变化，则会插入一个新地址，并且客户将收到一个新的地址ID，保留现有订单地址不变。同样，如果订单上的地址需要更改。 - Jeffrey L Whitledge

Answer 3

通常订单只是按照下单时的信息存储。特别是像零件号、零件名称和价格以及客户地址和姓名之类的信息，不需要连接5到6个表格来获取可以存储在一个表中的信息。这并不是非规范化，因为实际上您需要将信息保存在下单时的状态。我认为，在订单和订单详细（存储订购的各个项目）表中具有此信息的风险更小。

您的订单表不会有数百列。由于一对多的关系，您将拥有一个订单表和一个订单详细表。订单表将包括订单号、客户ID（因此即使名称更改也可以搜索此客户已经订购的所有内容）、客户名称、客户地址（请注意，您不需要城市州邮编等，将地址放在一个字段中）、订单日期以及可能与订单直接相关的其他几个字段。然后您有一个订单详细表，其中包含订单号、详细ID、零件号、零件描述（这可以是一堆字段的汇总，如大小、颜色等，或者您可以分开最常见的字段），数量、单位类型、每单位价格、税费、总价、发货日期和状态。您为每个订购的项目放置一个条目。

- HLGEM

---

我明白你的意思，但就像我在Conrad Frix的回答中所说的那样，最终我的“订单”表将有数百列，这真的是不可行的。我可能应该把这个问题加到问题描述里。 - Ryan Guill

Answer 4

如果您真正对这些问题感兴趣，我只能建议您认真阅读《时间数据与关系模型》。警告1：书中没有SQL，几乎您所知道的关系模型的所有内容都将被认为是虚假的。有很好的理由。警告2：您需要思考，而且要深入思考。警告3：这本书介绍了这一特定问题家族的解决方案应该是什么样子，但正如引言所说，它并不涉及任何现有技术。话虽如此，这本书确实是一种启示。至少，它有助于澄清这类问题的解决方案在今天的SQL或ORM中无法找到。

- Erwin Smout

Answer 5

我们的工资系统在许多表格中使用“生效日期”。地址表以EMPLID和EFFDT为键。这使我们能够跟踪员工地址更改的每个时间点。您可以使用相同的逻辑来跟踪客户的历史地址。您的查询只需要包括一个子句，将订单日期与订单时生效的客户地址日期进行比较即可。例如：

select o.orderID, c.customerID, c.address, c.city, c.state, c.zip
from orders o, customers c
where c.customerID = o.customerID
and c.effdt = (
   select max(c1.effdt) from customers c1
   where c1.customerID = c.customerID and c1.effdt <= o.orderdt
)

目标是选择客户中最近的一行，其有效日期在订单日期之前或当天。这种策略也可以用于保留产品价格的历史信息。

- SteveM82

Answer 6

我个人喜欢保持简单。我会使用两个表：一个客户表和一个客户历史记录表。如果在历史记录表中有关键字（例如CustomerID），则没有理由创建连接表，对该关键字进行选择将为您提供所有记录。

您还没有在历史记录表中显示审计信息（例如修改日期、修改者等），我认为您希望这样做。

因此，我的表看起来应该是这样的：

CustomerTable (this contains current customer information)
CustomerID (distinct non null)
...all customer information fields
    
CustomerHistoryTable
CustomerID (not distinct non null)
...all customer information fields
DateOfChange 
WhoChanged

DateOfChange字段是客户表更改的日期（从此记录中的值）到CustomerTable中更近记录中的值。

如果您需要在订单时间找到客户信息，您的订单表只需要一个CustomerID，这是一个简单的选择。

- Hogan

---

我不知道这个方案相对于 OP 建议的模式有什么优势 - 在某些方面，它可能会更加棘手（如果你想查找历史客户信息，正如你可能会发现的那样，这就多了一点复杂性） - 话虽如此，我之前也使用过这种方法，并且看到其他几位程序员也在使用。 - Will A

你说得对，我想要修改日期和修改者。（我在文本中提到了它，但没有在表格中，我会编辑使其更清晰明了）。实际上我一开始就设计成这样，但后来意识到这是数据的不必要重复。通过简单的连接（在适当的索引下应该很快），我可以完成同样的事情，而且每次都不必写两遍所有这些信息。但我最初也有同样的想法。 - Ryan Guill

订单参考是什么？客户ID吗？如果是，更改客户地址是否会自动影响订单信息？ - Conrad Frix

是的，订单将引用客户ID。并且保持对客户的更新不影响订单信息正是我想要的。我不希望在订单完成后更改客户地址会影响订单信息。 - Ryan Guill

@Ryan：这种方法不应包含重复数据--您不保留最新版本--这是为您正在保留历史记录的表保留的。您可以通过仅存储更改的字段使其更有效率--但这需要更多编码--如果您有时间/倾向，那么它更加优雅。这是快速简便的解决方案，许多DBA只使用触发器来完成--我讨厌触发器并且不建议使用。 - Hogan

Answer 7

你需要的是一个数据仓库。由于数据仓库是 OLAP 而不是 OLTP，建议您拥有所需的所有列以实现您的目标。在您的情况下，数据仓库中的 orders 表将具有11个字段，因为它保留着订单的“快照”，而不管用户账户更新如何。

Wiley -The Data Warehouse Toolkit, Second Edition

这是一个不错的开始。

- Ben

---

数据仓库/数据集市不一定是OLAP。数据仓库和数据集市可以作为OLAP Cube的来源，但你也可以拥有一个数据仓库而没有OLAP。 - jasonco

@jasonco 数据仓库的数据源通常是 OLTP，没错，但仅限于此。然而，数据仓库不计算，为了进行计算，它们需要摒弃规范化，因此拥有庞大的表格以及响应时间，这没问题，因为它们是 OLAP 而不是 OLTP。根据他对问题的描述，@OP 需要一个数据仓库，而不仅仅是另一个数据库。 - Ben