What is the best way to keep changes history to database fields?

Question

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.

Answer 1

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).