Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 4 of 4 total
Thread DB Trees in EDB
Mon, Sep 10 2018 10:38 PMPermanent Link

Richard Harding

Wise Nutrition Coaching

Attached is a EDBbackup file that represents a DB Tree by the following tables. The Nodes are the Topic categories and the Edges define the relationship between the Categories.

Nodes (ID, NodeName)
Edges (ParentID, NodeID)
Topic (ID, NodeID, Topic, . . . .)

There are two work tables along with the procedures to populate these tables when the Tree View stucture has been changed.
Paths (StartNode, EndNode, PathLength)
TreeView (ID, NodeID, IndentedNode, NodeName)

Plus an Options table.
Options (TreeViewVerticalChar, TreeViewHorizontalChar)

The Paths table stores all the paths in the tree and allows you to get from a node to another node. You can get the sub-categories of a selected category.
The TreeView table is a sorted, character-based, indented Tree View.
For example,

Top
+--Agriculture
+--Databases
+----Trees
+--Literature
+--Medicine
+----Cardiology
+----Diabetes
+----Neurology
+--Science
+----Geology
+----Mathematics
+------Topology
+--------Mapping
+--------Regular Solids
+----------Euler Formula

The EDB Backup file contains a number of views and functions that provide information about the tree.



Attachments: KBaseII-Backup_2018-09-11-1237.EDBBkp
Tue, Sep 11 2018 4:14 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Richard


I'm not a guru in this area (all I've done is for a simple email folder list) but it seems complex. What are the benefits of this approach over a simple self referencing table?

Roy Lambert
Tue, Sep 11 2018 6:37 PMPermanent Link

Richard Harding

Wise Nutrition Coaching

Roy

* I think it is a good idea to separate the data from how the data is organised. I have an engineering company that has several stock items assembled from other stock items. Keeping the stock items separate from how they are assembled is a good idea. Similarly, keeping the information regarding employees separate from how the employees are supervised is a good idea. The engineering company does record the weight their stock items - so you could calculate the weight of the assembled item.

* There are a number of constraints on the "Edges" table to ensure that tree structure is maintained.

* It is easy to go up the tree from a node in the normal adjacency list but impossible to go down the tree. The usual method to fix this is to create a "Path" column that has a list of nodes such as "RootNode;n2;n3;n4". This does not provide much useful information.

* By adding a "Path" working table, which is easily created from the "Edges" table, makes navigation of the tree much easier.

* You can get information about subtrees. For example, you can answer questions such as which employees are supervised by manager Malcolm, either as an immediate supervisor or indirectly via other managers or which topics belong to the category "Medical" including all the medical subcategories.

* There are a number of views and functions in the attached database that give you access to much more information about the tree.

* It does allow you to create an indented Tree View - once you have done a tree traversal in SQL/PSM.

Richard
Fri, Sep 14 2018 11:13 AMPermanent Link

Adam Brett

Orixa Systems

Richard

Thanks for this. I don't think I need anything this complex right now Smilebut I could in the future, so it is a brilliant resource!
Image