wood burning stoves 2.0
The moose likes Websphere and the fly likes Tree Stored Procedure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Products » Websphere
Bookmark "Tree Stored Procedure" Watch "Tree Stored Procedure" New topic

Tree Stored Procedure

Sharad Agarwal
Ranch Hand

Joined: Sep 11, 2002
Posts: 167

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.)

Alco-Haul: We move spirits.
Demented Deliberations of a Dilettante
I agree. Here's the link: http://aspose.com/file-tools
subject: Tree Stored Procedure
It's not a secret anymore!