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.
table_name，field_name，prev_value，current_val，time，user。
But it looks kind of hacky and ugly. Is there a better design?
但它看起来有点hacky和丑陋。有更好的设计吗？
Thanks.
谢谢。
4 个解决方案
#1
21
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).
Field_value有时会被拆分为多个字段，以便原始支持原始表中的实际字段类型（但是这些字段中只有一个将被填充，因此数据被非规范化;一种变体是将上表拆分为每个类型的一个表） 。
Other meta data such as field_type, user_id, user_ip, action (update, delete, insert) etc.. can be useful.
其他元数据，如field_type，user_id，user_ip，action（更新，删除，插入）等可能很有用。
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).
前两种方法通常使用非常轻微索引的表（或根本没有索引且没有参照完整性），从而使写入惩罚最小化。尽管如此，有时平板日志文件可能是首选，但当然功能上大大减少了。 （基本上取决于您是否需要由其他系统分析的实际审核/日志，或者历史记录是主系统的一部分）。
#2
6
A different way to look at this is to time-dimension the data.
另一种看待这种情况的方法是对数据进行时间维度。
Assuming your table looks like this:
假设你的表看起来像这样：
create table my_table (
my_table_id number not null primary key,
attr1 varchar2(10) not null,
attr2 number null,
constraint my_table_ak unique (attr1, att2) );
Then if you changed it like so:
然后，如果你改变它：
create table my_table (
my_table_id number not null,
attr1 varchar2(10) not null,
attr2 number null,
effective_date date not null,
is_deleted number(1,0) not null default 0,
constraint my_table_ak unique (attr1, att2, effective_date)
constraint my_table_pk primary key (my_table_id, effective_date) );
You'd be able to have a complete running history of my_table, online and available. You'd have to change the paradigm of the programs (or use database triggers) to intercept UPDATE activity into INSERT activity, and to change DELETE activity into UPDATing the IS_DELETED boolean.
您将能够拥有my_table，在线和可用的完整运行历史记录。您必须更改程序的范例（或使用数据库触发器）来拦截UPDATE活动到INSERT活动，并将DELETE活动更改为UPDATing IS_DELETED布尔值。
Unreason:
非理性：
You are correct that this solution similar to record-based auditing; I read it initially as a concatenation of fields into a string, which I've also seen. My apologies.
你说这个解决方案类似于基于记录的审计是正确的;我最初读它是一个字段连接成一个字符串，我也看到了。我很抱歉。
The primary differences I see between the time-dimensioning the table and using record based auditing center around maintainability without sacrificing performance or scalability.
在时间维度表和使用基于记录的审计中心之间看到的主要区别在于可维护性而不牺牲性能或可伸缩性。
Maintainability: One needs to remember to change the shadow table if making a structural change to the primary table. Similarly, one needs to remember to make changes to the triggers which perform change-tracking, as such logic cannot live in the app. If one uses a view to simplify access to the tables, you've also got to update it, and change the instead-of trigger which would be against it to intercept DML.
可维护性：如果对主表进行结构更改，则需要记住更改影子表。同样，需要记住对执行更改跟踪的触发器进行更改，因为这样的逻辑不能存在于应用程序中。如果使用视图来简化对表的访问，您还必须更新它，并更改反对它的反向触发器来拦截DML。
In a time-dimensioned table, you make the strucutural change you need to, and you're done. As someone who's been the FNG on a legacy project, such clarity is appreciated, especially if you have to do a lot of refactoring.
在时间尺寸表中，您可以进行所需的结构更改，并且您已完成。作为一个遗留项目的FNG的人，这种清晰度是值得赞赏的，特别是如果你需要进行大量的重构。
Performance and Scalability: If one partitions the time-dimensioned table on the effective/expiry date column, the active records are in one "table", and the inactive records are in another. Exactly how is that less scalable than your solution? "Deleting" and active record involves row movement in Oracle, which is a delete-and-insert under the covers - exactly what the record-based solution would require.
性能和可伸缩性：如果在有效/到期日期列上对时间维度表进行分区，则活动记录位于一个“表”中，而非活动记录位于另一个“表”中。究竟如何比您的解决方案更低的可扩展性？ “删除”和活动记录涉及Oracle中的行移动，这是一个删除和插入 - 正是基于记录的解决方案所需要的。
The flip side of performance is that if the application is querying for a record as of some date, partition elimination allows the database to search only the table/index where the record could be; a view-based solution to search active and inactive records would require a UNION-ALL, and not using such a view requires putting the UNION-ALL in everywhere, or using some sort of "look-here, then look-there" logic in the app, to which I say: blech.
性能的另一面是，如果应用程序在某个日期查询记录，则分区消除允许数据库仅搜索记录所在的表/索引;搜索活动和非活动记录的基于视图的解决方案需要UNION-ALL，而不使用这样的视图需要将UNION-ALL放在任何地方，或者使用某种“look-here，then look-there”逻辑应用程序，我说：blech。
In short, it's a design choice; I'm not sure either's right or either's wrong.
简而言之，它是一种设计选择;我不确定是对还是错。
#3
4
In our projects we usually do it this way: You have a table
在我们的项目中，我们通常这样做：你有一张桌子
properties(ID, value1, value2)
then you add table
然后你添加表格
properties_audit(ID, RecordID, timestamp or datetime, value1, value2)
ID -is an id of history record(not really required)
ID - 是历史记录的ID（不是真的需要）
RecordID -points to the record in original properties table.
RecordID -points指向原始属性表中的记录。
when you update properties table you add new record to properties_audit with previous values of record updated in properties. This can be done using triggers or in your DAL.
更新属性表时，将新记录添加到properties_audit，并在属性中更新以前的记录值。这可以使用触发器或在DAL中完成。
After that you have latest value in properties and all the history(previous values) in properties_audit.
之后，您拥有属性中的最新值以及properties_audit中的所有历史记录（以前的值）。
#4
2
I think a simpler schema would be
我认为一个更简单的架构
table_name, field_name, value, time, userId
No need to save current and previous values in the audit tables. When you make a change to any of the fields you just have to add a row in the audit table with the changed value. This way you can always sort the audit table on time and know what was the previous value in the field prior to your change.
无需在审计表中保存当前值和先前值。当您对任何字段进行更改时，您只需在审计表中添加一行并使用更改的值。这样，您始终可以按时对审计表进行排序，并在更改之前了解字段中的先前值。
#1
21
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).
Field_value有时会被拆分为多个字段，以便原始支持原始表中的实际字段类型（但是这些字段中只有一个将被填充，因此数据被非规范化;一种变体是将上表拆分为每个类型的一个表） 。
Other meta data such as field_type, user_id, user_ip, action (update, delete, insert) etc.. can be useful.
其他元数据，如field_type，user_id，user_ip，action（更新，删除，插入）等可能很有用。
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).
前两种方法通常使用非常轻微索引的表（或根本没有索引且没有参照完整性），从而使写入惩罚最小化。尽管如此，有时平板日志文件可能是首选，但当然功能上大大减少了。 （基本上取决于您是否需要由其他系统分析的实际审核/日志，或者历史记录是主系统的一部分）。
#2
6
A different way to look at this is to time-dimension the data.
另一种看待这种情况的方法是对数据进行时间维度。
Assuming your table looks like this:
假设你的表看起来像这样：
create table my_table (
my_table_id number not null primary key,
attr1 varchar2(10) not null,
attr2 number null,
constraint my_table_ak unique (attr1, att2) );
Then if you changed it like so:
然后，如果你改变它：
create table my_table (
my_table_id number not null,
attr1 varchar2(10) not null,
attr2 number null,
effective_date date not null,
is_deleted number(1,0) not null default 0,
constraint my_table_ak unique (attr1, att2, effective_date)
constraint my_table_pk primary key (my_table_id, effective_date) );
You'd be able to have a complete running history of my_table, online and available. You'd have to change the paradigm of the programs (or use database triggers) to intercept UPDATE activity into INSERT activity, and to change DELETE activity into UPDATing the IS_DELETED boolean.
您将能够拥有my_table，在线和可用的完整运行历史记录。您必须更改程序的范例（或使用数据库触发器）来拦截UPDATE活动到INSERT活动，并将DELETE活动更改为UPDATing IS_DELETED布尔值。
Unreason:
非理性：
You are correct that this solution similar to record-based auditing; I read it initially as a concatenation of fields into a string, which I've also seen. My apologies.
你说这个解决方案类似于基于记录的审计是正确的;我最初读它是一个字段连接成一个字符串，我也看到了。我很抱歉。
The primary differences I see between the time-dimensioning the table and using record based auditing center around maintainability without sacrificing performance or scalability.
在时间维度表和使用基于记录的审计中心之间看到的主要区别在于可维护性而不牺牲性能或可伸缩性。
Maintainability: One needs to remember to change the shadow table if making a structural change to the primary table. Similarly, one needs to remember to make changes to the triggers which perform change-tracking, as such logic cannot live in the app. If one uses a view to simplify access to the tables, you've also got to update it, and change the instead-of trigger which would be against it to intercept DML.
可维护性：如果对主表进行结构更改，则需要记住更改影子表。同样，需要记住对执行更改跟踪的触发器进行更改，因为这样的逻辑不能存在于应用程序中。如果使用视图来简化对表的访问，您还必须更新它，并更改反对它的反向触发器来拦截DML。
In a time-dimensioned table, you make the strucutural change you need to, and you're done. As someone who's been the FNG on a legacy project, such clarity is appreciated, especially if you have to do a lot of refactoring.
在时间尺寸表中，您可以进行所需的结构更改，并且您已完成。作为一个遗留项目的FNG的人，这种清晰度是值得赞赏的，特别是如果你需要进行大量的重构。
Performance and Scalability: If one partitions the time-dimensioned table on the effective/expiry date column, the active records are in one "table", and the inactive records are in another. Exactly how is that less scalable than your solution? "Deleting" and active record involves row movement in Oracle, which is a delete-and-insert under the covers - exactly what the record-based solution would require.
性能和可伸缩性：如果在有效/到期日期列上对时间维度表进行分区，则活动记录位于一个“表”中，而非活动记录位于另一个“表”中。究竟如何比您的解决方案更低的可扩展性？ “删除”和活动记录涉及Oracle中的行移动，这是一个删除和插入 - 正是基于记录的解决方案所需要的。
The flip side of performance is that if the application is querying for a record as of some date, partition elimination allows the database to search only the table/index where the record could be; a view-based solution to search active and inactive records would require a UNION-ALL, and not using such a view requires putting the UNION-ALL in everywhere, or using some sort of "look-here, then look-there" logic in the app, to which I say: blech.
性能的另一面是，如果应用程序在某个日期查询记录，则分区消除允许数据库仅搜索记录所在的表/索引;搜索活动和非活动记录的基于视图的解决方案需要UNION-ALL，而不使用这样的视图需要将UNION-ALL放在任何地方，或者使用某种“look-here，then look-there”逻辑应用程序，我说：blech。
In short, it's a design choice; I'm not sure either's right or either's wrong.
简而言之，它是一种设计选择;我不确定是对还是错。
#3
4
In our projects we usually do it this way: You have a table
在我们的项目中，我们通常这样做：你有一张桌子
properties(ID, value1, value2)
then you add table
然后你添加表格
properties_audit(ID, RecordID, timestamp or datetime, value1, value2)
ID -is an id of history record(not really required)
ID - 是历史记录的ID（不是真的需要）
RecordID -points to the record in original properties table.
RecordID -points指向原始属性表中的记录。
when you update properties table you add new record to properties_audit with previous values of record updated in properties. This can be done using triggers or in your DAL.
更新属性表时，将新记录添加到properties_audit，并在属性中更新以前的记录值。这可以使用触发器或在DAL中完成。
After that you have latest value in properties and all the history(previous values) in properties_audit.
之后，您拥有属性中的最新值以及properties_audit中的所有历史记录（以前的值）。
#4
2
I think a simpler schema would be
我认为一个更简单的架构
table_name, field_name, value, time, userId
No need to save current and previous values in the audit tables. When you make a change to any of the fields you just have to add a row in the audit table with the changed value. This way you can always sort the audit table on time and know what was the previous value in the field prior to your change.
无需在审计表中保存当前值和先前值。当您对任何字段进行更改时，您只需在审计表中添加一行并使用更改的值。这样，您始终可以按时对审计表进行排序，并在更改之前了解字段中的先前值。