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?