I'm working on a CMS platform that interacts with a MySQL database.
I need to write a history system that can show me a snapshot of the database at any time.
For example, if I have three tables and I add, edit, and delete entries over a period of one week and want to know the status of the platform at a time. date and at a specific time this week, what is the best algorithm / logic that I should implement in the code?
Here is my approach.
Let's say we have a table called 'users':
Id | Name | Last edition 1 | John Doe | 31-04-2019 2 | Jane Doe | 31-04-2019
And we make an edition at Id 1:
Id | Name | Last edition 1 | John Joe | 03-05-2019 2 | Jane Doe | 31-04-2019
What triggers this entry in another table (history) that follows the changes:
Id | Table | Action | Old value | New value | Date 1 | users | edit | "1, John Doe, 31-04-2019" | "1, John Joe, 03-05-2019" | 03-05-2019
If I had to request the data stored at 02-05-2019 for example, the treatment of the table & # 39; historic & # 39; should allow me to get the data stored at that time, is not it? (which looks exactly like the data in the first table).
Do I have to wait for performance issues if the table & # 39; historic & # 39; reaches thousands or more entries?