Skip to content

Clase 28 – Modelado de Datos jerárquicos con Recursión

El modelado de datos jerárquicos es una habilidad esencial en SQL cuando se trabaja con estructuras de árbol, como organizaciones, categorías anidadas o relaciones padres-hijos.

En esta clase avanzada, exploraremos cómo utilizar funciones recursivas en SQL para manipular y consultar eficientemente datos jerárquicos.

Entendiendo la Recursión en SQL

La recursión implica que una función se llame a sí misma, permitiendo procesar estructuras de datos jerárquicas de manera más efectiva.

En SQL, esto se logra mediante el uso de Common Table Expressions (CTEs) y la cláusula WITH RECURSIVE.

Creando una CTE Recursiva

Supongamos que tenemos una tabla llamada organizacion que representa una estructura jerárquica de una empresa:

CREATE TABLE organizacion (
    id INT PRIMARY KEY,
    nombre VARCHAR(50),
    id_padre INT,
    FOREIGN KEY (id_padre) REFERENCES organizacion(id)
);

INSERT INTO organizacion (id, nombre, id_padre) VALUES
(1, 'Empresa', NULL),
(2, 'Departamento A', 1),
(3, 'Departamento B', 1),
(4, 'Equipo A1', 2),
(5, 'Equipo A2', 2),
(6, 'Equipo B1', 3);

Para consultar la jerarquía, podemos utilizar una CTE recursiva:

WITH RECURSIVE JerarquiaCTE AS (
    SELECT id, nombre, id_padre
    FROM organizacion
    WHERE id_padre IS NULL  -- Seleccionamos los nodos raíz
    UNION ALL
    SELECT o.id, o.nombre, o.id_padre
    FROM organizacion o
    JOIN JerarquiaCTE cte ON o.id_padre = cte.id
)
SELECT * FROM JerarquiaCTE;

Esta consulta recupera todos los niveles de la jerarquía de la tabla organizacion.

Manipulación de Datos Jerárquicos

La manipulación de datos jerárquicos implica agregar, actualizar o eliminar nodos en la estructura de manera coherente.

Aquí hay un ejemplo de cómo agregar un nuevo departamento bajo Empresa:

INSERT INTO organizacion (nombre, id_padre)
VALUES ('Nuevo Departamento', (SELECT id FROM organizacion WHERE nombre = 'Empresa'));

Para eliminar un equipo y sus subequipos, podemos utilizar una CTE recursiva junto con la cláusula DELETE:

WITH RECURSIVE EquiposAEliminar AS (
    SELECT id
    FROM organizacion
    WHERE nombre = 'Equipo A1' OR nombre = 'Equipo A2'  -- Equipos a eliminar
    UNION ALL
    SELECT o.id
    FROM organizacion o
    JOIN EquiposAEliminar cte ON o.id_padre = cte.id
)
DELETE FROM organizacion WHERE id IN (SELECT id FROM EquiposAEliminar);

Esta operación elimina de manera segura el equipo Equipo A1, Equipo A2 y cualquier subequipo asociado.

Mejores Prácticas en Modelado de Datos Jerárquicos

  1. Manejo de Ciclos:
    • Ten cuidado con las estructuras jerárquicas que permiten ciclos, ya que pueden causar bucles infinitos en consultas recursivas.
  2. Índices Eficientes:
    • Asegúrate de tener índices eficientes en las columnas utilizadas en las operaciones de búsqueda en la jerarquía.
  3. Actualización Atómica:
    • Para actualizaciones complejas, considera realizarlas en una transacción para garantizar la atomicidad.
  4. Documentación Clara:
    • Documenta la estructura jerárquica y las operaciones permitidas para facilitar la comprensión y el mantenimiento.

Ejemplo Práctico: Consulta Jerárquica con Niveles

Supongamos que queremos obtener una vista jerárquica con niveles de la estructura de la organización:

WITH RECURSIVE JerarquiaConNiveles AS (
    SELECT id, nombre, id_padre, 1 as nivel
    FROM organizacion
    WHERE id_padre IS NULL
    UNION ALL
    SELECT o.id, o.nombre, o.id_padre, cte.nivel + 1
    FROM organizacion o
    JOIN JerarquiaConNiveles cte ON o.id_padre = cte.id
)
SELECT * FROM JerarquiaConNiveles;

Esta consulta devuelve la jerarquía con información adicional sobre los niveles de cada nodo.

Manipular datos jerárquicos en SQL utilizando funciones recursivas es esencial para abordar estructuras complejas.

La combinación de CTEs recursivas, índices eficientes y buenas prácticas de manipulación de datos permite construir y mantener jerarquías de manera efectiva.