than the basis for an entire DB architecture. As stated in SQL Antipatterns: Nested Sets is a clever solution – maybe too clever. It also fails to support referential integrity. It’s best used when you need to query a tree more frequently than you need to modify the tree. The model doesn't allow for multiple parent categories. For example, an 'Oak' could be a child of 'Tree-Type', but also 'Wood-Type'. An additional tagging or taxonomy has to be established to accommodate this, again leading to a design more complex than a straightforward fixed model. Nested sets are very slow for inserts because it requires updating left and right domain values for all records in the table after the insert. This can cause a lot of database stress as many rows are rewritten and indexes rebuilt. However, if it is possible to store a forest of small trees in table instead of a single big tree, the overhead may be significantly reduced, since only one small tree must be updated. The nested interval model does not suffer from this problem, but is more complex to implement, and is not as well known. It still suffers from the relational foreign-key table problem. The nested interval model stores the position of the nodes as rational numbers expressed as quotients (n/d). Variations Using the nested set model as described above has some performance limitations during certain tree traversal operations. For example, trying to find the immediate child nodes given a parent node requires pruning the subtree to a specific level as in the following SQL code example: SELECT Child.Node, Child.Left, Child.Right FROM Tree as Parent, Tree as Child WHERE Child.Left BETWEEN Parent.Left AND Parent.Right AND NOT EXISTS ( -- No Middle Node SELECT * FROM Tree as Mid WHERE Mid.Left BETWEEN Parent.Left AND Parent.Right AND Child.Left BETWEEN Mid.Left AND Mid.Right AND Mid.Node NOT IN (Parent.Node, Child.Node) ) AND Parent.Left = 1 -- Given Parent Node Left Index Or, equivalently: SELECT DISTINCT Child.Node, Child.Left, Child.Right FROM Tree as Child, Tree as Parent WHERE Parent.Left < Child.Left AND Parent.Right > Child.Right -- associate Child Nodes with ancestors GROUP BY Child.Node, Child.Left, Child.Right HAVING max(Parent.Left) = 1 -- Subset for those with the given Parent Node as the nearest ancestor The query will be more complicated when searching for children more than one level deep. To overcome this limitation and simplify tree traversal an additional column is