Menu
I have a couple tables that I want to keep a revision history on. What is the best way to accomplish this? It's going to be several fields (20 or so).
Should I create a duplicate table and just submit the old data to that? Should I use triggers? Or should I create a separate table and just track the changes made?
954
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.
MySQL binary logs provide a very efficient way to track data changes for MySQL CDC. They contain events that describe the modifications to data. In short, binary logs contain all the information that is required to trace the global status of the server right from the time it was started.
We can use COMMIT command to make the changes, made in a current transaction, permanently recorded in MySQL database. Suppose if we run some DML statements and it updates some data objects, then COMMIT command will record these updates permanently in the database.
A common way to detect changes to a table between runs is with a query like this: SELECT COUNT(*),MAX(t) FROM table; But for this to work, a few assumptions must be true about your table: The t column has a default value of NOW()
We're pretty happy with our choice which is using two tables per versioned entity.
Tables would look similar to this:
Table person:
person_version)Table person_version:
person)As entries in person_version won't ever change, it's easy to cache them (as long as there aren't any references to tables that might change)
147
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
