Data history tracking best practice

Question

we need to keep track of data modification during the time on some table. We need some advice about how to achieve this task. We have two streets to follow in our mind. 1) Create a table with the following records: userid, date modification, table name, fieldname, fieldtype, fieldvalue. In this way, we will track with a trigger.

2) Add a state field on all the table we need to track history called Status. This field will have the following values: I = inserted - D = deleted - M = Modified with relative date od modification. In this way we can always know the latest valid row and all the previous data modification 3) It’s in your minds  What do you suggest?

Answer 1

I have done this multiple times on PostgreSQL using separate "history" schema and triggers.

The tables in the "history" schema are identical to the real tables but with history_id PK and event timestamp added. Tables from "history" schema don't have any constraints. Also you need to create field for action if you need to track deletion as well.

In postgreSQL you can easily create such table using such CREATE statement:

CREATE TABLE history.tbl AS
  (history_id BIGSERIAL PRIMARY KEY, 
  event_time TIMESTAMP DEFAULT NOW(), 
  action CHAR(1), 
  LIKE public.tpl);

After this you should create trigger which will insert into the history table on insert, update, delete.