Nested set model

When the hierarchy is of fixed or bounded depth, the operations are possible, but expensive, due to the necessity of performing one relational join per level.

Refinements that use rational numbers instead of integers can avoid renumbering, and so are faster to update, although much more complicated.

[6] MySQL used to lack recursive query constructs but added such features in version 8.

If the tree isn't expected to change often, a properly normalized hierarchy of attribute tables can be created in the initial design of a system, leading to simpler, more portable SQL statements; specifically ones that don't require an arbitrary number of runtime, programmatically created or deleted tables for changes to the tree.

For more complex systems, hierarchy can be developed through relational models rather than an implicit numeric tree structure.

An additional tagging or taxonomy has to be established to accommodate this, again leading to a design more complex than a straightforward fixed model.

The nested interval model stores the position of the nodes as rational numbers expressed as quotients (n/d).

[1] Using the nested set model as described above has some performance limitations during certain tree traversal operations.

In this model, finding the immediate children given a parent node can be accomplished with the following SQL code: