I've been asked if I can keep track of the changes to the records in a MySQL database. So when a field has been changed, the old vs new is available and the date this took place. Is there a feature or common technique to do this?
If so, I was thinking of doing something like this. Create a table called
changes. It would contain the same fields as the master table but prefixed with old and new, but only for those fields which were actually changed and a
TIMESTAMP for it. It would be indexed with an
ID. This way, a
SELECT report could be run to show the history of each record. Is this a good method? Thanks!