如何实现历史版本？ [英] How to implement historical versioning?

问题描述

我们是在SQL 2008 R2建设一个大型的C＃MVC2应用程序（我们也采用夏普架构和NHibernate作为生态系统的一部分）的早期阶段，其中一项规定是，所有的数据库行版本的访问给定的历史时期。

我们已经有类似的布局的想法玩弄

ID（PK）结果
的recordId结果
VERSIONID

和有一个新的记录每个编辑，达到创纪录的结果被用同样的recordId和递增VERSIONID创建。记录显示然后将沿着SELECT ... WHERE的recordId = X和VERSIONID = MAX（VERSIONID）

的行有所作为

快照将在每交易将无法正常工作（太多？而不是从应用程序中访问容易）。

但我们是好奇什么其他的实现已经尝试成功，或者与我们的建议可能出现的问题。

解决方案

您似乎暗指一个时间表格。三种方法：

有效状态表：追加两个时间戳列（如键入 DATETIME ）的，一种指定当行成为有效的一指定当行不再是有效的，这段时间是作为该行的有效期间

交易时间状态表：每行联营的时间排在受监控表present，从而允许在任何previous被监测表的状态的时期在时间点重建。

双时态表：捕捉既有效时间和交易时间，同时记录了企业的历史，同时还捕捉变化的序列，历史的记录

来源：在开发SQL（理查德·斯诺德格拉斯牛逼）面向时间的数据库应用程序

We are in the early stages of building a large C# MVC2 app (we also employ Sharp architecture and Nhibernate as part of the ecosystem) on SQL 2008 R2, and one of the requirements is that all database row versions are accessible for a given period of history.

We have toyed with the idea of a layout similar to:

id (PK)
recordId
versionId

and having each edit to a record result in a new record being created with the same recordId and an incremented versionId. Record display would then be done with something along the lines of SELECT ... WHERE recordId = X AND versionId = MAX(versionId)

Snapshotting on every transaction would not work (too many? and not accessible from within the application easily).

But we are curious as to what other implementations have been tried with success, or potential problems with our proposal.

解决方案

You seem to be alluding to a temporal table. Three approaches:

Valid-state table: appending two 'timestamp' columns (e.g. of type DATETIME), one specifying when the row became valid and one specifying when the row stopped being valid, the intervening time is being the period of validity of the row

Transaction-time state table: associates with each row the period of time that row was present in the monitored table, thereby allowing the state of the monitored table at any previous point in time to be reconstructed.

Bitemporal table: capturing both valid time and transaction time, simultaneously records the history of the enterprise, while also capturing the sequence of changes to the record of that history.

Source: Developing Time-Oriented Database Applications in SQL (Richard T Snodgrass).

这篇关于如何实现历史版本？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！