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 |
|
assembly
Starting Member
2 Posts |
 Posted - 2006-09-14 : 17:49:29
|
| We have a nested set design in our database.The design allows multiple instances of nodes in the hierarchy. Each node has a combination of node name and its instance id as the primary key. We also maintain a unique_qty column that has the unique number of nodes below a particular node. This unique qty basically ignores the multiple instances of nodes below it and counts only the distinct node names(ignoring their instance ids).the problem that im facing is...how do i update the unique_qty when i perform any move in the tree.Thanks in advance,assembly |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-09-14 : 18:14:19
|
| Are you using SQL 2005 by any chance? If so, then I recommend to not use nested sets and just use the new recursive features of SQL 2005.- Jeff |
 |
|
|
assembly
Starting Member
2 Posts |
Posted - 2006-09-14 : 18:40:06
|
| we cannot change the database model at this point.but let me explain my ideas abt the problemfor any move in the tree, the unique_qty of nodes the in hierarchy changes.1) i can calculate the unique qty for each node in the heirarchy till the root node, and then update themOR2) for each node in the subtree that is being moved, i can find out how it affects the unique qty of its original root and how its affects the root where the subtree is being moved to.any more ideas or suggestions? |
|
|
|
|
|
|
updating nodes in a nested set model