Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

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.

share|improve this answer
    
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 '12 at 21:29
add comment

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.

share|improve this answer
    
+1 for using date over version number :) –  junky Feb 28 '12 at 21:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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