Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
content
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.

share|improve this question
    
How big (length of text) will a typical content entry be? Same again for a typical edit? –  Mark Storey-Smith Oct 11 '11 at 13:30
    
@MarkStorey-Smith yes it would be the same around 400 is the max.Sorry i forgot to mention that in table. –  Deeptechtons Oct 11 '11 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.

share|improve this answer
    
you rock. you seem to be the only person answering all my queries here. I will wait for some more answering before marking upvoted :) –  Deeptechtons Oct 11 '11 at 14:55
    
+1 nice........ –  garik Oct 12 '11 at 15:25

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.