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?

  • Note that they're libraries for this. For instance if you use Hibernate, there is Hibernate Envers. So if you have some framework handling DAO for you, try to search if there is something like this.
    – Walfrat
    Nov 16, 2016 at 10:25

3 Answers 3


Its pretty common to want do that in a db. Though you are putting a twist on it in that you want to track a revision for a list of items.

One way to do this might be to alter the structure such as

Alter table lists add revision_id integer;
Alter table list_tasks add revision_id integer;

Create Table revisions
   id int autoincrement... (revision id)
   list_id int...
   revdate datetime...

When the user saves their list, create a new revision in the revisions table above and assign that value to the list items in list_tasks and then to the revision id in lists to mark that id as the 'current' revision. When the user edits the items, dont edit the existing items - rather, insert new ones with a new revision id and update the list table with that revision to mark it as the current one.

Then to list the current items, list the items from the current revision id specified in the lists table. To browse previous versions, you can get a list of previous revisons of the lists from the revisions table, and then list the individual items based on that id.


This solution uses a separate auditing table. It has pros and cons. You may prefer to eliminate the old records from your main table. Performance improvement may be negligible.

Add the following fields to each audited table:

AddUserID      int <whatever your system uses>
AddDateTime    datetime
UpdateUserID   int <whatever your system uses>
UpdateDateTime datetime
CurrentVersion int
IsDeleted      bit

You will need to update these fields each time the data change. CurrentVersion gets incremented by 1 (It could be used as a way to lock a record, but that is another question.) IsDeleted provides a "soft delete" so it can be referenced in the future.

Separate Audit Tables Each table should have a corresponding _Archive or _History version of the table. These probably don't need to be indexed in the same way. Obviously a single primary key field will not apply. You should be able to make a composite key out of the ID field and UpdateDateTime.

Using a trigger (This will address changes made inside or outside your code. You can decide if this works for your situation.) or other coding, when a record is appended, updated or deleted, a copy of the record is placed in the archive/history table. All of the versions and other auditing fields are maintained. This will tell you what users did what when. The table can be compared to itself to see when a record was changed or to see trends.

I've seen this work well over the last few years. I would like to hear about drawbacks I may not be considering.

  • I'm upvoting this as well as GrandMasterB's, both are good and which one to use depends on more details on the specific needs.
    – junky
    Feb 28, 2012 at 21:29

I would suggest you read this well detailed article.


Another approach is to have a version_id column in your table and a 'current' flag which specifies which row is the current one. Each time you need an update, you can insert a new row and set the existing version 'current' flag to 0 / false and the newly added row to 1.

This way you can create a view showing only those with current flag set.

Not the answer you're looking for? Browse other questions tagged or ask your own question.