将更改历史记录保存到数据库字段的最佳方法是什么？ [英] What is the best way to keep changes history to database fields?

问题描述

例如，我有一个表，存储有关属性的详细信息。其中可能有业主，价值等。

是否有一个好的设计来保存每一个更改的所有者和价值的历史。我想为许多表做这个。类似于对表的审计。

我认为是保留一个包含字段的表格

code> table_name ， field_name ， prev_value ， current_val ， time ，用户。

$ b b

但它看起来有点丑陋和丑陋。是否有更好的设计？

感谢。

解决方案

几种方法

基于字段

  audit_field（table_name，id，field_name，field_value，datetime）
  

并且易于扩展到新表。

Field_value有时会分割为多个字段，以支持原始表格中的实际字段类型（但只有其中一个字段

其他元数据，例如field_type，user_id，以及其他元数据， user_ip，action（更新，删除，插入）等。

这些记录的结构很可能需要被转换才能被使用。 p>

以记录为基础

  audit_table_name id，field_1，field_2，...，field_n）
  

对于数据库中的每个记录类型，广义表，其具有作为原始记录的所有字段，加上版本字段（另外的元数据可能）。每个工作表的一个表是必要的。创建这样的表的过程可以自动化。

这种方法为您提供了与主数据结构非常相似的语义丰富的结构，所以用于分析和处理原始

$ b

前两种方法通常使用非常轻度索引的表（或者根本没有索引，并且没有引用完整性），使得写惩罚被最小化。仍然，有时平面日志文件可能是首选，但当然功能上大大减少。 （基本上取决于您是否希望实际的审计/日志将由其他系统分析，或历史记录是主系统的一部分）。

For example I have a table which stores details about properties. Which could have owners, value etc.

Is there a good design to keep the history of every change to owner and value. I want to do this for many tables. Kind of like an audit of the table.

What I thought was keeping a single table with fields

table_name, field_name, prev_value, current_val, time, user.

But it looks kind of hacky and ugly. Is there a better design?

Thanks.

解决方案

There are a few approaches

Field based

audit_field (table_name, id, field_name, field_value, datetime)

This one can capture the history of all tables and is easy to extend to new tables. No changes to structure is necessary for new tables.

Field_value is sometimes split into multiple fields to natively support the actual field type from the original table (but only one of those fields will be filled, so the data is denormalized; a variant is to split the above table into one table for each type).

Other meta data such as field_type, user_id, user_ip, action (update, delete, insert) etc.. can be useful.

The structure of such records will most likely need to be transformed to be used.

Record based

audit_table_name (timestamp, id, field_1, field_2, ..., field_n)

For each record type in the database create a generalized table that has all the fields as the original record, plus a versioning field (additional meta data again possible). One table for each working table is necessary. The process of creating such tables can be automated.

This approach provides you with semantically rich structure very similar to the main data structure so the tools used to analyze and process the original data can be easily used on this structure, too.

Log file

The first two approaches usually use tables which are very lightly indexed (or no indexes at all and no referential integrity) so that the write penalty is minimized. Still, sometimes flat log file might be preferred, but of course functionally is greatly reduced. (Basically depends if you want an actual audit/log that will be analyzed by some other system or the historical records are the part of the main system).

这篇关于将更改历史记录保存到数据库字段的最佳方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！