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 👇🏻👇🏻👇🏻

[MonsoonBloomLive]

a brilliant and elegant solution; this solves so many problems. I bought your book.

[kunafeh]

Provided it’s such a knowledgeable video, I think author should re-record this with better audio quality.

[bahamu]

Could you explain how the length column is populated on insertion? Thanks for the video!

[Nallieheai]

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.

[caydenIT]

how to produce the length in insertion?

[shareapps]

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

[corythenati]

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)?

[XinWongDigital]

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