What's the best way to create a record keeping table for all actions in my database?

I have a number of tables inside one database and I want to create another that records all movements in every other table - adds, removals, delete and edits, which can then be reviewed by a user with top-level administrative privileges and reversed if necessary at the click of a button.

Is there a tried and tested method for this or a native feature of (InnoDB) MySQL useful for this?

If possible, can you point me to resources specific to this please. I'm working on my first database so I apologise if this has been asked here before, but I couldn't find it.


The idea of being able to roll back individual changes to a database is problematic. Records are related to other records in RDBMS, so rolling back one change may cause referential integrity problems. This means that any kind of rollback mechanism is going to have to be smart enough to know what the referential integrity constraints are and either cascade rollbacks or prevent rollbacks that would cascade (depending on your business rules).

Another approach which you might want to consider instead is to put all changes into a work queue and let the administrator approve them before they are written into the database. In this way the contents of your database would be "moderated". If there is a very high risk of data changes being rejected then the pre-approval approach may be a good one for you.

On the other hand, if it would be rare for a change to be rejected, then really what you want is an auditing system that records changes. Rolling back a change is difficult, so many it just needs to be done manually. The important thing is knowing what changed and how - and perhaps who changed it so you can avoid undesirable changes in the future.

A common approach for auditing changes to data is to have a history/audit table for each table that you are tracking. The structure of the history/audit table is the same as the main table, but it adds auditing columns, typically:

  • Date/Time of the change
  • User who made the change
  • Change action (insert/update/delete)
  • Source of the change (if multiple systems/sources can change the data)

Essentially every time there is a write into the main table, the new main table values are also recorded in the audit table, with the extra columns filled out. In the case of a delete, use the last known values. Sometimes the audit record is written automatically by a trigger, sometimes people write application logic to record this information.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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