Joe Celko's Trees and Hierarchies in SQL for Smarties,
by Joe Celko · first published 2004
6mentions
5recommended by
6HN commenters
2013–2025first – last mentioned
83% recommend17% neutral
Why HN recommends it
Covers the main ways to store tree and hierarchy data in SQL, such as adjacency, path, and nested set models, with a chapter on each. Good starting point for developers who need to model trees in relational databases.
Summarised by Claude from recommending comments
Affiliate links. As an Amazon Associate I earn from qualifying purchases.
A good book on the subject is Joe Celko's Trees and Hierarchies in SQL for Smarties. http://www.amazon.com/Hierarchies-Smarties-Edition-Kaufmann-... He spends a chapter on each of the models outlined in this post: adjacency, path, and nested set models.
on “Representing Trees in PostgreSQL” ↗For those interested in sharpening their SQL skills I have found these two books to be a great resource. 1. Joe Celko's Trees and Hierarchies in SQL for Smarties 2. Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL
on “Learn SQL, dammit” ↗Joe Celko's Trees and Hierarchies in SQL for Smarties (book) is a good start for better understanding/defining trees. http://a.co/9eVzvji
on “Why Don't We Have a General-Purpose Tree Editor? (2014)” ↗There is an excellent book on this subject, "Joe Celko's Trees and Hierarchies in SQL for Smarties".
on “Models for hierarchical data (2010)” ↗…through put on all interfaces on group of servers [0] --- topic related books: Joe Celko has book series 'SQL for Smarties' that covers basic through advanced aggrecation with sql using example queries. Joe Celko's 'Trees and Hierarchies in SQL for Smarties' covers some of the ideas related to 'SQL statment returned a database'? --- [0]: https://dba.stackexchange.com/questions/173044/nested-aggreg... [1] : http://unicon.org/ [2] : https://cs.uwaterloo.ca/~tozsu/courses/CS338/lectures/6%20Ne...
on “What if an SQL Statement Returned a Database?” ↗…characteristics, especially if your RDBMS doesn't have Recursive CTEs. This starts to matter at a much lower scale than you might think; several million rows is enough to start showing slowdowns. This book [0] (Joe Celko's Trees and Hierarchies in SQL For Smarties) shows many other options, though it does lack the closure table approach [1], which is my preferred approach. And here, we come full circle back to the long-held friction between DBs and applications.…
on “Design Pressure: The Invisible Hand That Shapes Your Code” ↗