Gestión de estructuras jerárquicas en una base de datos relacional

Just another tree - Chris Gin, jul-2008

Se trata de la creación de estructuras de jerarquía —propio de carpetas o del lenguaje XML— utilizando una base de datos relacional.

El núcleo de la aplicación es una única tabla que se relaciona consigo misma. Para que la aplicación sea más flexible y potente he creado una serie de tablas auxiliares que acompañan a la tabla principal. El código fuente se encarga de mantener y gestionar adecuadamente todo este sistema.

En este caso concreto he utilizado la base de datos Microsoft Access y su lenguaje de programación Visual Basic.

Base de datos

Código SQL para crear las tablas con datos de ejemplo: jerarquia.sql

Tabla principal: jerarquías

Son los nodos del árbol 
  • Campos principales:
    • id — identificador único
    • jerarquía — Título del nodo
    • padre_id — Padre de este nodo o nulo sino tiene
    • nivel_id — Nivel de profundidad
  • Campos cuyo valor es generado mediante código:
    • orden — Ordenación al imprimir el árbol completo
    • tabulador — Número de ancestros o número de tabuladores a usar al imprimir.
    • hermano — Número de hermano
    • ruta — Desde el ancestro hasta el descendiente: Padre-Nodo-Hijo
    • ruta_inversa — Desde el descendiente hasta el ancestro: Hijo-Nodo-Padre
    • ruta_unix — Ruta de tipo /Padre/Nodo/Hijo
    • numeracion — De tipo 1.3.2., 1.3.3., etc.
    • rama — Caracteres Unicode para dibujar las ramas: ├ ┬ └

Filas de la tabla jerarquias dónde se almacena el árbol de tipos de personajes.

Tablas secundarias: tipos y niveles

La tabla jerarquías_tipos se utiliza para poder almacenar distintos árboles de jerarquía dentro de la tabla jerarquías.

La tabla jerarquías_niveles se utiliza para realizar una descripción de cada nivel de profundidad del árbol. 

Tabla temporal

La tabla temp_jerarquías se utiliza con el único fin de facilitar la introducción de la jerarquía en un formulario mediante un cuadro combinado o un cuadro de lista.

Tabla de ejemplo

La tabla personajes hace uso de una de las estructuras jerárquicas almacenada en la tabla jerarquías.

Relaciones entre las tablas

Dibujo de las ramas

Hay una serie de caracteres Unicode denominados Box drawings characters que sirven para dibujar líneas horizontales y verticales unidas de todas las formas posibles. Esto era propio del juego de caracteres ASCII que ahora con Unicode se puede volver a recuperar.

Caracter Código Unicode Descripción
U+2500 Box drawings light horizontal
U+2502 Box drawings light vertical
U+2514 Box drawings light up and right
U+251C Box drawings light vertical and right
U+252C Box drawings light down and horizontal

Los tres árboles introducidos en la base de datos:

Personajes:

├─ Animal
├┬ Ficción
│├─ Animado
│├─ Cine
│├─ Cuento
│├┬ Héroe
││└─ Superhéroe
│├─ Literario
│├─ Monstruo
│└─ Robot
└┬ Ser humano
 ├┬ Artista
 │├─ Cine
 │├─ Literato
 │├─ Músico
 │└─ Pintor
 ├─ Científico
 └─ Histórico

Lugares:

├─ Júpiter
├┬ La Tierra
│├┬ América
││└┬ Estados Unidos
││ ├─ Los Angeles
││ └─ Nueva York
│├─ Asia
│└┬ Europa
│ └┬ Alemania
│  └─ Munich
├─ Tatooine
└─ Trántor

Empresas:

