Respuesta.

El trabajo con estructuras jerárquicas es un clásico de las bases de datos relacionales. Ejemplos: catálogos, menús jerárquicos, estructuras organizativas.

Historia del tema: Las bases de datos son un modelo tabular. Existen varios enfoques para datos en forma de árbol, cada uno con sus pros y sus contras.

Problema: El modelo estándar parent_id lleva a SELECT recursivos lentos. Las tareas reales (buscar todos los descendientes, rutas, recuento de subárboles) requieren optimización.

Solución:

Lista de Adyacencia — simple referencia a parent_id.
Ruta Materializada — cadena que refleja la ruta completa.
Conjuntos Anidados — almacenamiento de límites izquierdo/derecho (left/right), lo que permite extraer subárboles fácilmente.
Tabla de Closure — tabla separada de relaciones (from->to), que refleja todas las relaciones en el árbol.

Ejemplo de código para Ruta Materializada (PostgreSQL):

CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name TEXT,
    path TEXT
);

-- Ejemplo de almacenamiento de path: '1/2/5/' (raíz/subcategoría/actual)
SELECT * FROM categories WHERE path LIKE '1/2/%'; -- todos los descendientes de 2

Ejemplo de código para Conjuntos Anidados:

CREATE TABLE nested_categories (
    id SERIAL PRIMARY KEY,
    name TEXT,
    lft INT NOT NULL,
    rgt INT NOT NULL
);

-- Nodos hijos
SELECT * FROM nested_categories WHERE lft > 2 AND rgt < 15;

Características clave:

La Lista de Adyacencia es conveniente para estructuras de árbol simples (baja profundidad).
La Ruta Materializada permite una rápida extracción de subárboles y es fácil de implementar.
Los Conjuntos Anidados permiten obtener todos los descendientes instantáneamente, lectura rápida, pero modificación compleja.

Preguntas capciosas.

¿Se puede simplemente usar un SELECT anidado con parent_id para buscar todos los descendientes a cualquier profundidad?

Esto solo funciona para profundidades pequeñas. Para búsqueda recursiva se requiere un CTE recursivo (WITH RECURSIVE), o esquemas más complejos, ya que los JOIN simples conducen a un número enorme de consultas y bajo rendimiento.

Ejemplo:

WITH RECURSIVE cte AS (
    SELECT id, parent_id, name FROM categories WHERE id = 1
    UNION ALL
    SELECT c.id, c.parent_id, c.name
    FROM categories c
    INNER JOIN cte ON c.parent_id = cte.id
)
SELECT * FROM cte;

¿Por qué el árbol molecular (Conjuntos Anidados) extrae rápidamente un subárbol, pero agrega/elimina nodos lentamente?

Al cambiar el árbol, es necesario modificar lft/rgt en muchas filas (el paso de modificación es todos los valores mayores que la inserción/eliminación). Para lectura, el enfoque es ideal, para cambios frecuentes, use otros métodos.

¿Cuándo usar la Tabla de Closure, en lugar de parent_id o ruta materializada?

La Tabla de Closure funciona perfectamente para consultas múltiples a descendientes de cualquier nivel y recuentos regulares de relaciones. La desventaja es que requiere más espacio.

Ejemplo:

CREATE TABLE closure (
    ancestor INT,
    descendant INT,
    depth INT
);

Errores comunes y anti-patrones

Almacenar la jerarquía solo a través de parent_id cuando se requiere una búsqueda rápida de estructuras anidadas.
Recontar manualmente las rutas o lft/rgt sin funciones auxiliares.
Falta de indexación en columnas clave (parent_id/path/lft/rgt).

Ejemplo de la vida real

Caso negativo

En una tienda en línea, las categorías se implementan a través de parent_id. Todos los anidados se establecen manualmente, buscando subcategorías mediante SELECT anidados.

Pros:

Simplicidad, no requiere soporte avanzado.

Contras:

El rendimiento disminuye incluso en 3-4 niveles de anidamiento.
Las operaciones de movimiento de nodos son poco evidentes, lo que lleva a errores lógicos.

Caso positivo

Se utiliza ruta materializada o tabla de closure. Todas las consultas sobre categorías anidadas son instantáneas, el recuento se realiza mediante scripts grupales.