Version i am talking about here is not of version control systems. Like in SO i got some textual data(compare them to questions) that could be versioned(in the sense edited, saved, rollback). Content of Each edit, update, save is to be saved as new version based on option by end-user. How should i design a table for this kind of task?

Current Implementation

id pk auto increment
parentid defaults to 0
version  defaults to 0

Sample records

id     content        parentid        version
1      Hello World        0              0
2      Hello Doggy        1              1
3      Hello Kitty        1              2

as you can see each record with a parent id equal to existing id will be saved in incremental version(which is actually calculated in server side code). Which is displayed in the UI like below

1.Hello World
    -Hello Doggy(Version1)
    -Hello Kitty(Version2)

where each is displayed as link so that further editing can be done. Things works like expected no problem but we want to improve on this & i think it would be best to start from the table designs.

  • How big (length of text) will a typical content entry be? Same again for a typical edit? Commented Oct 11, 2011 at 13:30
  • @MarkStorey-Smith yes it would be the same around 400 is the max.Sorry i forgot to mention that in table. Commented Oct 11, 2011 at 14:54

1 Answer 1


You already have one option: keep it in one table
This is a "Slowly Changing Dimension".

The other options is to split "Current" and "Previous" versions into 2 tables. When you update the current table, a trigger can populate the "Previous" table. Or an UPDATE..OUTPUT.

You can query this data in 2 ways:

  • combined into one recordset, client code splits it up
  • two record sets, one current + one previous. This can be done in a stored procedure too.

I've implemented both: it depends on access patterns and usage.

If I'm reading all versions in one go or JOINING onto it, then one table. If "previous" data is only on demand then tables.

In your case, can you defer the previous versions until a user clicks to load it? Is it rare to actually need previous versions? If yes to either, then I'd suggest 2 tables. This way, you only shift and render less data only for current versions.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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