├┬ Aplicación
│├┬ Desarrollo
││├─ Comercio
││├─ Filial
││└─ Mercado
│├┬ Integral
││├─ Cuentas
││├─ Programa
││└─ Segmento
│├┬ Medio
││├─ Internet
││├─ Prensa
││└─ Radio
│├─ Oficina
│└┬ Producto
│ ├─ Activo
│ ├─ Caja
│ └─ Riesgo
├─ Implantación
├┬ Infrastructura
│├┬ Arquitectura
││├─ Desarrollo
││├─ Servidor
││└─ Sistema
│├─ Exportación
│└┬ Localización
│ ├─ Plataforma
│ ├─ Producción
│ ├─ Tecnología
│ └─ Telecomunicaciones
└┬ Recursos
 ├─ Finanza
 ├─ Planificación
 ├─ RR.HH.
 ├─ Seguridad
 └─ Servicio

Interfaz

Para la gestión de las jerarquías se puede utilizar el siguiente formulario:

Formulario para la gestión de las jerarquías

Forma de utilización:

  1. En la lista Tipos se puede añadir un nuevo árbol de jerarquía.
  2. En la lista Niveles se han de describir cada una de las profundidades que tendrá el árbol.
  3. En la lista Elementos se añaden los elementos del primer nivel de la jerarquía.
  4. En la lista Hijos se añaden los elementos hijos de un determinado elemento.

Cuando se selecciona un hijo este paso automáticamente a la lista Elementos. Esto va bien para añadir los hijos de los hijos. 

Pulsando el botón Atrás se selecciona el elemento padre del elemento actual, el cual pasará a la lista Hijos.

Arriba se muestra la ruta completa desde el primer ancestro hasta el elemento actual. Si la información mostrada fuese incorrecta habría que pulsar el botón Recalcular ruta para actualizarla.

Desde este formulario se puede mostrar un informe o un texto del árbol de jerarquía indicado en la lista Tipos. Hay la opción de que, en el informe o en el texto, se muestren las ramas mediante el uso de caracteres Unicode especiales.

Las tablas que se editan con este formulario son: jerarquias_tipos, jerarquias_niveles y jerarquias.

Informes

Utilización de la jerarquía

Una tabla —como es el caso de la tabla personajes— que quiera hacer uso de un árbol de jerarquía, ha de usar consultas que filtren los datos de la tabla jerarquias para un valor determinado del campo tipo_id. El campo tipo_id identifica cada uno de los árboles de jerarquía introducidos en la tabla jerarquías.

Para seleccionar un nodo del árbol, lo ideal sería utilizar un control de tipo árbol. En el presente ejemplo se utiliza únicamente cuadros combinados ya que Microsoft Access carece de dicho control. Los cuadros combinados pueden mostrar la información combinando campos de la tabla jerarquias de varias formas distintas:

  • id, ruta
  • id, ruta_unix
  • id, rama + jerarquia
  • id, numeracion + jerarquia

En el formulario personajes_tabular se muestran algunos ejemplos.

Cuadro combinado progresivo

Para facilitar la introducción de un determinado nodo lo ideal sería mostrar sólo parte del árbol en vez de mostrar todo el árbol completo. La técnica es la siguiente:

  1. Si aún no se ha realizado ninguna selección en la lista se muestran los nodos del árbol correspondientes al primer nivel.
    • Ancestro uno
    • Ancestro dos
    • etc.
  2. Al elegir un nodo este se muestra como una ruta de tipo Unix desde el nodo raíz hasta la última rama que se haya indicado. Si se despliega la lista se muestra lo siguiente:
    1. El nodo actual y sus ancestros:
      • /abuelo/padre/nodo
      • /abuelo/padre
      • /abuelo
      • /
    2. Los hijos si los tiene:
      • Primer hijo
      • Segundo hijo
      • etc.

Los ancestros sirven para dar marcha atrás y los hijos para ir detallando más la selección. Para realizar una selección completa habrá que desplegar la lista tantas veces cómo sea necesario.

Podemos ver a una secuencia animada que ilustra como utilizar esta técnica. En el formulario personajes_ficha hay una implementación de la misma.

Forma de uso del cuadro combinado progresivo:

Crear un cuadro combinado llamado por ejemplo jerarquia_id con las siguientes propiedades:

