代码之家 › 专栏 › 技术社区 › Ryan Guill

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

database-design database sql

Ryan Guill · 技术社区 · 14 年前

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

问题: 假设您有一个订单表,并且订单与下订单的客户的customer表相关。在正常的数据库结构中,可能会出现以下情况:

orders
------
orderID
customerID


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

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

现在有几种方法可以解决这个问题,其中之一就是在创建订单时复制记录。不过,我想到的是一种更简单的方法,它可能更优雅一点,而且还有一个额外的好处,就是在任何时候进行更改时都可以记录日志。

如果我做一个这样的结构:

orders
------
orderID
customerID
customerHistoryID


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


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

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

一个潜在的不利因素可能是删除,但我并不真正担心这一点,因为任何东西都不应该被删除。但即使如此,根据数据域的不同,使用activeFlag或类似的东西也可以达到相同的效果。

我的想法是所有表都将使用这种结构。无论何时检索历史数据,都会使用customerHistoryID将其与历史表连接起来,以显示特定顺序的数据状态。

检索客户列表很容易,只需连接customerHistoryID上的customer表。

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

更新:

7 回复 | 直到 10 年前

Conrad Frix 14 年前

当我遇到这样的问题时,另一种选择是将订单作为历史表。它的功能是一样的,但是更容易理解

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



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

编辑:如果列的数量达到你喜欢的高,你可以分开它不管你喜欢。

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

Jeffrey L Whitledge 14 年前

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

尝试这样的安排:

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.

如果你真的需要储存历史例如,跟踪订单随时间的变化,那么应该使用日志或审计表,而不是事务表。

HLGEM 14 年前

通常,订单只是按订单时的状态存储信息。尤其是零件号、零件名称和价格以及客户地址和名称。这样,您就不必连接到5或6个表来获取可以存储在一个表中的信息。这不是非规范化,因为您实际上需要拥有订单时存在的信息。我认为,在order和order detail(存储单独订购的项目)表中包含这些信息的可能性较小,因为数据的意外更改也会降低风险。

您的订单表不会有数百列。由于一对多关系,您将拥有一个订单表和一个订单明细表。订单表将包括订单号customer id 9,这样您就可以搜索该客户曾经订购过的所有商品,即使名称发生了变化)、客户名称、客户地址(注意,您不需要市/自治区邮政编码等,请将地址放在一个字段中)、订单日期以及其他一些与订单直接相关的顶级字段。然后您就有了一个订单明细表,该表包含订单号、明细id、零件号、零件描述(可以是一组字段的合并,如大小、颜色等,也可以将最常见的字段分离出来)、项目编号、单位类型、单位价格、税金、总价、发货日期、状态。您为订购的每件商品输入一个条目。

Erwin Smout 14 年前

警告1:这里没有SQL,几乎所有你认为你知道的关于关系模型的东西都是假的。有充分的理由。

警告2:你应该好好想想,好好想想。

警告3:这本书是关于这一系列问题的解决方案应该是什么样子的,但正如导言所说,它不是关于当今任何可用的技术。

Hogan 14 年前

我自己喜欢保持简单。我将使用两个表,一个customer表和一个customer history表。如果您在历史表中有键(如customerId),则没有理由创建联接表,对该键进行选择将给出所有记录。

所以我的看起来像这样:

CustomerTable  (this contains current customer information)
CustID (distinct non null)
...all customer information fields

CustomerHistoryTable
CustId (not distinct non null)
...all customer information fields
DateOfChange 
WhoChanged

DataOfChagne字段是将customer表(从此记录中的值)更改为CustomerTable中值的较新记录中的值的日期

您的orders表只需要一个CustomerID,如果您需要在下单时查找客户信息,那么这是一个简单的选择。

Ben 14 年前

你想要的是所谓的数据仓库。由于数据仓库是OLAP而不是OLTP,因此建议您根据需要拥有尽可能多的列,以实现您的目标。就你而言 orders 数据仓库中的表将有11个字段,作为订单的“快照”,而不考虑用户帐户的更新。

Wiley -The Data Warehouse Toolkit, Second Edition

这是个好的开始。

SteveM82 14 年前

我们的工资系统使用 生效日期 在许多桌子上。ADDRESSES表的键是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
)

目标是在客户中选择生效日期在订单日期当天或之前的最近一行。同样的策略也可以用来保存产品价格的历史信息。