Maybe not that obscure, but Joe Celko’s Nested Set Model gave me exactly what I needed when I learned of it: fast queries on seldom-changing hierarchical database records.
Updates are heavy, but the reads are incredibly light.
I came here to mention these too. One addition that can be helpful in large trees is to add a depth attribute to each node so that you can easily limit the depth of subtree you retrieve.
Maybe not that obscure, but Joe Celko’s Nested Set Model gave me exactly what I needed when I learned of it: fast queries on seldom-changing hierarchical database records.
Updates are heavy, but the reads are incredibly light.
https://en.wikipedia.org/wiki/Nested_set_model
I came here to mention these too. One addition that can be helpful in large trees is to add a depth attribute to each node so that you can easily limit the depth of subtree you retrieve.