Respuesta.

Trabajar con árboles y jerarquías es un caso común al programar en SQL: catálogos de productos, estructuras organizativas, menús. Inicialmente, la mayoría de los SGBD solo soportaban una referencia al padre (ParentId), pero con el tiempo surgieron métodos alternativos: conjuntos anidados y rutas (Path), así como consultas recursivas a través de CTE.

Problema del enfoque tradicional Parent-Child — baja velocidad al recorrer grandes jerarquías; selecciones de nivel más profundo que 2-3 conducen a un número avalancha de JOIN. Técnicas más complejas (Nested Sets, Path Enumeration) aceleran los recorridos masivos, pero dificultan inserciones/eliminaciones.

Solución — elegir el modelo de almacenamiento óptimo para la tarea específica:

Para cambios raros y lectura masiva: Nested Sets (almacenamos los límites left/right en los nodos para búsqueda rápida de descendientes)
Para cambios frecuentes: Adjacency List + CTE recursivos
Para búsqueda rápida de rutas — Path y almacenamiento de la ruta completa en una cadena

Ejemplo de búsqueda recursiva del subárbol a través de CTE:

WITH RecursiveTree AS (
  SELECT id, parent_id, name, 0 as level
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name, rt.level + 1
  FROM categories c
  INNER JOIN RecursiveTree rt ON c.parent_id = rt.id
)
SELECT * FROM RecursiveTree;

Características clave:

Selección de la estructura de almacenamiento para el escenario (Parent-Child, Nested Sets, Path)
Uso de CTE recursivos para profundidades arbitrarias
Evaluación de la frecuencia de cambios y lecturas para seleccionar el método óptimo

Preguntas capciosas.

¿Se puede reemplazar el almacenamiento de árboles por una tabla desnormalizada con un solo nivel de referencias, si la profundidad es fija?

Solo si la profundidad es baja y siempre fija (2-3 niveles) — de lo contrario, los JOIN se vuelven incontrolables, complicando el manejo de cambios.

¿No se "colgará" CTEs con árboles grandes — ¿hay limitaciones de pila, profundidad de recursión?

Sí, la mayoría de los SGBD tienen límites en la profundidad de recursión (por ejemplo, 100/32767). Para árboles de 1000+ niveles se requerirá gestión manual de la profundidad o algoritmos personalizados de recorrido/división.

¿Nested Sets son la solución a todos los problemas?

No, los nested sets buscan instantáneamente todos los descendientes, pero la inserción/eliminación requiere actualización masiva de todos los índices izquierdo/derecho — esto es lento con un alto número de cambios frecuentes.

Ejemplo de inserción (simplificado):

UPDATE tree SET lft = lft + 2 WHERE lft > @parent_right;
UPDATE tree SET rgt = rgt + 2 WHERE rgt >= @parent_right;
INSERT INTO tree(id, name, lft, rgt) VALUES(@new_id, @name, @parent_right, @parent_right + 1);

Errores comunes y anti-patrones

Esperar que Parent-Child se escale fácilmente — en árboles profundos, rápido aumento de costos
Conjuntos anidados para datos actualizados (inserciones/eliminaciones) — costoso
No tener en cuenta los límites de CTE/Stack Overflow en grandes árboles

Ejemplo de la vida real

Caso negativo

Una gran tienda en línea almacenaba el árbol de categorías en una lista de adyacencia y construía el menú con subconsultas anidadas. Con 6+ niveles de menú, la consulta principal tomaba varios minutos, y cualquier cambio en el árbol provocaba una actualización en cascada. Pros:

Código simple
Soporte del esquema SQL estándar

Contras:

Recorrido lento, difícil construir agregados e informes

Caso positivo

Pasaron a Nested Sets para árboles estáticos (categorías), y para rutas en el menú — a Path. Usaron CTE para escenarios dinámicos. La búsqueda de descendientes se volvió instantánea, los cambios se realizaban por lotes. Pros:

Búsqueda rápida de cualquier nivel de anidación
Flexibilidad, diferentes modelos para diferentes tareas