Models for #Hierarchical Data in #SQL and PHP - Bill Karwin - #MySQL Tutorial
Why Stack Overflow recommends it
Stack Overflow users link it when someone needs to store or query hierarchical data in MySQL, which lacks recursive queries, and the closure table and nested set models are the usual fix.
Recommended by 4 people across 3 different months, 2016 to 2021.
What it is
A 2011 Percona webinar in which MySQL instructor Bill Karwin compares four ways to store trees in a relational database: adjacency list, path enumeration, nested sets and closure table. It includes PHP examples using the closure table and guidance on choosing between the designs.
What people said
Strong rec eggyal · 2016-08 · Stack Overflow · on “How to display all results in php mysql in recursive function”
Unlike some other RDBMS, MySQL doesn't support recursive functions—which makes it poorly suited to this "adjacency list" model of storing hierarchical data (you must explicitly self-join for each level of depth you desire); consider instead redesigning your schema to use either the "nested sets" or "transitive closure" models, about which you can learn more from @Bill Karwin [LINK] 's excellent presentation Models for Hierarchical Data in SQL and PHP [this video] .
Strong rec eggyal · 2016-08 · Stack Overflow · on “How to select table base on it column”
The model you have adopted for storing your hierarchical data is known as an " adjacency list ". Sadly, MySQL does not support recursive functions, so using such a model one would have to make a self-join for each level of the hierarchy that one wishes to traverse—but it ought to be readily apparent that that isn't a very scalable solution. PostgreSQL, on the other hand, does support recursive CTEs [LINK] with which you can do: WITH RECURSIVE tree AS ( SELECT id, name, ARRAY[]::INTEGER[] AS ancestors FROM employees WHERE upline_id = 0 UNION ALL SELECT employees.id, employees.name, tree.ancestors || employees.upline_id FROM employees JOIN tree ON employees.upline_id = tree.id ) SELECT * FROM
Recommends Kris T. · 2016-07 · Stack Overflow · on “Access control on different tables with parent/child relationships”
Found an answer: Models for Hierarchical Data in SQL and PHP [this video] . That video talks about "closure tables" which is exactly what I was looking for. Using the "depth" and "ancestor" fields in the closure table, I can pinpoint from any level of the hierarchy (People, Houses, Streets) to which City the data belongs. I created a "securables" table as well, that has one row for every row from the people, houses, and streets tables. Then my closure table (and some configuration to tell each child table what its depth should be) relates them together into a tree. Then, using that configured depth I can grab the root of the City's tree at any time from any node within- in a single query! Presto!
Recommends Bill Karwin · 2016-08 · Stack Overflow · on “How find oldest "ancestor" in MySQL where each row has a parent or null?”
Some comments recommend the "nested set" model, which was popularized in Joe Celko's SQL books. But I find that data model is hard to maintain, and it's complicated to modify the data. I prefer a design I call "closure table" which I described in this well-received Stack Overflow answer: What is the most efficient/elegant way to parse a flat table into a tree? [LINK] Your query would look like this: SELECT c.ID, c.NAME, a.ID AS OLDEST FROM MyTable AS t JOIN ClosureTable AS c ON t.ID = c.descendant JOIN MyTable AS a ON a.ID = c.ancestor WHERE a.PARENT IS NULL; I also cover this model (and other solutions) in my presentation Models for Hierarchical Data [LINK] . Here's the video recording of m
Recommends Gaz Zhao · 2021-03 · Stack Overflow · on “How to deal with transitive functional dependency in MySQL”
I think I found the direction to solve my problem. I got inspired by this thread to use a path enumeration method. [this video]