posted 19 years ago
Hi,
We have three tables A, B and C which represent a tree like structure. Each row in A can have zero or more children, each represented by rows in B. Similarly each row in B can have zero or more children, each represented by rows in C.
To illustrate, here is an example. There are three nodes (rows) in A - A1, A2 and A3. A1 has two child nodes in B - B1 and B2. A2 has no children. A3 had children B3 and B4. B1 has two children C1 and C2. B2 has children C3, C4 and C5. B3 has no children. B4 has 3 children - C6, C7 and C8.
Now we need a stored procedure that, given the primary key for a row in A, returns the sub-tree for that node. So, for example, if you are given A1, you need to return the subtree under that node.
I was looking for suggestions for the data structure returned. A simple data structure could be one that flattens out the tree:
A1 B1 C1
A1 B1 C2
A1 B2 C3
A1 B2 C4
A1 B2 C5
It leads to some redundancy. But if there are not too many siblings and the data is small, it is a simple solution. Or I could return three structures with only the needed attributes.
Other ideas?
(Note - the situation and example presented here are simplifications that focus on the issue I am struggling with.)
