Systems often store data in tables that store hierarchical information in a self-referencing (parent-child) relationship. With this design, a field in the table is used to reference the primary key (identifying record) within the same table. A common example of this type of relationship can be found in organizational structures where an employee has a manager field that specifies another employee's record. Another example includes a bill of materials, which defines a product made from subproducts, which may in turn be made up of other products, and so on.
This type of structure is very efficient for storage because the same table can be used for all items within the hierarchy; this allows for easy updates, insertions, and deletions. However, one issue with this type of structure is that the tree of items within the structure is not immediately visible to an end user. A common method to expose the structure is to flatten the relationship into a table, so that each...