|Help with storing Hierarchical data using Nested Intervals and Matrix Encoding [message #274365]
||Mon, 15 October 2007 17:58
Registered: October 2007
I have been working on an implementation of Nested Invervals to store some hierarchial data.|
The code that I am writing is based on Vadim Tropashkos book SQL Design Patterns.
I have run into a number of problems with the code in the book and I am wondering if anyone has worked through chapter 5 of this book and has tried to implement Nested Intervals using the Matrix encoding.
I have had several problems with the accuracy of the code in the book. I can not get some of the proposed queries proposed in the book to work. For example:
To query for descendants:
From MatrixTreeNodes descendant,MatrixTreeNodes node
Where descendant.a11*node.a21 >= descendant.a21*node.a11 and
descendant.a11*node.a22 >= descendant.a21*node.a12
and node.name=some name
This does not work for me. It returns all nodes in the tree. I had to go back to this query in the book:
from MatrixTreeNodes descendant, MatrixTreeNodes node
where descendant.a11/descendant.a21 between node.a11/node.a21
and node.name = … -- predicate uniquely identifying a node
The first query is supposed to be equivalent to the second query with all the expressions rewritten without divisions.
When I went through the process of rewritting the query to get rid of divisions, I come up with something completely different:
join tblSiteLogonHierarchy decendants
on decendants.a11 * parent.a21> decendants.a21 * parent.a11 and
((decendants.a11 * parent.a21) -(parent.a22*decendants.a11))
((parent.a11 * decendants.a21) -(parent.a21*decendants.a21))
parent.lSiteLogonID = @lSiteLogonID
This query works but leads me to the problem that has sunk the whole concept for me.
I know this doesnt concern oracle types, when I try to implement this on SQL Server
WHen you get up to a large tree, conditions like decendants.a11 * parent.a21> decendants.a21 * parent.a11 blow up because of over flow. I am using values larger than 9223372036854775807 which is the maximum value that can fit into a bigint.
I need to implement this on both SQL Server and Oracle.
Anyway, has there been other attempts at similar encodings for hierarchial data.
I can always go back to materialized path or adjacency list. Dont want to because its not as interesting.
Is there something similar where the numbers do not grow as fast?