Mysql – Concept to keep track of changes in all tables

database-designMySQL

In my current project I use about 10 MySQL tables (Doctrine as DBAL). I'd like to keep track of all changes in the databases (insertions, updates and deletions).

At the moment there are two approaches which might be the solution:

  • Create a table named history which stores all changes. Each time there is a change insert a new row in this table. It might have this fields:

    id

    target_table (name of the table)

    target_id (ID of the saved entry)

    created_at

    data (serialized data of the saved row)

  • Add to each the table the following columns:

    history_id (NULL: most recent entry, history_id = b.id: an old state of b, history.id pointing to nowhere: deleted object)

Are there any other approaches to keep track of all changes? Would you recommend one of my approaches?

Best Answer

The answer depends on what you intend to do with the data. Are you going to be doing frequent queries against it or do you only need it for the occasional lookup of what happened when, to rollback bad changes, or for regulatory reasons?

In the first case above, use the second process you descibed although I would add a column to denote the active record. I would also add a record created date. I wouild also create a view of just the active records and use that for allof my code that needs to see the current data.

In the second case above, I would have audit tables that are populated through triggers (the offective way to create audit records for any change to the db) and that also include the data of the change and the user or application that made the change as well as the old and new values.

Related Question