Sql-server – How to design a table for quick versioning of textual data

database-designsql serversql-server-2005version control

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.

Best Answer

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.

Related Question