I am using MySQL database (with Django ORM). I want to maintain the database audits similar to StackOverflow, Quora, Wikipedia, etc. These websites maintain the revisions of the changes in the database so that the changes made by users/ admins can be reverted.
Create a duplicate table to keep the log of the changes made in the database. For each entry record the changes,timestamp and admin/user who made this changes. Use these entries to find diffs and revert to any point. SO keeps the history of revisions in a separate
Instead of making separate tables for each table in the database, make a table like this for audits.-
- id - revision id
- scope_id - id of database table
- scope_type - Question, Topic, User
- item_id - row id of Question/topic/user in the database table
- event - Edited, Added, Reverted, Removed
- user_id - who triggered the event
- serialized_item_column - serialized data in the json format
Then the serialized data can be used to calculate diffs and revert a particular entry.
In context of crowdsourcing platform like wiki/SO where multiple users/admins can make changes,
- Which of the two database design will be better ?
- If I use one duplicate table per table for revisions, i.e. one for the current value and one with all the previous revisions, is that a scalable way for a website with millions of entries and much more revisions ?