Hierarchies

Unbalanced n-level hierarchies are often used to represent among other things, geographical or organizational dimensions in data. These types of hierarchies are usually stored in an adjacent nodes table, that is, in a table where each record corresponds to a node and has a field that contains a reference to the parent node.

In such a table, the node is stored on one record only but can still have any number of children. The table may of course contain additional fields describing attributes for the nodes.

An adjacent nodes table is optimal for maintenance, but difficult to use in everyday work. Instead, in queries and analysis, other representations are used. The expanded nodes table is one common representation, where each level in the hierarchy is stored in a separate field. The levels in an expanded nodes table can easily be used e.g. in a tree structure.The hierarchy keyword can be used in the data load script to transform an adjacent nodes table to an expanded nodes table.

Example:  

A problem with the expanded nodes table is that it is not easy to use the level fields for searches or selections, since a prior knowledge is needed about which level to search or select in. The ancestors table is a different representation that solves this problem. This representation is also called a bridge table.

The ancestors table contains one record for every child-ancestor relation found in the data. It contains keys and names for the children as well as for the ancestors, that is, every record describes which node a specific node belongs to. The hierarchybelongsto keyword can be used in the data load script to transform an adjacent nodes table to an ancestors table.