I am trying to find an approach for a project, where A user can edit records, and be able to see past versions of those records. Here is a simlified example schema, using a list:
TABLE list ( id int auto_increment primary key, user_id int, title varchar(255) ); TABLE list_tasks ( id int auto_increment primary key, list_id int, title varchar(255), order int, is_complete tinyint );
So a user may come in, and make several edits to the list, (i.e., add or remove tasks, re-order tasks, mark some complete, rename some, etc.), Then save them. At this point, I'd like to generate a 'version 2' of the list and tasks, and have them be able to view previous versions, but when they access the list, always get the latest version.
Is there a common approach / design pattern for dealing with versioning data in this way in a MySQL database?