Origen de la fila: select * from temp_jerarquias where clave='combo1'
Número de columnas: 2
Ancho de columnas: 0 cm;

Hay que añadir código para el evento Al hacer clic del cuadro combinado y el evento Al activar registro del formulario.

Private Sub Form_Current()
  Call jerarquia_id_Click
End Sub

Private Sub jerarquia_id_Click()
  If jerarquiaListaProgresiva(Nz(Me.jerarquia_id, 0), 1, "combo1") Then
    Me.jerarquia_id.Requery
  End If
End Sub

Parámetros utilizados en la función jerarquiaListaProgresiva:

  1. jerarquia_id — Es el valor actual del cuadro combinado. Se utiliza la función NZ porque es necesario que un posible valor NULL se convierta en 0.
  2. nivel_id — Selecciona uno de los árboles de jerarquía almacenado en la tabla jerarquias. En este caso se selecciona el número 1.
  3. clave — La clave combo1 se utiliza para realizar un filtro SQL en la propiedad origen de la fila de este cuadro combinado. El nombre de la clave es arbitrario y su utilidad radica en no causar interferencias entre distintos cuadros combinados progresivos. Cada cuadro combinado progresivo debería usar un nombre de clave distinto.

Arbol de personajes

Se trata de un formulario de ejemplo que filtra los personajes utilizando la técnica del cuadro combinado progresivo pero con una lista.  Se navega por la lista de jerarquía y se van mostrando en una segunda lista los personajes de la jerarquía indicada. Además hay un informe del árbol de personajes.

Código fuente

Módulo bdJerarquia

  • Analizar la jerarquía:
    • jerarquiaCalcular()
      Actualiza el valor de los campos: orden, tabulador, hermano, ruta, ruta_inversa, ruta_unix, numeracion y rama de la tabla jerarquias.
  • Informe:
    • jerarquiaInformeEnTexto(tipo_id, [conRamas])
      Representación en modo texto de uno de los árboles almacenados en la tabla jerarquias.
  • Consulta:
    • jerarquiaBuscarProgenitor(elemento_id, nivel_id)
      Asciende por los ancestros hasta llegar al nivel indicado.
    • jerarquiaDesplazarNivel(nivel_id, nivel_desplaz)
      Sube o baja por los niveles el número de niveles indicados (+/-)
    • jerarquiaPrimerNivel(tipo_id)
      Retorna el primer nivel del tipo indicado y sino 0
    • jerarquiaTieneHijos(elemento_id)
      Indica si el elemento tiene descendientes
    • jerarquiaPadre(elemento_id)
      Retorna el elemento padre y sino 0
    • jerarquiaNivel(elemento_id)
      'Retorna el nivel del elemento y sino 0
    • jerarquiaElemento(elemento_id)
      Retorna la descripción del elemento y sino ""
    • jerarquiaRuta(elemento_id)
      Retorna la ruta del elemento y sino ""
  • Lista progresiva:
    • jerarquiaListaProgresiva(elemento_id, tipo_id, clave)
      Actualiza la tabla temp_jerarquia para que muestre todos los ancestros y los hijos inmediatos del nodo indicado.

Código fuente

Posibles ampliaciones

Sería interesante adaptar este ejemplo a MySQL y utilizar como lenguaje de programación Java, PHP o Ruby on Rails.

La funcionalidad del cuadro combinado progresivo se podría ampliar para permitir añadir dinámicamente nuevos elementos.

Descargar

  • jerarquia.mdb.7z — Incluye tablas, formularios y código fuente. Es un archivo de Microsoft Access versión 2003 comprimido con 7-zip.

Perfect tree - Daveybot, feb-2006

Comentarios

  1. 1 Francisco 2009-04-17 Hace 8 años
    Lo he actualizado a la versión 1.1
    He mejorado la lista progresiva y he añadido un formulario e informe de ejemplos sobre el árbol de personajes.
Proinf.net, ©2003-2017 ci 3.1.5 (CC) Esta obra está bajo una licencia de Creative Commons Este software está sujeto a la CC-GNU GPL