suggestion on table structure for tree data

[shack]

i need to store node data from a tree into a database table while still preserving the tree structure. the tree can then be re-built at runtime. the tree is a 1 parent, n-children structure.

i'm not sure which properties of each node are important for storing, i.e. who is my parent, who is my child, # of children, etc.

database structure is not my strength and i thank you in advance for any suggestions.

[JHausmann]

All you really need is a sort key, a node level and the text that goes along with it. In my case, I've used:

NodeID Long
Node long
NodeName Char(50)

I use multiples of 10 to space my main nodes, and use single increments for the bottommost children. For example:

SortKey   Node    Name
0          1      treetop
10         2      Branch1
11         3      leaf1
12         3      leaf2
20         2      Branch2
21         3      leaf1
22         3      leaf2

[This message has been edited by JHausmann (edited 10-22-1999).]

[shack]

thx for your reply..

i'm not sure i've grasped your suggestion quite yet. using your example, if a node is added at level 3, how is its parent kept track of? or 2 nodes at level 3 with 2 different parents?

if you don't mind, could you post a bit of your code which re-creates the tree based on the table data?

thx again.
shack

[JHausmann]

Basically what I do is have one routine that loads the tree control. I manipulate the database table to add/delete items, when done, I re-load the tree control.

[shack]

ok..perhaps you can tell me (in words if you like) how each db record is examined to determine it's proper location in the tree. looking at your few pasted records doesn't tell the whole story.

[JHausmann]

Dim nodTreeNode     As Node
Dim strNodeKey      As String
Dim strBaseNode     As String
Dim strDefaultText  As String
Dim rsTmp           As Recordset
Dim rsLvl(1 To 5)   As String

frmMain.tvwNodetree.Nodes.Clear
Set rsTmp = gdbStatic.OpenRecordset("Select * from outline_nodes order by nodeid", dbOpenSnapshot)
strDefaultText = "None"

If rsTmp.EOF = False Then 'we collect the entries from the table
    Do While Not rsTmp.EOF
        strNodeKey = rsTmp!nodename
        If (rsTmp!nodelevel = 1) Then
            Set nodTreeNode = _
                frmMain.tvwNodetree.Nodes.Add(, _
                                              , _
                                                strNodeKey, _
                                                rsTmp!nodename _
                                                )
            rsLvl(rsTmp!nodelevel) = strNodeKey
            nodTreeNode.EnsureVisible
        End If
        If (rsTmp!nodelevel > 1) Then
            Set nodTreeNode = _
                frmMain.tvwNodetree.Nodes.Add(rsLvl(rsTmp!nodelevel - 1), _
                                                tvwChild, _
                                                rsTmp!nodename, _
                                                rsTmp!nodename _
                                                )
            rsLvl(rsTmp!nodelevel) = strNodeKey
            If rsTmp!nodelevel < 5 Then nodTreeNode.EnsureVisible
        End If
        rsTmp.MoveNext
    Loop
    rsTmp.Close

End If

[JHausmann]

That's the approach I've taken. It allows for fairly simplistic coding of tree manipulation.

[shack]

thx for the code... i understand now what you were saying. it seems that the record order is crucial. any node's children must immediately follow it's entry in the table..and their children them. this is why you said you rebuild the table following any add/delete of nodes.