Tablas y Funciones de SQL Server para Implementar una
Jerarquía sin Límite de NivelesLeonel Morales Díaz
Ingeniería Simple
Copyright 2008 by Leonel Morales Díaz – Ingeniería Simple.Derechos reservados Disponible en: http://www.ingenieriasimple.com/TSQL
Jerarquías usuales
• Estructura tabla padre – tabla hijo– La llave primaria de la padre es llave foránea
en la hija– Puede haber una tabla nieto
• Hasta una biznieto
Padres
CódigoDescripción
Hijos
CódigoCódigoPadreDescripción
Nietos
CódigoCódigoHijoPadre
Descripción
Problemas en jerarquías
• Limitada a tres niveles– O a la cantidad de niveles establecida– Inflexibilidad:
• Nuevos niveles reales deben ser “adaptados”
– La estructura es permanente y coercitiva• Aunque un nivel ya no sea necesario
• Dificultad de consultas– Se trata de relacionar tres o más tablas
Jerarquías de una sola tabla
• Estructura registro hijo – registro padre– La llave primaria es llave foránea de la misma
tabla• Si no hay padre la llave foránea es nula
– Tabla con relación a sí misma
Datos
CódigoCódigoPadreDescripción
Ventajas jerarquía unitabla
• Ilimitados niveles– Se ajusta a las necesidades reales
• Consultas más sencillas
• Estructura más simple– Llega a conocerse muy bien
• Bastante flexible
Desventajas
• El nivel del registro no se conoce inmediatamente– En la jerarquía tradicional se conoce el nivel
con solo saber a qué tabla pertenece– Se necesita agregar campos para esto
Jerarquía contable
• Usualmente por posiciones en una cadena– #.##.###.####– Cuenta, subcuenta, sub-subcuenta, cuenta de detalle,
etc.
• Puede ser una sola tabla– Con referencia a sí misma
• La cantidad de niveles está pre-establecida• No hay necesidad de campo con cuenta padre• Usualmente se llama Nomenclatura Contable
Ejemplo jerarquía contable
• En el código está implícito el código del padre
• Puede ser necesario poner validaciones para evitar que se inserte un código sin padre– Los códigos de longitud
1 no tienen padre
• Una sola tabla
Código Cuenta1 Activo2 Pasivo3 Capital4 Gastos5 Ingresos
1.1 Circulante1.2 Fijo1.3 Diferido2.1 Circulante2.2 Fijo2.3 Diferido3.1 Acciones al portador3.2 Acciones preferentes4.1 Fijos4.2 Variables5.1 Fijos5.2 Variables
1.1.1 Caja1.1.2 Bancos
Nomenclatura
• Implementación– Puede hacerse mediante “constraints” de tipo
“Check”• Y una función para encontrar el código padre• Si la función devuelve “Null” no se acepta
Nomenclatura
CódigoCuenta
CREATE TABLE Nomenclatura(Código nVarChar(13) NOT NULL
CONSTRAINT Código_Nomenclatura Check ((Código Like '[1-9]' Or Código Like '[1-9].[0-9][0-9]' Or Código Like '[1-9].[0-9][0-9].[0-9][0-9][0-9]' Or Código Like '[1-9].[0-9][0-9].[0-9][0-9][0-9].[0-9][0-9][0-9][0-9]')And (len(Código)=1 Or Not dbo.CuentaPadre(Código) Is Null)),
Cuenta nVarChar(30) NULL,Constraint PK_Nomenclatura Primary Key Clustered
( Código ASC ))
Función para chequeo
• CuentaPadre(@Código)– Encuentra la cuenta padre de @Código– Si no hay devuelve “Null”
CREATE FUNCTION CuentaPadre ( @Código nVarChar(13) )RETURNS nVarChar(13) AS BEGIN
DECLARE @Resu nVarChar(13)Set @Resu = NullIf CharIndex('.',@Código) > 0 Begin
Declare @PosiblePadre nVarChar(13)Set @PosiblePadre = RTrim(@Código)While Right(@PosiblePadre,1) <> '.'
Set @PosiblePadre = Left(@PosiblePadre,Len(@PosiblePadre)-1)Set @PosiblePadre = Left(@PosiblePadre,Len(@PosiblePadre)-1)
Select @Resu = Código From NomenclaturaWhere Código = @PosiblePadre
EndRETURN @Resu
END
Padre y nivel
• Se pueden implementar con campos calculados
• Padre– El valor devuelto por CuentaPadre
• Nivel– El número de puntos más 1– Se puede hacer con una función que los
cuente o aprovechando la función Like
Tabla con padre y nivel
Nomenclatura
CódigoCuentaPadreNivel
CREATE TABLE Nomenclatura(Código nVarChar(13) NOT NULL
CONSTRAINT Código_Nomenclatura Check ((Código Like '[1-9]' Or Código Like '[1-9].[0-9][0-9]' Or Código Like '[1-9].[0-9][0-9].[0-9][0-9][0-9]' Or Código Like '[1-9].[0-9][0-9].[0-9][0-9][0-9].[0-9][0-9][0-9][0-9]')And (len(Código)=1 Or Not dbo.CuentaPadre(Código) Is Null)),
Cuenta nVarChar(30) NULL,Padre As dbo.CuentaPadre(Código),Nivel As Case
When Código Like '%.%.%.%' Then 4 When Código Like '%.%.%' Then 3 When Código Like '%.%' Then 2 Else 1
End,Constraint PK_Nomenclatura Primary Key Clustered
( Código ASC ))
Datos de tabla Nomenclatura
Select * From Nomenclatura
Código Cuenta Padre Nivel------------- ------------------------------ ------------- -----------1 Activo NULL 11.01 Circulante 1 21.01.001 Caja 1.01 31.01.002 Bancos 1.01 31.01.002.0001 Banco Industrial 1.01.002 41.01.002.0002 Banco Continental 1.01.002 41.01.002.0003 Banco Internacional 1.01.002 41.02 Fijo 1 21.03 Diferido 1 22 Pasivo NULL 12.01 Circulante 2 22.02 Fijo 2 22.03 Diferido 2 23 Capital NULL 13.01 Acciones al portador 3 23.02 Acciones preferentes 3 24 Gastos NULL 14.01 Fijos 4 24.02 Variables 4 25 Ingresos NULL 15.01 Fijos 5 25.02 Variables 5 2
(22 row(s) affected)
Otros tipos de jerarquía
• Por ruta o “path”– Similar a la de directorios de windows– Se tiene un nodo “raíz” y un separador
• C:, D:, etc., son raíces• \ es el separador
– Todos los nodos de un mismo nivel tienen la misma cantidad de separadores en la ruta
– Todos los hijos de un mismo nodo comparten el mismo prefijo
• Generalización: jerarquía por prefijo– Puede o no existir separador
• En cualquier caso se usa solo una tabla
Planteamiento
• Partiendo de una jerarquía de una sola tabla construir las consultas para:– Obtener la lista de padres
• Registros sin padre
– Obtener la lista de registros en el nivel “n”– Obtener la lista de registros descendientes
del registro “R”– Obtener la lista de registros que descienden
del registro “P” y están en el nivel “m”
Tabla básica
• Solo tres campos– Código– CódigoPadre– Descripción
• El resto serán calculados– Padre– Nivel– Ruta
Datos
CódigoCódigoPadreDescripción
Creación de la tabla básica
• La tabla permite almacenar cualquier jerarquía
• En este ejemplo se usará para países y provincias geográficas (departamentos), municipios, etc.
CREATE TABLE Datos(Código Int NOT NULL,CódigoPadre Int NULL
Constraint FK_Datos_CódigoPadre Foreign Key References Datos ( Código ),
Descripción nVarChar(Max),Constraint PK_Datos Primary Key Clustered
( Código ASC ))
Datos
CódigoCódigoPadreDescripción
Registros para pruebasSelect * From Datos
Código CódigoPadre Descripción----------- ----------- -----------------------1 NULL Guatemala2 NULL El Salvador3 NULL Honduras4 NULL Nicaragua5 NULL Costa Rica6 NULL Belice7 NULL Panamá8 1 Guatemala9 1 Sacatepequez10 1 Chimaltenango11 1 Sololá12 1 Totonicapán13 1 Huehuetenango14 1 Quetzaltenango15 1 San Marcos16 1 Retalhuleu17 1 Suchitepequez18 1 Escuintla19 1 Santa Rosa20 1 Jutiapa
21 1 Jalapa22 1 Zacapa23 1 Izabal24 1 Baja Verapaz25 1 Alta Verapaz26 1 Quiché27 1 Petén28 1 El Progreso29 8 Ciudad de Guatemala30 8 Mixco31 8 Villa Nueva32 8 Jocotenango33 9 San Juan Sacatepequez34 9 San Raymundo35 9 Antigua Guatemala36 8 Amatitlán37 11 Atitlán38 11 San Pedro La Laguna39 13 Chiantla40 39 Los Regadillos41 13 Huehuetenango42 41 El Terrero43 41 El Cambote
(43 row(s) affected)
Lista de padres
• Padres:– Registros sin padre– CódigoPadre Is Null
Select * From Datos Where CódigoPadre Is Null
Código CódigoPadre Descripción----------- ----------- ------------------1 NULL Guatemala2 NULL El Salvador3 NULL Honduras4 NULL Nicaragua5 NULL Costa Rica6 NULL Belice7 NULL Panamá
(7 row(s) affected)
Lista de registros en nivel “n”
• Se necesita una función que calcule el nivel
• Puede ser recursiva
Create Function CalculaNivelDato( @Código As Int )Returns IntAs Begin
Declare @CódigoPadre IntDeclare @Nivel Int
Select @CódigoPadre = CódigoPadreFrom Datos Where Código = @Código
If (@CódigoPadre) Is NullSet @Nivel = 1
ElseSet @Nivel = dbo.CalculaNivelDato(@CódigoPadre) + 1
Return @NivelEnd
Select * From Datos Where dbo.CalculaNivelDato(Código) = 3
Código CódigoPadre Descripción----------- ----------- ---------------------29 8 Ciudad de Guatemala30 8 Mixco31 8 Villa Nueva32 8 Jocotenango33 9 San Juan Sacatepequez34 9 San Raymundo35 9 Antigua Guatemala36 8 Amatitlán37 11 Atitlán38 11 San Pedro La Laguna39 13 Chiantla41 13 Huehuetenango
(12 row(s) affected)
Nivel como campo calculado
• Se puede incorporar el nivel como campo calculado– Usando la función CalculaNivelDato
CREATE TABLE Datos(Código Int NOT NULL,CódigoPadre Int NULL
Constraint FK_Datos_CódigoPadre Foreign Key References Datos ( Código ),
Descripción nVarChar(Max),Nivel As dbo.CalculaNivelDato(Código),Constraint PK_Datos Primary Key Clustered
( Código ASC ))
Select * From Datos Where Nivel = 3 or Nivel = 4
Código CódigoPadre Descripción Nivel----------- ----------- ---------------------- ------29 8 Ciudad de Guatemala 330 8 Mixco 331 8 Villa Nueva 332 8 Jocotenango 333 9 San Juan Sacatepequez 334 9 San Raymundo 335 9 Antigua Guatemala 336 8 Amatitlán 337 11 Atitlán 338 11 San Pedro La Laguna 339 13 Chiantla 340 39 Los Regadillos 441 13 Huehuetenango 342 41 El Terrero 443 41 El Cambote 4
(15 row(s) affected)
Lista de descendientes de “R”
• Prerrequisito:– Función que construye el “path” hacía la raíz
• También se puede hacer recursiva
– Usa delimitadores: “>” antes y “=“ después• Para evitar el código 30 se confunda con el 3030
por ejemplo• Facilita las búsquedas
– Ejemplo: path de 30:
>1=>8=>30=
Función de ruta
Create Function ComponePathDato( @Código As Int )Returns nVarChar(Max)As Begin
Declare @CódigoPadre IntDeclare @Path nVarChar(Max)Set @Path = '>' + Convert(nVarChar(Max),@Código) + '='
Select @CódigoPadre = CódigoPadreFrom Datos Where Código = @CódigoIf Not (@CódigoPadre) Is Null
Set @Path = dbo.ComponePathDato(@CódigoPadre) + @Path
Return @PathEnd
Select Código, dbo.ComponePathDato(Código)From DatosWhere Nivel = 3 or Nivel = 4
Código ----------- -------------------29 >1=>8=>29=30 >1=>8=>30=31 >1=>8=>31=32 >1=>8=>32=33 >1=>9=>33=34 >1=>9=>34=35 >1=>9=>35=36 >1=>8=>36=37 >1=>11=>37=38 >1=>11=>38=39 >1=>13=>39=40 >1=>13=>39=>40=41 >1=>13=>41=42 >1=>13=>41=>42=43 >1=>13=>41=>43=
(15 row(s) affected)
Ruta como campo calculado
• Similar al caso de Nivel
CREATE TABLE Datos(Código Int NOT NULL,CódigoPadre Int NULL
Constraint FK_Datos_CódigoPadre Foreign Key References Datos ( Código ),
Descripción nVarChar(Max),Nivel As dbo.CalculaNivelDato(Código),Ruta As dbo.ComponePathDato(Código),Constraint PK_Datos Primary Key Clustered
( Código ASC ))
Select Código, Nivel, RutaFrom Datos Where Nivel = 4
Código Nivel Ruta----------- ----------- ----------------40 4 >1=>13=>39=>40=42 4 >1=>13=>41=>42=43 4 >1=>13=>41=>43=
(3 row(s) affected)
¡Ahora sí! Descendientes de “R”
• Descendientes de “R” tienen la ruta de “R” en su ruta
Declare @Ruta nVarChar(Max)
Select @Ruta = Ruta From Datos Where Código = 13
Select * From Datos Where Ruta Like @Ruta + '%' And Ruta <> @Ruta
Código CódigoPadre Descripción Nivel Ruta----------- ----------- --------------- ----------- ----------------39 13 Chiantla 3 >1=>13=>39=40 39 Los Regadillos 4 >1=>13=>39=>40=41 13 Huehuetenango 3 >1=>13=>41=42 41 El Terrero 4 >1=>13=>41=>42=43 41 El Cambote 4 >1=>13=>41=>43=
(5 row(s) affected)
Descendientes de “P” en nivel “m”
• Igual que el anterior– Pero con condición sobre el nivelDeclare @Ruta nVarChar(Max)
Select @Ruta = Ruta From Datos Where Código = 13
Select * From Datos Where Ruta Like @Ruta + '%' And Ruta <> @Ruta And Nivel = 3
Código CódigoPadre Descripción Nivel Ruta----------- ----------- ------------- ----------- ------------39 13 Chiantla 3 >1=>13=>39=41 13 Huehuetenango 3 >1=>13=>41=
(2 row(s) affected)
Select * From Datos Where Ruta Like @Ruta + '%' And Ruta <> @Ruta And Nivel = 4
Código CódigoPadre Descripción Nivel Ruta----------- ----------- --------------- ----------- ----------------40 39 Los Regadillos 4 >1=>13=>39=>40=42 41 El Terrero 4 >1=>13=>41=>42=43 41 El Cambote 4 >1=>13=>41=>43=
(3 row(s) affected)
Variaciones de las funciones
• Calcular el nivel a partir de la ruta– El nivel es el número de “>” o “=“ en la ruta
• Transformar las funciones a formas no recursivas
• Usar vistas para evitar los campos calculados
• Poner el código en “Identity” generado automáticamente
Formas no recursivas
• Función de cálculo de nivel
Create Function CalculaNivelDato( @Código As Int )Returns IntAs Begin
Declare @CódigoPadre IntSelect @CódigoPadre = CódigoPadre From Datos Where Código = @CódigoDeclare @Nivel IntSet @Nivel = 1While Not @CódigoPadre Is Null Begin
Set @Nivel = @Nivel + 1Select @CódigoPadre = CódigoPadre From Datos Where Código = @CódigoPadre
EndReturn @Nivel
End
Formas no recursivas
• Función de composición de rutas
Create Function ComponePathDato( @Código As Int )Returns nVarChar(Max)As Begin
Declare @CódigoPadre IntDeclare @Path nVarChar(Max)Select @CódigoPadre = CódigoPadre,@Path = '>' + Convert(nVarChar(Max),Código) + '='From Datos Where Código = @CódigoWhile Not @CódigoPadre Is Null
Select @CódigoPadre = CódigoPadre,@Path = '>' + Convert(nVarChar(Max),Código) + '=' + @PathFrom Datos Where Código = @CódigoPadre
Return @PathEnd