Is there a "right" way to store data for a treeview control?

[Alisa]

Or if not right, maybe right"er"? I have a treeview control that displays up to five levels of data. Right now, I have the top level of things in one table ("jobs"), and the next four levels of things (parts, subparts, sub sub parts, etc.) in another table ("parts"). There is a field in the parts table that links to the jobs table. There is another field in the parts table that holds the PK of the parent for parts that are sub parts (so its a self join). I find it is possible, though not necessarily easy, to load the tree using this structure. I was just wondering if anyone has any better ideas about it, or if there is some standard way of doing it that I don't know about.

 

[stopher]

I think the question about how you store your data is purely governed by good relational database design and not by which tools you want to use to view your data. So your table structure (different table for each "level") is correct in my opinion.

The question is then how you can optimise the loading of this data for a tree view. In my limited experience I would use recordsets in nested loops to add each node to the treeview.

I suppose (and I'm thinking aloud here) you could construct an optimised view that you would use as the datasource to create the treeview. I guess it would look like this:
Parent____Child
part1_____subprt1
part1_____subprt2
subprt1___subsubpart1
subprt1___subsubpart2
etc

This then becomes a simple loop to add the nodes in code I think. So could we write a query that gets our normalised table structure into this format. I guess we could write a query for each level that gives the parent and child. Then UNION these five queries together to produce the above list.

Doing the above does sound much simpler than trying to code five levels since writing the five queries is rather easy. I don't know if it would work though or what the effect on performance would be.

Just had a thought. Still have to deal with the top level slightly differently since it doesn't have a parent (poor thing).

Chris

 

[Alisa]

That's pretty much what I was thinking. What I have set up is one query for each level - it's not THAT hard to load, but I just never know when I am just ignorant of a better way.

 

[Jurjen]

you should use recursion

A tree is a typical recursive structure, so to generate it, you should use recursion. Suppose your table structure is as follows:

JOBS
jobs_id (long) = primary key
jobs_code (text)

PART
part_id (long) = primary key
part_code (text)
part_idP (long) = optional foreign key to PART
jobs_id (long) = optional foreign key to JOBS

Then the algorithm could be something like the following:

[U]JobsLoop:[/U]
FOR EACH jobs:
   - create a root node N
   - call PartsLoop (jobs_id, N)

[U]PartsLoop (v_jobs_id, v_node):[/U]
FOR EACH part WHERE jobs_id = v_jobs_id:
   - call CreatePartNode (part_id, v_node)

[U]SubPartsLoop (v_part_id, v_node):[/U]
FOR EACH part WHERE part_idP = v_part_id:
   - call CreatePartNode (part_id, v_node)
    
[U]CreatePartNode (v_part_id, v_node):[/U]
   - create a child node of v_node N
   - call SubPartsLoop (v_part_id, N)

 

[Alisa]

Jurjen, I started out doing it that way, but now I just add all the nodes for each level at once, i.e., add all jobs, add all parts, add all subparts. I create a recordset for each level that provides the key to the parent, then I use Treeview1.Nodes.Add(sRelative, sRelationship, sKey, sDisplay). I don't know if it is actually any faster to do it this way, but it seems like it to me. At least there aren't any nested loops this way.

 

[Jurjen]

breadth first or depth first?

Doing it your way, level by level, means traversing the tree breadth first. That should be done only if it is really necessary, because it involves more coding. Doing it my way means traversing the tree depth first, and can be established with very little coding.

Suppose the treestructure is as follows:

JOB1 (PART1 (PART11, PART12), PART2 (PART21, PART22))
JOB2 (PART3 (PART31, PART32), PART4 (PART41, PART42))

The order of traversal is:
breadth first : J1,J2,P1,P2,P3,P4,P11,P12,P21,P22,P31,P32,P41,P42
depth first : J1,P1,P11,P12,P2,P21,P22,J2,P3,P31,P32,P4,P41,P42

Breadth first involves more coding since after creating each node in a level, based on a record, you have to "visit" that node again in the next level, to provide the parent link.
Depth first is more elegant (in my humble opinion) since after creating a node you can directly pass it to its child level.
And it will work for any number of subpart levels without changing the code, while in your way you have to create a separate recordset for each level.
To me, that seems to be less generic.