如何存储数据的更改历史记录？ [英] How to store data's change history?

问题描述

我需要将数据的更改历史记录存储在数据库中。例如有些时候某些用户修改某些数据的某些属性。预期的结果是我们可以得到一个数据的变化历史，例如

I need to store data's change histories in database. For example some time some user modify some property of some data. The expected result is we can get the change histories for one data like

Tom    changed title to 'Title one;' 
James  changed name to 'New name'
Steve  added new_tag 'tag23'

这些更改历史，我们可以得到一些数据的所有版本。

Based on these change histories we can get all versions for some data.

任何好主意来实现这一点？不限于传统关系数据库。

Any good idea to achieve this? Not limited to traditional relation database.

推荐答案

这些通常称为审计表。我通常管理这是在数据库上使用触发器。对于来自源表的每个插入/更新，触发器将数据复制到另一个名为相同表名的表中，并附加一个_AUDIT（命名约定无关紧要，这只是我使用的）。 ORACLE为你提供了称为日志表的东西。使用ORACLE设计器（或手动）可以实现相同的事情，并且经常开发人员将_JN放到日志/审计表的末尾。然而，这样做的工作原理相同，源表上的触发器将数据复制到审计表中。

These are commonly called audit tables. How I generally manage this is using triggers on the database. For every insert/update from a source table the trigger copies the data into another table called the same table name with an _AUDIT appended to it (the naming convention does not matter, it's just what I use). ORACLE provides you with something called journal tables. Using ORACLE designer (or manually) you can achieve the same thing and often developers put a _JN to the end of the journal/audit table. This, however, works the same, with triggers on the source table copying data into the audit table.

编辑：
我还应该注意，一个新的单独模式来管理您的审计表，或者您可以使用源表将它们保留在模式中。我这两个，它只是取决于情况。

I should also note that you can create a new separate schema to manage just your audit tables or you can keep them in your schema with the source tables. I do both, it just depends on the situation.

这篇关于如何存储数据的更改历史记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！