存储历史数据的数据库结构 [英] Database structure for storing historical data

问题描述

前言：
我一直在想另一天有关新应用程序的新数据库结构，并意识到我们需要一种以有效的方式存储历史数据的方法。我想让别人来看看，看看这个结构是否有任何问题。我意识到，这种存储数据的方法可能很好地发明了（我几乎肯定它有），但我不知道它是否有一个名称和一些谷歌搜索，我试过没有产生任何东西。

Preface: I was thinking the other day about a new database structure for a new application and realized that we needed a way to store historical data in an efficient way. I was wanting someone else to take a look and see if there are any problems with this structure. I realize that this method of storing data may very well have been invented before (I am almost certain it has) but I have no idea if it has a name and some google searches that I tried didn't yield anything.

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

Problem: Lets say you have a table for orders, and orders are related to a customer table for the customer that placed the order. In a normal database structure you might expect something like this:

orders
------
orderID
customerID


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

很简单，orderID有一个外键of customerID是客户表的主键。但是，如果我们要在订单表上运行一个报表，我们将加入customers表到订单表，这将返回该客户ID的当前记录。如果订单下了，客户地址是不同的，它后来改变了。现在我们的订单不再反映客户地址的历史，在订单的时间。基本上，通过更改客户记录，我们只是更改了该客户的所有历史记录。

Pretty straightforward, orderID has a foreign key of customerID which is the primary key of the customer table. But if we were to go and run a report over the order table, we are going to join the customers table to the orders table, which will bring back the current record for that customer ID. What if when the order was placed, the customers address was different and it has been subsequently changed. Now our order no longer reflects the history of that customers address, at the time the order was placed. Basically, by changing the customer record, we just changed all history for that customer.

现在有几种方法，其中之一是复制记录已创建订单。我想到的是，我认为这将是一个更简单的方法，这可能是一个更优雅的一点，并有额外的好处日志记录任何时候改变。

Now there are several ways around this, one of which would be to copy the record when an order was created. What I have come up with though is what I think would be an easier way to do this that is perhaps a little more elegant, and has the added bonus of logging anytime a change is made.

如果我改成这样的结构会怎么样：

What if I did a structure like this instead:

orders
------
orderID
customerID
customerHistoryID


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


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

请原谅格式化，但我想你可以看到的想法。基本上，这个想法是，每当客户更改，插入或更新时，customerHistoryID都会增加，并且customers表将使用最新的customerHistoryID更新。现在，订单表不仅指向customerID（允许您查看客户记录的所有修订版本），还指向指向记录的特定修订版本的customerHistoryID。现在订单反映订单创建时的数据状态。

please forgive the formatting, but I think you can see the idea. Basically, the idea is that anytime a customer is changed, insert or update, the customerHistoryID is incremented and the customers table is updated with the latest customerHistoryID. The order table now not only points to the customerID (which allows you to see all revisions of the customer record), but also to the customerHistoryID, which points to a specific revision of the record. Now the order reflects the state of data at the time the order was created.

通过在customerHistory表中添加updatedby和updatedon列，您还可以看到audit日志，所以你可以看到谁做了更改和什么时候。

By adding an updatedby and updatedon column to the customerHistory table, you can also see an "audit log" of the data, so you could see who made the changes and when.

一个潜在的缺点可能是删除，但我不是真的担心这个需要，因为什么都不应该删除。但是，即使如此，通过使用activeFlag或类似的东西可以实现相同的效果取决于数据的域。

One potential downside could be deletes, but I am not really worried about that for this need as nothing should ever be deleted. But even still, the same effect could be achieved by using an activeFlag or something like it depending on the domain of the data.

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

My thought is that all tables would use this structure. Anytime historical data is being retrieved, it would be joined against the history table using the customerHistoryID to show the state of data for that particular order.

检索客户列表很容易，只需要加入customerHistoryID上的客户表即可。

Retrieving a list of customers is easy, it just takes a join to the customer table on the customerHistoryID.

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

Can anyone see any problems with this approach, either from a design standpoint, or performance reasons why this is bad. Remember, no matter what I do I need to make sure that the historical data is preserved so that subsequent updates to records do not change history. Is there a better way? Is this a known idea that has a name, or any documentation on it?

感谢任何帮助。

更新：
这是一个非常简单的例子，我将真正有。我的实际应用程序将有订单几个外键到其他表。起点/目的地位置信息，客户信息，设施信息，用户信息等。已经提出了几次，我可以将信息拷贝到该点的订单记录中，并且我已经看到它以这种方式多次，但是这将导致一个有数百列的记录，这在这种情况下真的不可行。

Update: This is a very simple example of what I am really going to have. My real application will have "orders" with several foreign keys to other tables. Origin/destination location information, customer information, facility information, user information, etc. It has been suggested a couple of times that I could copy the information into the order record at that point, and I have seen it done this way many times, but this would result in a record with hundreds of columns, which really isn't feasible in this case.

推荐答案

ve遇到这样的问题一个选择是使顺序的历史表。

When I've encountered such problems one alternative is to make the order the history table. Its functions the same but its a little easier to follow

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



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

编辑：如果列的数量变得很高，你可以根据自己的喜好将其分开。

if the number of columns gets to high for your liking you can separate it out however you like.

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

If you do go with the other option and using history tables you should consider using bitemporal data since you may have to deal with the possibility that historical data needs to be corrected. For example Customer Changed his current address From A to B but you also have to correct address on an existing order that is currently be fulfilled.

此外，如果您使用MS SQL Server，您可能需要考虑使用索引视图。这将允许您交易小增量插入/更新perf减少为大选择perf增加。如果您不使用MS SQL服务器，您可以使用触发器和表来复制。

Also if you are using MS SQL Server you might want to consider using indexed views. That will allow you to trade a small incremental insert/update perf decrease for a large select perf increase. If you're not using MS SQL server you can replicate this using triggers and tables.

这篇关于存储历史数据的数据库结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！