SQL TreeView Versioning  
Author Message
EricaLais





PostPosted: Top

SQL Server Developer >> SQL TreeView Versioning

Hi Al

I will try to be brief - as I guess you can go on explaining recursive Trees and its Representation in a SQL table forever! Now, I need to add "Versioning" to these Trees and it is becoming a head ace for me. My Question Up front is, what is the Quickest Way to achieve Full Tree Versioning Without Compromising Storage and processing speed. (So basically I need to keep a history of "The Tree" - and must be able to go back in time and see "how" it looked

My Solution Works fine when you have small number of nodes (Ok, less than 20 :( ), but when you start adding more nodes, the speed and the amount of records you need to process decrease and increase exponentially respectively!

My Scenario - 3 Tables:
tblNodes (Pk=ID, Fk_tblNodes = ParentID, ParentID=-1 means record is the Root
ID Name ParentID Orde
1 A -1
2 B 1
3 C 1
Recursive Function starts at Parent (ParentID=-1 - always only 1 parent per table) and construct the "Current Tree
This table has a trigger "OnUpdate" and "OnInsert" - so-to-speak that copies the "pre-updated" or "newly-inserted" record into the shadow tabl
tblNodeShadow (Pk= VersID, ID from tblNode as FK in tblNodeShadow
VersID ID Name ParentID Order TimeSm
1 1 A -1 1
2 2 B 1 1
3 3 C 1 2
On Insert into "tblNodes" the Trigger insert the above data in "tblNodeShadow

tblVersionLookup (VersID from tblNodeShadow as FK in tblVersionLookup
VersID Versio
1
2
3
The version lookup table combines records from tblNodeShadow and apply a version to it as a last ste

NOW: I change a record in "tblNodes" - Say I change the "B" to "BB
"tblNodes" Looks like this now
ID Name ParentID Orde
1 A -1
2 BB 1
3 C 1
...and "tblNodeShadow" like
VersID ID Name ParentID Order TimeSm
1 1 A -1 1
2 2 B 1 1
3 3 C 1 2
4 1 AA -1 1
...and "tblVersionLookup" like
VersID Versio
1
2
3
4
2
3

So if I want to "go" back to version 1 of the Tree, I simply create a Temp table in a stored proc and insert "Select VersID from tblVersionLookup Where Version=1
This Temp table will be the basis for my recursion algorithm which will successfully retrieve my original version 1 -

NOW - how do I achieve the same results, but without making a duplicate copy of "tblVersionLookup" every time I change a record in "tblNodes

Help will be priceles

Thank
Arn

SQL Server306