表修订/历史记录？
我试图找出在关系数据库中保留表的历史记录/修订的最佳方法。
我做了一些研究和阅读，但不确定跟踪变化的最佳方法是什么。 对于我的主表，我很确定我已经选择了修订表来跟踪（见图），但我不确定的是关系表。 也许只是一个保存更改的审计跟踪表？
查看下面的示例图片，保留 movies_has_actors 表历史记录的最佳方法是什么？ 我不能简单地对两个主表（电影、演员）进行操作，因为我需要知道在建立关系时哪个 MOVIE revision_id 是活动的。 如果我想跟踪哪个用户添加了关系（用户表不在示例图片中）怎么办？ 我不想将所有内容保留在关系表本身中，因为这只会变得巨大并减慢查询速度...！[alt text][1]
总而言之，保留历史记录的最佳方法是什么关系表？
图片链接：img115.imageshack.us/my.php?image=44623598nv1.jpg
[1]: 图片
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?
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?
Link to image: img115.imageshack.us/my.php?image=44623598nv1.jpg
[1]: Image
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在 SQL Server 2008 中，MSDN 上称为 CDC（更改数据捕获）CDC 的新功能可以提供帮助。 CDC 能够将表数据的更改记录到另一个表中，而无需编写触发器或其他机制，更改数据捕获将插入、更新和删除等更改记录到 SQL Server 中的表中，从而使更改的详细信息以关系型形式提供格式。
Channel9 视频 - https://channel9. msdn.com/posts/ashishjaiman/CDC-Change-Data-Capture-SQL-Server-2008/
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 video - https://channel9.msdn.com/posts/ashishjaiman/CDC-Change-Data-Capture-SQL-Server-2008/
我认为审计表是一个很好的解决方案。 如果您使用 SQL Server 2008，请查看更改数据捕获。 这是一项自动更改跟踪功能。
I think Audit tables are a good solution. If your using SQL Server 2008 check out Change Data Capture. This is an automatic change tracking feature.