Estructura de arbol en MySQL

(Photo credit: Wikipedia)

En la medida que tu avances en el desarrollo web, en algún momento te encontrarás con la necesidad de almacenar información jerárquica en un tabla MySQL. Un caso típico en la generación de un menú jerárquico en un administrador de contenidos, estamos hablando de la generación automátizada del menú.
Es decir, si agrega algún item de contenido, se desea que automáticamente se agregue el enlace a ese item en el menú, de modo desatendido. Parece a primera vista una situación sencilla de resolver, pero en la práctica presenta desafíos.

CREATE  TABLE `articles` (
  `article_id` INT NOT NULL AUTO_INCREMENT ,
  `title` VARCHAR(45) NOT NULL ,
  `content` TEXT NULL ,
  `article_parent_id` INT NOT NULL ,
  PRIMARY KEY (`article_id`)
 )
ENGINE = InnoDB

El problema de esta operación es que, a diferencia de la facilidad de construir una tabla en MySQL para almacenar la jerarquía, la forma de renderizar el menú desde esta tabla no presenta misma facilidad.
Claro, a primera vista el problema podría ser abordado usando las técnicas de programación para manejar una estructura de datos de tipo árbol. Programando, por ejemplo, en PHP una función recursiva para construir los distintos elementos en la lista, con su correspondiente profundidad, desarrollando un despliegue del tipo:

           -- HOMEPAGE--
           |
           -- PRODUCTOS--
           |       |
           |       |
           |       -- CATEGORIA 1
           |       |     |
           |       |     --PRODUCTO 5
           |       |     |
           |       |     --PRODUCTO 3
           |       |   
           |       -- CATEGORIA 2
           |       |     |
           |       |     --PROUCTO 2
           .       .
           .       .
           |       |
           |       -- CATEGORIA N
           |
           -- ACERCA DE--
           |
           -- CONTACTENOS--

Sin embargo, hay aspectos de rendimiento que una programación de una función recursiva en PHP interactuando en cada llamada a la DB, lo hace inviable. Por lo tanto, el enfoque es dejar principalmente la responsabilidad de realizar el proceso en la Base de Datos, directamente programar la tarea en el motor de la DB.

Las soluciones a este problema se formulan en dos modelos que permiten manejar la data jerárquica en una tabla, aplicables en todos los sistemas de bases de datos relacionales de corriente principal, los modelos son:

• Modelo de Lista Adjacente: que corresponde al primer código SQL presentado en este artículo.
• Modelo de Conjuntos Anidados: que presenta una forma más elaborada de almacenar la información, pero tiene la ventaja de que su recuperación es muy eficiente.

 Los detalles de los distintos enfoques se presentan en:

• Trees and Hierachies in SQL: Adjacency List Model
• Storing Hierarchical Data in a Database Article
• PostgreSQL: Documentation: 9.1: Ltree
• My take on trees in SQL
• Managing Hierarchical Data in MySQL
• Hierarchical data in MySQL: parents and children in one query
• The modified preorder tree traversal algorithm
• http://en.wikipedia.org/wiki/Hierarchical_database_model