{"id":29031,"date":"2024-04-28T13:01:09","date_gmt":"2024-04-28T12:01:09","guid":{"rendered":"https:\/\/nelkodev.com\/blog\/domina-los-datos-jerarquicos-en-mysql-con-el-modelo-de-lista-de-adyacencia\/"},"modified":"2024-06-03T17:43:59","modified_gmt":"2024-06-03T16:43:59","slug":"domina-los-datos-jerarquicos-en-mysql-con-el-modelo-de-lista-de-adyacencia","status":"publish","type":"post","link":"https:\/\/nelkodev.com\/en\/blog\/master-hierarchical-data-in-mysql-with-the-adjacency-list-model\/","title":{"rendered":"Master Hierarchical Data in MySQL with the Adjacency List Model"},"content":{"rendered":"<p>In the world of databases, managing hierarchical data structures is a common need, especially in applications that involve categories, organizational structures, or any type of data that is presented in the form of a tree. MySQL, being one of the most popular database management systems, offers various techniques to handle this type of data. One of the most efficient and used methods is the adjacency list model. In this article, I&#039;ll take you through the fundamental concepts of this model and how to properly implement it in MySQL to get the most out of your hierarchical databases.<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_80 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #ffffff;color:#ffffff\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewbox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #ffffff;color:#ffffff\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewbox=\"0 0 24 24\" version=\"1.2\" baseprofile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-hierarchical-data-in-mysql-with-the-adjacency-list-model\/#%C2%BFQue_es_el_Modelo_de_Lista_de_Adyacencia\" >What is the Adjacency List Model?<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-hierarchical-data-in-mysql-with-the-adjacency-list-model\/#Estructura_Basica_de_una_Tabla_con_Modelo_de_Lista_de_Adyacencia\" >Basic Structure of a Table with Adjacency List Model<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-hierarchical-data-in-mysql-with-the-adjacency-list-model\/#Implementando_el_Modelo_en_MySQL\" >Implementing the Model in MySQL<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-hierarchical-data-in-mysql-with-the-adjacency-list-model\/#Consultando_Datos_con_Modelo_de_Lista_de_Adyacencia\" >Querying Data with Adjacency List Model<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-hierarchical-data-in-mysql-with-the-adjacency-list-model\/#Desafios_al_Usar_Lista_de_Adyacencia\" >Challenges When Using Adjacency List<\/a><ul class='ez-toc-list-level-4' ><li class='ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-hierarchical-data-in-mysql-with-the-adjacency-list-model\/#Trabajando_Alrededor_de_la_Limitacion\" >Working Around the Limitation<\/a><\/li><\/ul><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-hierarchical-data-in-mysql-with-the-adjacency-list-model\/#Conclusiones\" >Conclusions<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"%C2%BFQue_es_el_Modelo_de_Lista_de_Adyacencia\"><\/span>What is the Adjacency List Model?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The adjacency list model is a way of representing hierarchical data in a relational database. In this model, each row in a table is linked to its parent row through a field that references the parent&#039;s ID. This structure is particularly useful because it is simple to understand and implement. However, it can be challenging to perform queries that require traversing multiple levels of the hierarchy.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Estructura_Basica_de_una_Tabla_con_Modelo_de_Lista_de_Adyacencia\"><\/span>Basic Structure of a Table with Adjacency List Model<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>To illustrate, let&#039;s consider a simple table called <code>Categories<\/code> which represents a hierarchical structure of product categories:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">CREATE TABLE Categories ( ID int AUTO_INCREMENT PRIMARY KEY, Name varchar(255), ParentID int, FOREIGN KEY (ParentID) REFERENCES Categories(ID) );<\/code><\/pre>\n<p>In this design, <code>ID<\/code> is the unique identifier of each category, <code>Name<\/code> is the name of the category, and <code>ParentID<\/code> is the identifier of the parent category of the current entry. Yeah <code>ParentID<\/code> is NULL, then the category is top level.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Implementando_el_Modelo_en_MySQL\"><\/span>Implementing the Model in MySQL<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Once the table structure is defined, the next step is to insert data into this table. Here&#039;s how you can insert some categories to form a hierarchy:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">INSERT INTO Categories (Name, ParentID) VALUES (&#039;Electronics&#039;, NULL), (&#039;Televisions&#039;, 1), (&#039;Smartphones&#039;, 1), (&#039;Cameras&#039; , 1), (&#039;Portables&#039; , 1), (&#039;Accessories&#039;, 5);<\/code><\/pre>\n<p>Here, &#039;Electronics&#039; is a top-level category and the rest are subcategories that depend on it in various ways.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Consultando_Datos_con_Modelo_de_Lista_de_Adyacencia\"><\/span>Querying Data with Adjacency List Model<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Consulting categories is simple if you only need direct information from one level. For example, to find all the subcategories of &#039;Electronics&#039;, you could use:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">SELECT b.Name FROM Categories a JOIN Categories b ON a.ID = b.ParentID WHERE a.Name = &#039;Electronics&#039;;<\/code><\/pre>\n<p>This query would give you &#039;Televisions&#039;, &#039;Smartphones&#039;, &#039;Cameras&#039; and &#039;Laptops&#039;, which are directly subordinate to &#039;Electronics&#039;.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Desafios_al_Usar_Lista_de_Adyacencia\"><\/span>Challenges When Using Adjacency List<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Despite its simplicity, the adjacency list model presents challenges, especially when performing queries that involve multiple levels of the hierarchy. This is because MySQL does not have direct support for recursive queries, commonly used in this context in other database management systems such as PostgreSQL.<\/p>\n<h4><span class=\"ez-toc-section\" id=\"Trabajando_Alrededor_de_la_Limitacion\"><\/span>Working Around the Limitation<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>To handle multiple levels, we can make use of stored procedures or simulate recursion using multiple nested queries, although the latter can be inefficient in terms of performance with large volumes of data.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Conclusiones\"><\/span>Conclusions<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The adjacency list model, despite its challenges, offers a robust and easily implemented way to handle hierarchical data in MySQL. With a good design and understanding of its limitations, you can effectively handle these types of data structures. Additionally, it is crucial to keep an eye on developments in MySQL software and related techniques that could further simplify the management of these models in the future.<\/p>\n<p>To learn more about advanced techniques and learn more about developing with MySQL, visit <a href=\"https:\/\/nelkodev.com\/en\/\">nelkodev.com<\/a>. If you have questions or need advice on a project, don&#039;t hesitate to visit <a href=\"https:\/\/nelkodev.com\/en\/contact\/\">nelkodev.com\/contact<\/a>.<\/p>\n<p>Implementing and querying hierarchical data can be challenging but also very rewarding, allowing you to build more dynamic and comprehensive applications. I hope this article has equipped you with the necessary knowledge to start working with hierarchical data in MySQL effectively. Happy coding!<\/p>","protected":false},"excerpt":{"rendered":"<p>In the world of databases, managing hierarchical data structures is a common need, especially in applications that involve categories, organizational structures, or any type of data that is presented in tree form. MySQL, being one of the most popular database management systems, offers various techniques for managing hierarchical data structures.<\/p>","protected":false},"author":1,"featured_media":29032,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[420,2010,2185],"tags":[2193,2109,205,90,2189,353,1473,2081,2110,221,617,352,2192],"class_list":["post-29031","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","category-advanced-techniques","category-curso-mysql","tag-advanced","tag-adyacencia","tag-blog","tag-con","tag-curso","tag-datos","tag-domina","tag-jerarquicos","tag-lista","tag-los","tag-modelo","tag-mysql","tag-techniques"],"_links":{"self":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts\/29031","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/comments?post=29031"}],"version-history":[{"count":0,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts\/29031\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/media\/29032"}],"wp:attachment":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/media?parent=29031"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/categories?post=29031"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/tags?post=29031"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}