Models for #Hierarchical Data in #SQL and PHP – Bill Karwin – #MySQL Tutorial


Tree-like data relationships are common, but working with trees in SQL usually requires awkward recursive queries. This talk describes alternative solutions for modeling #hierarchical data that work for #MySQL, including:

* Adjacency List
* Path Enumeration
* Nested Sets
* Closure Table

Code examples will demonstrate using the Closure Table in PHP, and offer guidelines for choosing one design over another.

PRESENTER: Bill Karwin, Principal #MySQL Instructor on February 27, 2011.

📺Subscribe To Our Channel and Get More information about #OpenSource #Databases, #kubernetes and many other things.

Comment 💬, Share 🔗, Like 👍🏻, and Subscribe ✅ to our channel + Turn on the 🔔

Connect With us on our Social Networks 👇🏻👇🏻👇🏻
Follow Percona on Twitter:
Follow Percona on Facebook:
Follow Percona on Instagram:
Follow Percona on LinkedIn:


Xem thêm bài viết khác:


  1. share apps3 10 July, 2020 at 22:36 Reply

    is Zend framework capable of doing all those kind of queries automatically, please i need some clarity, i didnt learned zend yet but is zend that powerful in Db management ??? thanks in advance

  2. Nallieheai 10 July, 2020 at 22:36 Reply

    Im currently working of a project, the main thing is to create a treeview of the data i have in my database, however i have two three tables so far, one called Project(its a project folders destination on the pc pretty much and shown with a nicer name in the web), Subfolder(child of a project, they have things like "project_id" to know which project they belong to) then i have my 3rd table which is what i called Subfolder_link, which hold its own subfolder_link_id, subfolder_parent and subfolder_child, so a subfolder could be a parent or a child, but you dont know that before you check the link. Could i use the closure table design for this or does it only work if i have one table? Because as far as i know i cant combine Project and Subfolder into one table without having to NULL a few columns? Because stuff like a nice name is things i only care about the Project having, like a display name, but i suppose i could do that for the Subfolder as well if its the only way to solve it. Would appreciate any response that could help, at the moment im just watching through these sort of videos to learn about creating the treeview since im a bit unsure if i should do it in HTML and JavaScript or in PHP? I havent worked as much with JavaScript as PHP so i wouldnt really know where to start, but thats a different problem that ill try to figure out later.

  3. Yinghan Wang 10 July, 2020 at 22:36 Reply

    correct me if i am wrong, when inserting a new node to the treepaths db, union all won't remove all the duplicates.

  4. Rock Bros 10 July, 2020 at 22:36 Reply

    This is a great video. Would the closure table work for a geneology heirarchy where every node has two parents? Would you have to use two closure tables, one for each parent type (Male/Female)?

Leave a reply