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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-01-25 : 09:18:08
|
| Anthony writes "I've read your article on using trees and hierarchy in SQL Server and I have a further question. Two useful ways of doing this are the Nested Set Model (lft and rgt columns) and adding a level and hierarchy column (not sure what this method is called, but it adds a hierarchy column for each node which shows the full hierarchy of node ids e.g. 1.3.5.7 for node 7 with a level of 4).The problem that I've come across is this: What is the best way to extend these models (or come up with a new one) when you have to keep a full audit table of changes e.g. have a full history table and also cope with the possibility of link types and link percentages. We only have these node percentages in a few special cases. e.g. node 7 is allocated 50/50 to node 5 and 6 for P&L, but 100% to node 5 for Risk.The Nested Set Model doesn't seem to cope very well with having to keep a history as there are a lot of updates for new node inserted, though the other model doesn't seem to cope very well with having linktypes and linkvalues.Have you seen any examples of this or do you have any ideas.Regards,Anthony Lloyd" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-01-25 : 13:13:24
|
| Unfortunately relational data models don't handle tree/hierchical data models too well, you have to fudge it. One time I used a similar entire hierarchy column like you described (I called it lineage), and it worked pretty well. It was easy to update and maintain, but it wasn't a relational structure and it added extra space. However, it made it very easy to generate org charts and such.I think if you want to do this in such a way as to audit the tree, you should probably just stick with storing the affected child node(s) and its parent, and possibly its individual lineage. I can't see keeping an entire tree archived if only one or a few nodes are affected.I'm a little fuzzy on what link percentages and types are, so I can't say whether this helps or not. My experience with hierarchies doesn't extend past org charts or file systems; if you have more detail on the structure you're modeling we might have more insight. If you are modeling more than one parent per child I am definitely lost.Edited by - robvolk on 01/25/2002 14:04:13 |
 |
|
|
|
|
|
Trees in SQL