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:


    target_table (name of the table)

    target_id (ID of the saved entry)


    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?

  • The second one appears to me as more effective if yo want to access history often.
    – Iľja
    Apr 6, 2012 at 16:33
  • It would be good to know you RDMS, it may already support auditing. Apr 6, 2012 at 17:06
  • @JonofAllTrades: Sorry, I forgot to mention that I'd like to use MySQL. I updated the question accordingly.
    – SecStone
    Apr 6, 2012 at 17:18

1 Answer 1


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.