How to make versioning like on a wiki?

Question

PHP

Hello!
There is a certain base (accounting) with a bunch of fields.
You need to somehow make versioning for it like on a wiki.
That is, if the user changed 2 fields, then the history should show which ones and from which value to which one.
Tell me, is there something ready?
Thank you!

Answer 1

I did not work with accounting databases, but I use JSON for storing the history of changes for my MySQL database. There is a separate history table with fields - table_name, table_id and history (JSON) with data of the array [] type, to which JSON format records are added, which are monitored. Triggers for adding, changing, deleting hang on the user table. As soon as changes are made in the user table, the triggers write the changes to the history table. This allows you to track the entire history of work with the record from the moment of creation to deletion without inserting code into any procedures or business logic, but working abstractly only using the database tools.

Answer 2

Math part:
- Chronological database ;
- Transaction time ;
- Temporality in SQL:2011
is a big read: one , two .

Answer 3

This is how you can do it for postgresql: https://wiki.postgresql.org/wiki/Audit_trigger

Answer 4

Make table history
id | article_id | old | changes
in changes write new to old old, you can write with json string