Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
 Posted - 2005-10-24 : 14:20:16
|
| Hi,I'm using the adjacency list model of a tree and I'm concerned with data integrity.Here's the table...Tree====NodeID int PKParentNodeID int FKOrdinal intI have a web page page which displays the tree and there are arrows next to each branch which allow a user to move the branches up and down in order to order them.So a user might swap two branches with the same parent around. However, back at the database, someone may have given one those branches a different parent. In this case, to swap their order doesn't really make sense and could result in a duplication of ordinal values.To further explain, take this initial structure (ordinals in brackets) ...A (0)->A1 (0)->A2 (1)B (1)->B1 (0)->B2 (1)User 1 swaps the order of B1 and B2 hoping to get this...A (0)->A1 (0)->A2 (1)B (1)->B2 (0)->B1 (1)...but user 2 has modified the DB so it looks like this..A (0)->B1 (0)->A2 (1)B (1)->A1 (0)->B2 (1)So if user one's updates are processed by swapping the ordinals of the two branches then the DB would look like this...A (0)->B1 (1)->A2 (1)B (1)->A1 (0)->B2 (0)Which completely wipes the ordering of the branches.So does anyone have any tips on how to maintain data integrity in a tree? The easiest way would be to just use a concurrency control to stop an update being done if the DB has changed since the data was retrieved but that's a bit crude for me.Cheers, XF. |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-10-24 : 15:03:03
|
Why don't you use optimistic concurrency, I don't see why that would be crude.And since the branches can be swapped around like this, what integrity would you be maintining ?To me this looks like a concurrency problem.Implement a timestamp rowversion column for concurrency.rockmoose |
 |
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2005-10-24 : 17:21:31
|
| I've tried to display the integrity I'm trying to maintain in the example. The idea is that each of the nodes with the same parentID have a unique ordinal value.So user one wants to have B1 go from an ordinal value of 0 to 1 and B2 go from an ordinal value of 1 to 0. That would be two updates. But if user two has given B1 a different parentID then the two simple updates done by user one result in duplicate ordinals within two of the branches in the example which wouldn't be much use to an ORDER BY clause.Also, giving a node a different parentID doesn't count as re-ordering. You can only re-order nodes relative to nodes with the same parentID.Anyhow, I figure that there are a limited number of conflicts. As long as user two hasn't inserted or deleted a node into the scope being re-ordered by user one or re-ordered or changed in any way a range of nodes which intersect with the range of nodes being re-ordered by user one then there'd be no conflict.So that just means checking that the rows being re-ordered haven't been updated or deleted. So yes, a rowversion would suffice. What I was trying to suggest was crude was locking out the entire tree.Do you think its worth putting a unique constraint across parentID and ordinal? I think this would be good except it would require doing something like negating all of the ordinal values within the scope which is being re-ordered whilst the updates are happening.Cheers, XF. |
|
|
|
|
|
|
|
|
The integrity of a tree