表修订/历史？ [英] Table revision/history?
问题描述
我正在尝试找出在关系数据库中保留表的历史记录/修订的最佳方法。
I'm trying to figure out the best way to keep history/revision of tables in a relational database.
我已经做了一些研究和阅读，不确定跟踪变更的最佳方法是什么。对于我的主表，我很确定自己已经准备好修订表，以进行跟踪（参见图片），但是我不确定它是关系表。
I have done some research and reading, and am not sure what would be the best way to go about keeping track of changes. For my main tables, I'm quite sure I have settled for a revision table, to keep track (see picture), but it is the relation tables that I'm not sure about. Maybe just an audit trail table holding the changes?
看看下面的示例图片，保留 movies_has_actors历史的最佳方法是什么？ 表？
我不能简单地使用两个主表（电影，演员），因为我需要知道在建立关系时哪个MOVIEversion_id是活动的。如果要跟踪哪个USER添加了关系（USER表不在示例图片中），该怎么办？
我不想将所有内容保留在关系表本身中，因为这只会变得庞大而使查询变慢...！[alt text] [1]
Looking at the sample picture below, what would be the best way to keep history of the movies_has_actors table?
I can't simply do as with the two main tables (movies, actors), as I need to know which MOVIE revision_id that was the active one at the point of time the relation was made. And what if I want to throw in tracking of which USER added the relation (USER table not in sample picture)?
I don't want to keep everything in the relation table itself, because that will just grow huge and slow down queries...![alt text][1]
总而言之，保留关系表历史记录的最佳方法是什么？
So to sum up, what is the best way to keep history of a relation table?
链接到图像：img115.imageshack.us/my.php ？image = 44623598nv1.jpg
Link to image: img115.imageshack.us/my.php?image=44623598nv1.jpg
[1]：图像
推荐答案
在SQL Server 2008中，有一个称为CDC（更改数据捕获）的新功能。 ）MSDN上的CDC可以提供帮助。 CDC可以将表数据的更改记录到另一个表中，而无需编写触发器或其他机制，Change Data Capture可以将更改（如插入，更新和删除）记录到SQL Server中的表中，从而使更改的详细信息在关系型中可用格式。
In SQL Server 2008 a new feature called CDC (Change Data Capture) CDC on MSDN can help. CDC is an ability to record changes to table data into another table without writing triggers or some other mechanism, Change Data Capture records the changes like insert, update, and delete to a table in SQL server thus making the details of the changes available in relational format.
Channel9视频- https://channel9.msdn.com/posts/ashishjaiman/CDC-Change-Data-Capture-SQL-Server-2008/
Channel9 video - https://channel9.msdn.com/posts/ashishjaiman/CDC-Change-Data-Capture-SQL-Server-2008/
这篇关于表修订/历史？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
