One way of modelling trees in SQL is hierarchical intervals. It allows fast querying entire sub-branches, along branches, and so on. Updating hierarchical inter

One way of modelling trees in SQL is hierarchical intervals. It allows fast querying entire sub-branches, along branches, and so on. Updating hierarchical intervals is a real pain, so think of them as an index that is built like a materialised view. Quickest explanation: each node has two numbers: left and right (or low/high). All children under the node have numbers between left and right. So, querying an entire sub-branch: select child.* from Nodes as parent, Nodes as child where child.lft between parent.lft and parent.rgt;