Autora: Marcia G. Akins
Traducido por: Germán Giraldo G.
Resumen
Modelar relaciones padre-hijo-nieto en Visual FoxPro es una tarea con la cual están bastante familiarizados los desarrolladores. La aproximación tradicional ha sido crear tablas padre, hijo y nieto que se enlazan. Sin embargo, no es flexible utilizando claves foráneas. Esta aproximación trabaja bastante bien cuando la jerarquía es simétrica. Sin embargo es rígida y colapsa cuando la jerarquía es asimétrica (es decir, una rama dada puede saltar un nivel en la jerarquía). En ese artículo, Marcia demuestra una aproximación alterna para modelar jerarquías que es mucho mas flexible ya que separa los datos de la estructura de la jerarquía.
Qué es una jerarquía?
Una jerarquía es una representación (generalmente mostrada gráficamente como un diagrama de árbol) de forma que los elementos de datos se relacionan unos con otros. Cada elemento está representado en la jerarquía por un
'Nodo' (generalmente representado como cajas), y cada nodo está relacionado con otro u otros nodos por
'Lados' (generalmente representados como líneas). La forma en que los nodos y sus lados, se interpretan depende de la información que está describiendo la jerarquía. De este modo en jerarquías organizacionales los nodos pueden representar
"Posiciones" o
"Grupos", y los lados representan relaciones
'informar a' o
'es un miembro de'. A la inversa en una jerarquía de Factura de Materiales los nodos representan
'Unidades de ensamble' y los lados definen las relaciones
'hecho de'.
La posición de un nodo está definida por el número de enlaces de entrada (su
'grado de entrada') que define sus
'padres' y el número de enlaces salientes (su
'grado de salida') que define sus
'hijos'. Un nodo con un grado de entrada de cero y un grado de salida mayor que cero define el punto inicial de una jerarquía y se denomina como el nodo
'Raíz'. A la inversa un nodo con un grado de entrada mayor que cero y un grado de salida cero define el punto final de una jerarquía y se denomina como nodo
'hoja'. Los nodos donde ambos grados el de entrada y el de salida son mayores que cero son 'intermedios' y aquellos donde ambos son cero son
'aislados'.
Tipos de jerarquías
Las jerarquías simples constan de una única familia de nodos que están directamente relacionados unos con otros. En este caso la jerarquía se representa como un único
'Arbol'. Las jerarquías mas complejas pueden estar compuestas por mas de una familia de nodos y la jerarquía es una colección de
'SubArboless'. Sin embargo la jerarquía compleja, en su definición característica es posible trazar desde algún nodo en la jerarquía a algún otro nodo siguiendo los lados. Otra característica es que todos los nodos excepto el raíz tienen un grado de entrada de al menos 1. Mientras existe una infinita variedad de posibles jerarquías que pueden definirse utilizando Nodos y Lados, hay dos tipos básicos
'Simétrica' o
'Asimétrica'.
Jerarquías Simétricas
Una jerarquías simétrica es aquella en la cual el número de niveles es el mismo para todos los subárboles componentes (Figura 1). Observe que los subárboles no son necesarios para ser simétrica, lo que define la jerarquía como simétrica es que hay al menos un nodo en cada nivel separado de cada subárbol.
Figura 1: Una jerarquía simétrica
Tal jerarquía puede modelarse utilizando simples tablas relacionales, donde una tabla se utiliza para representar cada nivel de la jerarquía (Figura 2). La relación entre registros en cualquier par de tablas es siempre al menos uno a uno y nunca puede haber un espacio en la estructura.
Figura 2: Modelar una jerarquía simétrica con tablas relacionales
Jerarquías Asimétricas
Una jerarquía asimétrica es aquella en la cual el número de niveles no es el mismo para todos los subárboles componentes (Figura 3). Observe que, de nuevo, la estructura de los subárboles no importa. La definición característica es que no siempre hay un nodo en cada nivel de cada subárbol (Figure 3).
Figura 3: Una jerarquía asimétrica
Es claro que este tipo de jerarquía no puede representarse mapeando los niveles a un conjunto de tablas relacionales ya que hay espacios en la estructura. El primer subárbol no tiene nodos
"Nivel 2", mientras que el segundo tiene un nodo hoja en el
"Nivel 1" y ninguno a continuación de este. El tercer subárbol tiene nodos hoja e intermedio en el
"Nivel 2" y un nodo Hoja que existe en el
"Nivel 4" pero está relacionado directamente a un nodo en el
"Nivel 2" que también tiene hijos en el
"Nivel 3".
Ya que no hay reglas claras acerca de cómo está construido cualquier subárbol dado, la única forma en la cual se puede modelar tal jerarquía es definiendo las relaciones individuales que existen entre pares de nodos. Generalmente esto se hace definiendo una tabla reflexiva (también conocida como
'auto-referencial') en la cual cada registro define un nodo y su padre inmediato (Figura 4).
Figura 4: Modelar una jerarquía asimétrica e una tabla reflexiva
Trabajar con jerarquías
Como se estableció al comienzo de este artículo, el propósito de cualquier jerarquía es describir como se relacionan unos con otros los elementos de datos. La razón para hacer esto es permitir agregar datos crudos así como evitar la necesidad de trabajar directamente con los detalles. Las jerarquías nos permiten definir significativos niveles de detalle, si estos "Equipo de Ventas" o "Componentes de Ensamblado" solo dependen de su interpretación. Si estamos tratando solo con jerarquías simétricas no hay dificultad. SQL nos permite realizar las operaciones importante para agregar directamente en las series de tablas relacionadas que describen la jerarquía. Entonces para recuperar una lista de todos los nodos en cualquier nivel, simplemente consultamos la tabla correspondiente. Para encontrar todos los hijos de cualquier nodo dado sólo se requiere una única unión. Para atravesar la jerarquía completa se requiere una serie de uniones pero aún es posible hacerlo directamente utilizando SQL.
Sin embargo, tan pronto como tengamos que considerar la posibilidad de una jerarquía asimétrica tendremos un problema. Como se anotó antes, tales jerarquías solo pueden modelarse definiendo las relaciones entre pares de nodos directamente. Con el fin de reconstruir la jerarquía tenemos que realizar una operación a menudo referida como 'recorrido de árbol'. En otras palabras tenemos que realizar los siguientes pasos:
[1] Recuperar la clave de inicio para el nodo raíz [2] Encontrar todos los registros donde esa clave está definida como el padre (Set1). [3] Para el primer registro en el conjunto de resultados encontrar todos los registros donde esa clave está definida como el padre [4] Repetir [3] hasta que no haya mas hijos [5] Repetir pasos [3] y [4] para cada registro del Conjunto1 (Set1)
Desafortunadamente SQL (el cual es un lenguaje basado en conjuntos) simplemente no soporta tales operaciones recursivas, o funciones que dependan de ellas.
(Curiosamente tal soporte fue propuesto para el estándar SQL3 y se denominó operador 'RECURSIVE UNION' pero fue retirado en 1996 y no hay planes para reintroducirlo en el estándar). Por lo tanto hay tres posibilidades:
- Controlar este tema en código. Esta es la solución adoptada mas a menudo; las herramientas y aplicaciones que dependen de jerarquías generalmente incluyen funcionalidad que realizará el recorrido de árbol en forma transparente. El ejemplo clásico es la "Factura de Materiales" encontrada en aplicaciones de manufactura y control de inventarios. El problema es que tal código es difícil de escribir y, cuando hay grandes volúmenes de datos y muchos niveles es comparativamente lento para ejecutarse.
- Usar un enfoque de conjuntos anidados. Este enfoque confía sobre todo en el hecho que una jerarquía no sólo puede representarse como un árbol, también puede representarse como un conjunto de datos anidados. El problema con este enfoque es que es difícil de controlar dinámicamente o cambiar estructuras. Este enfoque se describe en detalle en la siguiente sección.
- Usar un enfoque de mapeo. Este enfoque confía en el hecho que es posible crear, para cada nodo, un conjunto detallado de caminos los cuales definen su ubicación. Tales caminos se utilizan para definir todos los posibles resultados de 'recorrer el árbol' para cada nodo en la jerarquía. Este enfoque se describe en detalle mas adelante.
El enfoque de Conjuntos Anidados
El modelo de conjuntos anidados confía en el hecho que en lugar de ser vista como un árbol, una jerarquía puede ser vista como un conjunto de datos anidados. La Figura 5 bosqueja la misma jerarquía ilustrada en la Figura 3 con cada nodo identificado por una única clave (una única letra en este caso).
Figura 5: Jerarquía asimétrica con nodos identificados por claves
Esta misma jerarquía también puede verse como un conjunto anidado reemplazando las líneas utilizadas generalmente para representar los lados de la Jerarquía con nodos están anidadazos cada uno dentro del otro (Figure 6).
Figura 6: Jerarquía asimétrica como nodos anidados
Un examen de este diagrama muestra que es, en efecto, idéntico al árbol mostrado en la Figura 5. De modo que los nodos "B", "C" y "D" son hijos de "A". Los nodos "G" y "H" son hijos de "B" y "K" es el único hijo de "H". Este diagrama puede representarse en una tabla de datos asociando un par de números con cada nodo. Estos números definen el número mas bajo que existe dentro de un subárbol de nodos dado (si hay alguno) y el número mas alto dentro de ese subárbol. La Figura 7 muestra la jerarquía con el primer subárbol numerado.
Figura 7: Identificar nodos anidados con pares de números
(Para visualizarlo mas fácil puede pensar en los números como definidos empezando por el nodo raíz del árbol en la figura 6 con el número "1" y luego visitar cada nodo del árbol uno a la vez. Cuando entra en cada nodo, incremente el número por 1 y asígnelo al lado 'izquierdo' del nodo. Cuando alcance un nodo hoja, incremente el número en uno y asígnelo a la 'derecha'. Cuando regrese a cada nodo después de visitar todos los hijos, de nuevo incremente el número por 1 y asígnelo a la 'derecha'. Este enfoque algunas veces se denomina como 'Numeraicón izquierda-derecha' por esa misma razón).
El resultado es que cada nodo define los valores límites que identifican a todos sus hijos. Entonces es posible recuperar todos los nodos que caen dentro un subárbol dado en una única consulta dejando fuera aquellos nodos cuyo valor 'izquierdo' es menor que ese valor del nodo padre. Esto es obvio en la Figura 7 donde el Nodo B define el rango [2-9]. Seleccionado todos los nodos donde
"LEFT > 2 AND RIGHT < 9" podemos obtener los nodos "G", "H" y "K". Esta es una forma muy poderosa de controlar cualquier tipo de jerarquía, sea simétrica o asimétrica y trabaja limpiamente sobre el problema de tener que recorrer dinámicamente el árbol para definir los límites como parte de la definición del nodo.
También se controla fácilmente dentro del contexto de un enfoque estándar, definiendo los nodos de una jerarquía como una serie de relaciones padre-hijo. Todo lo que se necesita son dos columnas adicionales en la tabla (generalmente llamadas 'lft' y 'rgt' para evitar conflictos con las palabras clave 'LEFT' y "RIGHT') para mantener los valores límite para cada nodo. La Figura 8 muestra tal estructura y los datos para la porción numerada de la jerarquía.
Figura 8: Implementar Numeración Izquierda-Derecha
La gran desventaja de este enfoque es que en realidad sólo es adecuado para estructuras que son estáticas. Insertar o eliminar un nodo o mover un nodo de un lugar a otro, requiere validar la numeración completa del árbol..Este puede ser un proceso extensor y difícil debido a que requiere recorrer todo el árbol en un orden específico para asegurar que los límites reflejan correctamente las estructuras dependientes. Esto no significa que no sea un enfoque válido, se utiliza ampliamente cuando se trabaja con jerarquías que son esencialmente estáticas y como se anotó elimina las limitaciones de SQL al evitar la necesidad de utilizar un algoritmo recursivo. Sin embargo es muy rígida y solo es la mejor solución en ciertas circunstancias muy especializadas.
El enfoque de Mapeo
El tercer enfoque rompe con el método tradicional de representar una jerarquía como una serie de relaciones padre hijo definidas en una única tabla reflexiva. En lugar de tratar de representar las relaciones entre pares de nodos, describimos la jerarquía como el conjunto de todos los caminos aceptables entre una serie de nodos aislados.
Para hacerlo simplemente atravesamos la jerarquía iniciando con el nodo raíz y, observe que para cada nodo que encontramos, se identifican todos los nodos que hay entre él y el nodo raíz. Es importante reconocer que esta metodología está firmemente arraigada en la definición de qué constituye una jerarquía, que confía en el principio que es posible trazar un camino continuo entre el nodo raíz y cualquier nodo. También es importante reconocer que puesto que cada nodo es tratado individualmente, no hay que considerar dependencias internas. No importa si la jerarquía es simétrica o asimétrica. Cualquier jerarquía puede describirse con este modelo.
Entonces la jerarquía mostrada en la Figura 5 puede representarse por los datos mostrados en la Tabla 1.
| Nodo | Camino |
|---|
| A | A* |
| B | A*B* |
| G | A*B*G* |
| H | A*B*H* |
| K | A*B*H*K* |
| C | A*C* |
| D | A*D* |
| E | A*D*E* |
| F | A*D*F* |
| I | A*D*F*I* |
| L | A*D*F*L* |
| J | A*D*F*J* |
Tabla 1: Mapeo de la jerarquía de la Figure 5
Estos datos simplemente podrían almacenarse en una única tabla pero generalmente se almacenan en un par de tablas relacionadas, la primera define los nodos en si mismos y la segunda define los caminos entre ellos. La razón para separar los datos en dos tablas es para permitir la posibilidad que la misma definición de nodo se utilice en mas de una jerarquía. (Por ejemplo en una jerarquía que enlaza dos organizaciones y define la estructura de cada subárbol, el mismo nodo "departamento" puede aparecer en cada una). Ya que no hay necesidad de definir la estructura directamente con el nodo, podemos normalizar los datos. La Figura 9 muestra las tablas utilizadas.
Figura 9: Implementar una jerarquía mapeada
Observe que la tabla de definición (Node_Def) ahora únicamente nos permite asignar una descripción para el nodo. Ahora es la tabla de estructura (Node_Stru) la que describe como se relacionan los nodos y la columna node_path de esta tabla es la clave para toda la metodología. La columna para el camino puede utilizarse directamente en consultas SQL para recuperar conjuntos de datos que conforman una plantilla estructural específica y esto nos permite desmenuzar los datos por fuera de las tablas asociadas. Por esta razón el campo 'camino' generalmente se denomina como 'Campo Rip' y la tabla de estructura como 'Tabla Rip'.
Adjuntando datos a la estructura, en lugar de a los nodos, logramos un grado de libertad que no puede alcanzarse con ningún otro enfoque como se describe en la sección final de este artículo.
Utilizar Mapeo para Administrar jerarquías
El siguiente ejemplo intenta ilustrar como el uso del enfoque de mapeo puede resolver algunos de los asuntos clave asociados con la administración de jerarquías en el entorno de una aplicación. Para el propósito de este ejemplo considere la posición de una representante de ventas, llamada Amanda Barry, quien trabaja fuera de la planta de Massillon para una Compañía que tiene plantas en varias partes del país. Ella actualmente es miembro simultáneamente de tres jerarquías separadas. Estas son:
- Equipo de Ventas
- Planta
- Clientes
Examinaremos cada una a la vez y las modelaremos como corresponde.
Jerarquía del Equipo de Ventas
La Fuerza de Ventas de la compañía está organizada en equipos los cuales están basados en territorios geográficos. La jerarquía que describe esta estructura se muestra en la Figura 10. Observe que estos nodos representan no solo agrupaciones lógicas, si no que en algunos casos son también posiciones dentro de la compañía y tienen personas asociadas con ellos. Por ejemplo, Amanda es miembro del equipo "Great Lakes", el cual tiene un Gerente (Charlie Davis). Pero el equipo Great Lakes se considera una parte de "Northern Sub-Division" la cual es solo una forma conveniente de subdividir los cuatro equipos que comprenden la Eastern Division. No hay personas asociadas con el Nodo "Northern Sub".
Figura 10: Jerarquía de Ventas de la compañía
Para modelar esta estructura requerirá un mínimo de dos tablas, como se describió anteriormente, una para Definiciones de Nodo y otra para el mapeo de datos (o despiece). Ya que sabemos que deseamos definir múltiples jerarquías, agregaremos una tercera tabla a la estructura, para permitirnos categorizar los nodos. El la Figura 11 se muestra la estructura que usaremos:
Figura 11: Estructura básica de tablas para el mapeo de jerarquías
Para mapear la estructura requerimos entradas en estas tablas como se muestra en la Tabla 2
| Tabla Categoría de Nodo (NodeCat) | | Tabla Definición de Nodo (Node) |
|---|
| node_cat_pk | node_cat_desc | | node_pk | node_cat_fk | node_desc |
|---|
| 1 | Sales Organization | | 1 | 1 | National Sales |
| | | 2 | 1 | Eastern Division |
| | | 2 | 1 | Western Division |
| | | 4 | 1 | Northern Sub-Division |
| | | 5 | 1 | Southern Sub-Division |
| | | 6 | 1 | California |
| | | 7 | 1 | Western |
| | | 8 | 1 | Mid-West |
| | | 9 | 1 | Great Lakes |
| | | 10 | 1 | South West |
| | | 11 | 1 | Central |
Tabla 2: Primeras entradas para la Categoría de Nodos y la Tabla Nodos
Ahora podemos mapear la estructura a la tabla de estructura trazando los caminos. Una vez que se ha definido todos los caminos pueden agregarse los datos a la jerarquía (Figura 11). Los datos están en la Tabla 3.
Observe que utilizamos el carácter "*" no sólo para separar si no también para terminar las referencias de claves en el campo rip (despiece). Esto es para que mas tarde podamos consultar este campo y evitar los problemas asociados con coincidencias parciales en las referencias de claves. Esto es necesario para evitar obtener coincidencias parciales cuando consultamos la tabla utilizando el operador LIKE en el campo rip. Considere el siguiente par de campos rip: "10*20*30" y "10*20*300". Claramente ellos no se refieren al mismo nodo pero si fuéramos ha realizar una consulta donde el valor es LIKE "10*20*30%" podríamos obtener ambos. Utilizando el terminal "*" evitamos este problema.
| Tabla Estructura de Nodo (NodeStru) |
|---|
| nodestru_pk | node_fk | Cripfield |
|---|
| 1 | 1 | 1* |
| 2 | 2 | 1*2* |
| 3 | 3 | 1*3* |
| 4 | 4 | 1*2*4* |
| 5 | 5 | 1*2*5* |
| 6 | 6 | 1*2*6* |
| 7 | 7 | 1*3*7* |
| 8 | 8 | 1*2*4*8* |
| 9 | 9 | 1*2*4*9* |
| 10 | 10 | 1*2*5*10* |
| 11 | 11 | 1*2*5*11* |
Tabla 3: Mapear la Jerarquía Ventas
La tarea final es enlazar las personas al nodo apropiado en la jerarquía. Para el propósito de esta ilustración no importa como se almacenan los datos de los Empleados, simplemente asumimos que en algún lugar existen tablas que incluyen un valor clave para cada empleado y la información usual asociada (Nombre, Cargo, Fecha de Ingreso etc). Estos datos se representan por "Emp_Data Table" en la Figura 11.
Utilizamos una tabla de asignación para permitirnos enlazar a un único empleado a diferentes nodos (ya sabemos que Amanda es un miembro de tres jerarquías, así que ella estará asociada con al menos tres nodos, uno en cada jerarquía).
Figura 12: Enlazar personas a su nodo
El punto importante a observar es que esta tabla de asignación enlaza personas a la tabla de estructura, no al nodo!
La razón para es que la tabla de nodos es simplemente una mirada que nos permite dar un nombre significativo a un nodo en la jerarquía. Ella no tiene ninguna importancia y ciertamente, el mismo nombre puede utilizarse en cualquier lugar en diferentes jerarquías (cuántos departamentos "Admin" hay en el mundo?). Si fuéramos a adjuntar personas directamente al registro del nodo podríamos necesitar un registro de nodo separado para cada ocurrencia individual de un nodo en la jerarquía, por esta razón hacer la relación entre un nodo y su estructura uno a uno, forzándonos a regresar a una estructura muy rígida.
Ahora podemos agregar los registros necesarios para la tabla de asignación para las personas que conocemos, mas el Vice Presidente a cargo de Sales Nationally (Jane Kingsland) y la cabeza de Eastern Division (Martin Niles) y el gerente y un representante de ventas para el equipo Mid-West:
| Datos Referencia de Empleado (Emp_Data) | | Tabla Asignación de Persona/Estructura (Ct_Psn_Stru) |
|---|
| person_pk | Name | | Psnstru_pk | person_fk | nodestru_fk |
|---|
| 1 | Amanda Barry (Sales Rep) | | 1 | 1 | 9 |
| 2 | Charlie Davis (Sales Manager) | | 2 | 2 | 9 |
| 3 | Jane Kingsland (VP Sales) | | 3 | 3 | 2 |
| 4 | Martin Niles (Eastern Division) | | 4 | 4 | 2 |
| 5 | Ellen Franks (Sales Manager) | | 5 | 5 | 8 |
| 6 | George Harrison (Sales Rep) | | 6 | 6 | 8 |
Tabla 4: Enlazar personas a su jerarquías
Qué nos da esto?
Ahora permítanos crear vistas estáticas que consulten la jerarquía y devuelvan los miembros coincidentes. Aquí están las definiciones necesarias para dos vistas. La primera retorna el campo rip (despiece) asociado con una descripción de nodo dada:
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('v_GetRip') and sysstat & 0xf = 2)
DROP VIEW v_GetRip
GO
CREATE VIEW v_GetRip AS
SELECT RTRIM(cripfield) + '%' cRipMask, node_desc
FROM nodestru RF, node NN
WHERE RF.node_fk = NN.node_pk
Una simple consulta es todo lo que necesitamos ahora para retornar la mascara de campo rip apropiada que define el punto de inicio para la selección de datos. Es importante reconoce que esta 'máscara' permite extraer información desde cualquier nivel de la jerarquía son necesidad de conocer algo acerca del nivel o la relación entre los nodos. Por ejemplo:
SELECT cRipMask FROM v_getrip WHERE node_desc = 'Great Lakes' RETURNS "1*2*4*9*%"
while
SELECT cRipMask FROM v_getrip WHERE node_desc LIKE 'Mid%' RETURNS "1*2*4*8*%"
Entonces esta vista puede combinarse con otra vista estática para devolver una lista de personas que son miembros de cualquier nodo por el que deseemos consultar:
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('v_GetMembers') and sysstat & 0xf = 2)
DROP VIEW v_GetMembers
GO
CREATE VIEW v_GetMembers AS
SELECT CT.person_fk, GR.node_desc
FROM ct_psn_stru CT, nodestru NS, v_getRip GR
WHERE CT.nodestru_fk = NS.nodestru_pk
AND NS.cripfield LIKE GR.cripmask
Unas simples consultas en estas vistas retornan la lista de personas que califican:
SELECT person_fk FROM v_getmembers WHERE node_desc = 'Great Lakes' RETURNS [1,2]
while
SELECT person_fk FROM v_getmembers WHERE node_desc = 'Mid-West' RETURNS [5,6]
and
SELECT person_fk FROM v_getmembers WHERE node_desc = 'Northern Sub-Division' RETURNS [1,2,5,6]
Cualquier consulta relacionada con la Personas de Ventas ahora es algo simple. Asumiendo que tenemos información de ventas relacionada con el ID de la persona que realiza la venta, podemos obtener el total de ventas para cualquier nodo en la jerarquía simplemente sumando las ventas asociadas con la lista de la persona que está relacionada con ese nodo:
SELECT SUM( sales) FROM sales_data WHERE sales_person_id IN
(SELECT person_FK FROM v_getmembers WHERE node_desc = 'Great Lakes' )
El mismo método puede utilizarse para extraer los miembros de un Equipo de Ventas
SELECT name FROM emp_data WHERE person_pk IN
(SELECT person_FK FROM v_getmembers WHERE node_desc = 'Great Lakes' )
La Jerarquía Planta
Una parte de una 'jerarquía de planta' nacional se ilustra en la Figura 13.
Figura 13: La jerarquía de planta
Podemos simplemente agregar los datos para esta jerarquía a nuestras tablas existentes (Tabla 5) en exactamente la misma forma como se hizo para la Organización Ventas detallada anteriormente.
| Categoría de Nodo (NodeCat) | | Tabla Definición de Nodo (Node) | | Tabla Estructure de Nodo (NodeStru) |
|---|
| node_cat_pk | node_cat_pk | | node_pk | node_cat_fk | node_desc | | nodestru_pk | node_fk | cripfield |
|---|
| 2 | Plants | | 12 | 2 | All US Plants | | 12 | 12 | 12* |
| | | 13 | 2 | California Plants | | 13 | 13 | 12*13* |
| | | 14 | 2 | Ohio Plants | | 14 | 14 | 12*14* |
| | | 15 | 2 | North Ohio | | 15 | 15 | 12*14*15 |
| | | 16 | 2 | South Ohio | | 16 | 16 | 12*14*16 |
| | | 17 | 2 | Cleveland | | 17 | 17 | 12*14*15*17* |
| | | 18 | 2 | Massillon | | 18 | 18 | 12*14*15*18* |
| | | 19 | 2 | Columbus | | 19 | 19 | 12*14*16*19* |
| | | 20 | 2 | Cincinnatti | | 20 | 20 | 12*14*16*20* |
Tabla 5: Agregar la jerarquía de planta
Ahora necesitamos asociar los Representantes de Ventas con la planta que ellos representan (Tabla 6). En este caso solo Amanda Barry (Massillon) y George Harrison (Columbus) están asociados directamente con plantas, así que solo se requieren dos nuevas entradas en la tabla de asignación (obviamente podrían existir otras personas asociadas con la jerarquía de plantas pero el objetivo aquí es enfocarnos sólo en el lado de las Ventas del negocio):
| Datos Referencia de Empleado (Emp_Data) | | Tabla Asignación de Persona/Estructura (Ct_Psn_Stru) |
|---|
| person_pk | Name | | psnstru_pk | person_fk | nodestru_fk |
|---|
| 1 | Amanda Barry (Sales Rep) | | | 1 | 18 |
| 2 | Charlie Davis (Sales Manager) | | | | |
| 3 | Jane Kingsland (VP Sales) | | | | |
| 4 | Martin Niles (Eastern Division) | | | | |
| 5 | Ellen Franks (Sales Manager) | | | | |
| 6 | George Harrison (Sales Rep) | | | 6 | 19 |
Tabla 6: Personas asociada con la Jerarquía de Planta
Observe que ahora podemos sumar nuestras ventas por cualquier nodo en cada jerarquía utilizando la misma consulta básica. Para obtener todas las ventas para todas las Plantas de Ohio la consulta es simplemente:
SELECT SUM( sales) FROM sales_data WHERE sales_person_id IN
(SELECT person_FK FROM v_getmembers WHERE node_desc = 'Ohio Plants' )
y para obtener una lista de representantes que están trabajando fuera de cualquier planta determinada, utilizamos exactamente la misma consulta que utilizamos para obtener las ventas del equipo:
SELECT name FROM emp_data WHERE person_pk IN
(SELECT person_FK FROM v_getmembers WHERE node_desc = 'Columbus' )
La Jerarquía Cliente
Agregar una jerarquía cliente es tan simple como agregar la jerarquía planta. La Figura 14 muestra una jerarquía parcial para un grupo grande de clientes y muestra directamente las entradas para las tablas Nodo y NodeStru en el diagrama:
Figura 14: Detalles de la jerarquía cliente
Una vez mas lo único que necesitamos hacer para acomodar esta nueva jerarquía es enlazar nuestras personas de ventas a los nodos apropiados en la jerarquía. Para ilustración, estamos mapeando nuestros Representantes de Ventas al mas bajo nivel de su jerarquía y, nuestra Cabeza de División a nivel de Estado.
| Datos Referencia de Empleado (Emp_Data) | | Tabla Asignación de Persona/Estructura (Ct_Psn_Stru) |
|---|
| person_pk | Name | | psnstru_pk | person_fk | nodestru_fk |
|---|
| 1 | Amanda Barry (Sales Rep) | | | 1 | 24 |
| 2 | Charlie Davis (Sales Manager) | | | | |
| 3 | Jane Kingsland (VP Sales) | | | | |
| 4 | Martin Niles (Eastern Division) | | | 4 | 23 |
| 5 | Ellen Franks (Sales Manager) | | | | |
| 6 | George Harrison (Sales Rep) | | | 6 | 25 |
Tabla 7: Personas asociadas con la Jerarquía Cliente
Mantener la estructura
Como puede verse, utilizar el enfoque detallado aquí es muy fácil de mapear y, facilita recuperar los datos asociados con, cualquier número de jerarquías. Definiendo vistas simples para ocultar la complejidad de la estructura actual es posible soportar cualquier interfaz de usuario que se desee y aún agregar y borrar dinámicamente jerarquías completas. Las estructuras ilustradas aquí muestran el mínimo de información necesaria para administrar los datos pero muestra los principios que son la base del enfoque. Un refinamiento obvio para la tabla Node Structure es agregar una clave "nivel", para permitir recuperar datos a través de todos los nodos que se encuentran en un nivel específico de una jerarquía (por ejemplo, se podría implementar muy fácilmente un TreeView para mostrar el contenido de una jerarquía).
La tabla estructura de nodos puede mantenerse fácilmente utilizando desencadenantes que, cuando inserten un nuevo registro, genere el campo rip (y también el nivel) automáticamente basado e en el campo rip (y nivel) del nodo al cual se relaciona el nuevo registro.
A diferencia del enfoque Numeración Izquierda-Derecha, cambiar las jerarquías solo afecta aquellos registros que están directamente involucrados. No es necesario reconstruir la jerarquía completa cuando se agrega un nodo, se elimina o se mueve. Todo lo que se necesita es cambiar algunos registros que incluyen el nodo afectados, reemplazando la porción relevante del campo rip con la versión corregida.
Por ejemplo, si fuéramos a dispersar la Sub División Southern (Figura 10) y movemos su equipo de ventas "Central" a la Sub División Northern y el equipo "South West" a la División Western, se afectan un total de tres registros en el tabla Node Structure (Tabla 8):
| Antes | | Después |
|---|
| nodestru_pk | node_fk | cripfield | | nodestru_pk | node_fk | cripfield |
|---|
| 1 | 1 | 1* | | 1 | 1 | 1* |
| 2 | 2 | 1*2* | | 2 | 2 | 1*2* |
| 3 | 3 | 1*3* | | 3 | 3 | 1*3* |
| 4 | 4 | 1*2*4* | | 4 | 4 | 1*2*4* |
| 5 | 5 | 1*2*5* | | 5 | Record is deleted |
| 6 | 6 | 1*3*6* | | 6 | 6 | 1*3*6* |
| 7 | 7 | 1*3*7* | | 7 | 7 | 1*3*7* |
| 8 | 8 | 1*2*4*8* | | 8 | 8 | 1*2*4*8* |
| 9 | 9 | 1*2*4*9* | | 9 | 9 | 1*2*4*9* |
| 10 | 10 | 1*2*5*10* | | 10 | 10 | 1*2*4*9* |
| 11 | 11 | 1*2*5*10* | | 11 | 11 | 1*3*11* |
Tabla 8: Cambios para dispersar un grupo de ventas y mover sus equipos a dos grupos diferentes
Obviamente también debe actualizarse cualquier tabla que incluya referencias a los registros borrados, pero el punto clave aquí es que no hay que hacer mas cambios. Todos los datos agregados previamente al grupo "Southern Sub" ahora se agregan a la división "northern" o a la "western".
Conclusión
El enfoque de mapeo descrito aquí proporciona el método mas flexible y fácil de mantener para modelar los datos de una jerarquía de cualquier tipo. Separando la información estructural de la definición, recuperar la información de la jerarquía se simplifica hasta el punto donde SEQL puede utilizarse directamente en lugar de requerir operaciones recursivas para recorrer el árbol o la complejidad de e inflexibilidad de la numeración izquierda-derecha las cuales son las únicas alternativas viables.
Acerca del autor
Marcia es una Consultora independiente y desarrolladora de software quien por los pasados años ha trabajado principalmente con Visual FoxPro. Ella y su esposo, Andy Kramek son propietarios de Tightline Computers, Inc. En su casa en Akron, Ohio. A ella se le ha otorgado el Microsoft Most Valuable Professional desde 1999 y también tiene su calificación Microsoft Certified Professional para ambos Aplicaciones Distribuidas y de Escritorio en Visual FoxPro.
Marcia es coautora de la columna Kitbox en FoxTalk Magazine desde November, 2001. Su trabajo publicado también incluye varios artículos para ambos FoxPro Advisor y FoxTalk magazines como también el exitoso libro "1001 Things You Wanted to Know About VFP" (Hentzenwerke publishing, 2000) y "MegaFox: 1002 Things You Wanted to Know About Extending VFP" (Hentzenwerke publishing, 2002).
Sus conferencias incluyen SouthwestFox (Tempe, 2004, 2005), OzFox (Sydney, Australia, July 2003), Visual FoxPro Devcon (Prague, Czech Republic, June 2002 and 2005), Essential Fox (Kansas City, 2002, 2003, 2004), Conference to the Max (Holland, May 2000 and May 2002), Great Lakes Great Database Workshop (Milwaukee, 2000, 2001, 2002, 2003), Advisor Devcon (San Diego, September 2001 and Fort Lauderdale, September 2002), German Devcon (Frankfurt, November 2001, 2002, 2003, 2005), también reuniones de grupos de usuarios en Europa y U.S.
