数据的时间点“快照”的数据库设计?

这并不容易。

和 ,基本上,对于每个基准,您需要附加一个时间范围,该值的基准适用于该时间范围。然后,在联接中,联接必须仅包括在所考虑的时间范围内的行。

时态建模很困难——这是第六范式所要解决的问题——并且在当前的RDBMS中没有得到很好的支持。

基本上,只要你有关系(“ted拥有id为789的GM股票”),你就会加上一个时间:“ted拥有id为789的GM股票 因此,你可以同时说,“弗雷德从2000年2月3日到昨天拥有id为789的通用汽车股票证书”。显然,这些关系是多对多的,(特德现在可以拥有多张证书,在他一生中也可以拥有多张证书,弗雷德以前可以拥有杰克现在拥有的证书)。

现在,假设每个州/省/土地都对股票的股息征税,因此出于税务目的,记录股票持有人的居住状态。

所有者居住地可以明显独立变更股权;特德可以住在内布拉斯加州,购买10股,获得内布拉斯加州征税的股息,搬到内华达州,向弗雷德出售5股,再购买10股。

有时 ,购买10股 有时 有时 ,内布拉斯加州征税,搬到内华达州 ,向弗雷德出售5股 有时 .

如果我们想计算泰德在内布拉斯加州和内华达州所欠的税款,我们需要所有这些,将个人股票证书和个人地址中的匹配/重叠日期范围合并起来。一个人的地址不再是一对一,而是一对多,因为它是地址 在时间范围内 .

如果ted购买了10股股票,我们是用一个购买日期来模拟购买事件,还是给每一股股票添加一个购买日期?取决于我们需要模型回答的问题。

然后,我们为在所有受影响的表上进行连接的特定插入或更新生成触发器,并将其插入快照表中。

id, firstname, lastname, department_id

部门:

id, name, departmenthead_id

用户表的快照可能如下所示:

user_id, user_firstname, user_lastname, department_id, department_name, deparmenthead_id, deparmenthead_firstname, departmenthead_lastname, snapshot_date

还有一个类似这样的查询

INSERT INTO usersnapshot
SELECT user.id AS user_id, user.firstname AS user_firstname, user.lastname AS user_lastname,
department.id AS department_id, department.name AS department_name
departmenthead.id AS departmenthead_id, departmenthead.firstname AS departmenthead_firstname, departmenthead.lastname AS departmenthead_lastname,
GETDATE() AS snapshot_date
FROM user
INNER JOIN department ON user.department_id = department.id
INNER JOIN user departmenthead ON department.departmenthead_id = departmenthead.id

拥有快照和/或审计跟踪是常见的数据库要求。对于许多应用程序来说,创建“影子”或审计表是一项简单而直接的任务。虽然数据库级备份和事务日志很好,但它们不是版本控制系统。

基本上,您需要创建一个具有与基表相同的所有列的阴影表,然后在基表上设置触发器,以便在更新或删除行时将其副本放置在阴影表中。

通过某种逻辑,您可以重新创建给定时间点的数据外观。有关在Sybase中设置此项的简单方法,请参阅: http://www.theeggeadventure.com/wikimedia/index.php/Sybase_Tips#create_.27audit.27_columns

如果需要进行大量历史快照,则可以将数据保存在同一个表中。基本上,创建两列—一个添加列和一个删除列。缺点是对于每个查询,都必须添加where子句。当然,您可以创建一个只显示活动记录的视图。如果您有一个包含多个表的规范化数据库,并且所有表都有历史记录,那么这将变得更加复杂。

然而,它确实有效。您只需在每个表上添加“added”和“deleted”列,然后您的查询就有了感兴趣的时间点。无论何时修改数据,都必须复制当前行,并将其标记为已删除。

Log Triggers

所有数据更改都被捕获,从而能够像在任何时间点一样进行查询。

SQL Server 2005(以后版本)企业版能够创建 Database snapshots

从版本9i开始的Oracle具有Flashback技术,在Oracle 10g和11g中得到了很大的改进,只要启用Flashback,您就可以看到数据库在历史上任何给定时刻的状态。

Flashback Overview

你的客户到底想要什么?是否出于分析目的(例如,问题是两周前我们有多少订单)?因为这正是数据仓库要解决的问题。

您可以使用RDBMS生成的日志来获取数据的快照。通常,日志用于提供数据库恢复。但是,它们也可以用于跨多个RDBMS实例复制数据或获取数据的快照。

要获取数据的快照,只需考虑在所需时间之前生成的所有日志。然后“回放”这些日志,以获得恢复数据的实际数据库。

如何访问和“回放”日志取决于您使用的具体RDBMS产品。

另一种可能性是使用时态数据库。它们具有内置的时间特性,允许“回到时间”。以“Oracle闪回技术”为例。 http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10795/adfns_fl.htm#ADFNS1008

我会在每个表上使用一个额外的时间戳字段,无论它是主表还是事实/事务表。即使目标是更新,所有表都需要新插入。