This article is a follow-up to A Flexible Tree Structure in which I explained how to create a hierarchical tree structure from only 3 database tables. This design can hold any number of structure (tree) types, with any number of levels and any number of nodes, and also has the ability to enable levels to be added or deleted from a structure without requiring that the whole structure be rebuilt.
This tree structure was implemented in a payroll system in order to record work done by employees on particular tasks for particular projects. The tree structure contained nodes which identified the various projects and tasks, while the leaf records were entries in a person's payroll data which identified dates and amounts. It was a requirement that all project entries be extracted from the payroll system and imported into an external accounting system so that a record of all costs against each project could be maintained.
However, a problem lay in the fact each project and its component parts were represented as nodes within the tree structure, and the identity of each node with the payroll system was completely different from its corresponding node within the accounting system. The resolution of this particular problem is the subject of this particular article.
Nodes within the tree structure are arranged in a pyramid-like hierarchy as shown in figure 1:
Figure 1 - A typical hierarchical structure
The identity of each node is a unique number which is automatically generated by the system. Each node in the structure can have only one parent but any number of children, therefore this relationship can be recorded by including within the details of each node the identity of its parent node within the structure. This also makes it very simple to trace the ancestry or parentage of each node, which is the identity of each parent node all the way to the root node at the top of the tree. The ancestry of the nodes in figure 1 is shown in table 1:
Table 1 - The Ancestry of each Node
| Node | Ancestry |
|---|---|
| 1 | 1 |
| 2 | 1,2 |
| 3 | 1,3 |
| 4 | 1,4 |
| 5 | 1,2,5 |
| 6 | 1,2,6 |
| 7 | 1,3,7 |
| 8 | 1,3,8 |
| 9 | 1,4,9 |
| 10 | 1,4,10 |
Financial details within accounting systems are held in ledgers which are subdivided into a number of accounts. In a Nominal Ledger each of these different accounts has a unique identity known as a Nominal Code. These Nominal Codes usually have some sort of hierarchical structure which, although similar to a node's ancestry, is allocated manually rather than being generated by the system, therefore each component part may be constructed according to a different set of rules. Each component part may have a different number of digits and letters, and there may or may not be a separator between each part such as a period (.) or a hyphen (-).
For example, the node sequence '1,3,7' may have an equivalent nominal code of 'AAK07-104-M096'.
In designing a method to translate a node sequence to a nominal code I had one golden rule - nothing must be hard-wired, everything must be defined on the database so that it can be changed at will.
With this in mind I looked at the existing database structure for my TREE NODE table and simply added another field called EXTERNAL_CODE. Now when I wish to construct a code string for use in an external system all I have to do is replace each NODE_ID in the ancestry sequence with its corresponding EXTERNAL_CODE. This can be shown in figure 2:
Figure 2 - Showing a Node's Ancestry
This screen shot shows the ancestry of node 81 at level 3 as '62,66,81'. By joining together the contents of the EXTERNAL_CODE fields you will see that we end up with the string 'AAK07-104-M096'. You will notice in this simple solution that the hyphen (-) separator is included in the contents of EXTERNAL_CODE rather than being added in separately. Also, if any node does not have a value defined for EXTERNAL_CODE then it contributes nothing to the final output string.
Tony Marston
6th November, 2001
mailto:tony@tonymarston.net
mailto:TonyMarston@hotmail.com
http://www.tonymarston.net