Phil Waclawski: Using hierarchical data in mysql trees vs nests


Presented to the Phoenix Linux Users Group (PLUG) on April 13th 2017 by Phil Waclawski.

Real world data is often made of hierarchical relationships, or “trees”.
Representing that data in a Relational Database starts to get complicated as soon as you have more than two levels in your “Tree”.
There are many strategies for handling such data, and I will cover examples of at least two of them (adjacency list and nested sets) and possibly more.

If you have access to a mysql instance, you can create the test tables and follow along, or get the code later.

About Phil:
Phil has been a faculty member, focusing on open source software at MCC for nearly two decades.

Phil teaches a variety of course, including MySQL Database, PHP, Python, Javascript, HTML 5 and CSS, Linux OS, Internet Server Set up and more.

This summer, Phil will once again be offering a “MCC Multimedia Madness” summer camp for 6-8th graders. Over the two weeks I will cover a wide range of open source software, including GIMP, Inkscape, Krita, Audacity, Blender, Hydrogen Drum Kit, Tux Guitar, Openshot, Linux Multimedia Studio, work with green screen and more.

For hobbies, Phil has too many. Blacksmithing, archery, Leatherworking, Wood Working, Jewelry, Sound Recording/editing and SCUBA diving. Phil is also learning to play the Bass.

Phil has been using linux at home for desktops and servers since 1994.

If you like what you see here, the Phoenix Linux Users Group meets several times a month. Please visit for meeting times and locations then come see the presentations live and uncut.


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


  1. shrey som 11 July, 2020 at 14:38 Reply

    For making a folder and file type system where a folder is parent and have many children including folder and file. I am storing all the meta data in the sql Table specifying the parent id and base parent id. Which technique will be best fit for retrieving the child of a particular folder provide that it should be Efficient in term of moving a folder and deleting as well.

Leave a reply