如何建模表以记录其他表的记录值的变化？(How would you model a table to record changes in other tables' records'

如何建模表以记录其他表的记录值的变化？(How would you model a table to record changes in other tables' records' values?)

我正在编写一个模型来记录对其他模型所做的更改，以便记录每个属性的变化。 我正在寻找有关如何最好地构建更改记录表的输入。

例如，我有一个带有name属性的User模型。 当用户的名称从Bob更改为Ted时，我想将Change记录保存到数据库。

此外，如果用户同时更新用户的多个属性，我希望将这些属性记录为单个Change 。 例如， User将Bob和bob@bobsdomain.com的name和email属性分别bob@bobsdomain.com为Ted和ted@teddy.com 。

正在更改的给定对象上的属性集是任意的。 你会如何构建桌子？

目前，我正在做类似以下（简化）的事情：

Changes: user_id:integer changed_fields:string old_values:text new_values:text

在上面的示例中，这些记录如下所示：

:changed_fields => "name", :old_values => "Bob", :new_values => "Ted"

和

:changed_fields => "name,email", :old_values => "Bob,bob@bobsdomain.com", :new_values => "Ted,ted@teddy.com"

在Change模型中，我有一个特殊的getter / setter方法，它解析输入/输出以映射到特定的格式。

有没有更好的方法来模拟这种事情？

如果不是，那么格式化数据库的值以使解析输入/输出最佳的最佳方法是什么，因为这些值可以是任意的。

我使用Postgres作为我的DB和ActiveRecord作为ORM。

I am writing a model to record the changes that are made to other models so that there is a record of every attribute change. I'm looking for input on how best to structure the change record table.

For instance, I have a User model with a name attribute. I want to save a Change record to the database when that user has its name changed from Bob to Ted.

Furthermore, if a user updates multiple attributes on the User at the same time I would like to record these as being a single Change. For instance, a User changes both the name and email attributes from Bob and bob@bobsdomain.com to Ted and ted@teddy.com, respectively.

The set of attributes on a given object that are being changed are arbitrary. How would you structure the table?

Currently, I am doing something like the following (simplified):

Changes: user_id:integer changed_fields:string old_values:text new_values:text

In the above examples these records would look like the following:

:changed_fields => "name", :old_values => "Bob", :new_values => "Ted"

and

:changed_fields => "name,email", :old_values => "Bob,bob@bobsdomain.com", :new_values => "Ted,ted@teddy.com"

On the Change model I have special getter/setter methods that parse the input/output to map to specific formatting.

Is there a better way to model this kind of thing?

If not, what is the best way to format the values for the database to make parsing the input/output work best, given that the values can be arbitrary.

I am using Postgres as my DB and ActiveRecord as the ORM.

最满意答案

存储旧值和新值都是多余的。 您可以在更改时仅存储新值，并确保在创建新行时也添加更改记录。 如果一行已更改N次，则更改表中将包含N + 1条记录，如果需要，您可以追溯查找它们之间的差异。

实际上，一种方法是只有一个表包含所有版本的数据。 而不是就地更新任何行，通过插入新行进行编辑。 我使用这种方法，使用布尔“最新”字段，或使用另一个表中的外键，来引用每行的当前版本。

这几天虽然我正在使用django-reversion软件包为我实现修订跟踪。 它使用单个表来记录所有跟踪表的所有版本，并在每个版本中存储每个对象状态的JSON表示。 它使用附加表来跟踪整个更改集，因此它可以跨多个对象进行版本更改。

最适合您的解决方案取决于您在桌面上有效执行哪些操作，以及您的ORM易于使用的操作。 我希望这个答案有助于指出在其他环境中有效的一些方法。

Storing both the old and new value is redundant. You could instead store just the new value at the time of the change, and make sure you also add a change record when a new row is created. If a row has been changed N times, it will have N+1 records in the change table, and you can find the differences between them retrospectively if you need to.

In fact, one approach is to have only one table containing all versions of the data. Instead of updating any rows in-place, make edits by inserting new rows. I've taken this approach while either using a boolean "latest" field, or using a foreign key from another table, to refer to the current version of each row.

These days though I'm using the django-reversion package to implement revision tracking for me. It uses a single table to record all versions of all tracked tables, storing a JSON representation of the state of each object at each version. It uses an additional table to track whole change-sets, so it can version changes that span multiple objects.

The solution that's best for you will depend on which operations you need to be able to do efficiently on your tables, and on what your ORM makes easy for you to work with. I hope this answer has been helpful at pointing out some approaches that have worked in other settings.