Separata 03 Desarrollo Fisico de La Desnormalizacion
Click here to load reader
Transcript of Separata 03 Desarrollo Fisico de La Desnormalizacion
Sistema de Bases II Desarrollo Físico - Desnormalización
DESARROLLO FISICO DE LA DESNORMALIZACION
En esta sección presentaremos ejemplos de las 11 técnicas de
desnormalización descritas anteriormente. La pregunta que siempre habrá que tener
en la mente cuando lea esta información es si los posibles beneficios obtenidos cuando
se desnormaliza realmente superan al coste añadido debido al esfuerzo adicional de
codificación y documentación. Con frecuencia la desnormalización se ejecuta para
obtener beneficios en el rendimiento de las funciones de generación de informes. Sin
embrago, habremos de tener siempre presente que desnormalización reduce el
rendimiento desde la perspectiva del proceso de transacciones, de esta forma , habrá
que evaluar qué es mejor para nosotros, acelerar la generación de informes o disminuir
el rendimiento de las transacciones.
El método ideal para realizar la desnormalización es utilizando
desencadenadores. Si puede permitirse el lujo de esperar para realizar actualizaciones en
modo diferido, entonces podrá desarrollar un OLAP para este tipo de sistemas de
información y no tendrá que sacrificar la integridad de su diseño OLTP con el fin de
mejorar el rendimiento de la generación de informes. En nuestra opinión, no deberá
desnormalizarce los sistemas de generación de informes para mejorar las prestaciones.
TÉCNICAS DE DESNORMALIZACIÓN
Hemos identificado 11 tipos de de desnormalización que pueden ser necesarios para
facilitar el código o por motivos de rendimiento. Describiremos cada una de estas
técnicas y mostraremos ejemplos.
Campos total redundante
Utilizaremos el escenario definido por las órdenes de compra simple y detallada para
el ejemplo del campo total. Por desgracia, este ejemplo cae en el tema de la tabla de
mutación, salvo que creemos una tercera que duplique la tabla OC_DTL.
Sistema de Bases II Desarrollo Físico - Desnormalización
Suponga que queremos obtener cantidades en dólares divididas por segundo por orden
de compra. Podremos satisfacer este requisito añadiendo una columna ‘X-CANT’ a la
tabla OC. Esta columna se actualizará con el valor total en dólares de la OC si mas que
sumar los valores individuales en dólares de cada OC_DTL asociada con una OC
determinada. Para el presente ejemplo, hemos creado tres tablas OC, OC_DTL y
X_OC_DTLy un desencadenador AIU_X_OC_DTL para la tabla X_OC_DTL, tal y
como se muestra en el código siguiente:
CREATE TABLE OC (OC_ID NUMBER (10) NOTNULL,DESCR_TXT VARCHAR2 (100) NOTNULL,DIREC_ID_COM_TO NUMBER (10) NOTNULL,DIREC_ID_FACT_TO NUMBER (10) NOTNULL,CTCT_ID NUMBER (10) NOTNULL,VENDEDOR_ID NUMBER (10) NOTNULL,X_CANT NUMBER (10,2) NOTNULL )/CREATE TABLE OC_DTL (OC_ID NUMBER (10) NOTNULL,OC_DTL_ID NUMBER (10) NOTNULL,ARTIC_ID NUMBER (10) NOTNULL,ORDEN_CANT NUMBER (10,2) NOTNULL,ORDEN_PRC NUMBER (10,2) NOTNULL )/CREATE TABLE X_OC_DTL (OC_ID NUMBER (10) NOTNULL,OC_DTL_ID NUMBER (10) NOTNULL,ARTIC_ID NUMBER (10) NOTNULL,ORDEN_CANT NUMBER (10,2) NOTNULL,ORDEN_PRC NUMBER (10,2) NOTNULL )/CREATE TABLE OR REPLACE TRIGGER AIU_OC_DTLAFTER INSERT OR UPDATEON X_OC_DTLFOR EACH ROW
DECLARECURSOR C1 IS SELECT ORDEN_CANT * ORDEN_PRC X_CANT_OC_DTLFROM X_OC_DTL WHERE OC_ID =:NEW.OC_ID;
X_CANT_OC OC.X_CANT%TYPE :=0;
BEGINFOR C1_REC IN C1 LOOPX_CANT_OC :=X_CANT_OC + C1_REC.X_CANT_OC_DTL;END LOOP;UPDATE OC SET X_CANT =X_CANT_OC WHERE OC_ID =:NEW.OC_ID;END;/
Sistema de Bases II Desarrollo Físico - Desnormalización
El principal inconveniente de este caso es que debemos crear una tercera una
tercera tabla, X_OC_DTL, lo que no es muy práctico de cara a la desnormalización. El
principal inconveniente es el almacenamiento adicional de una tabla del mismo tamaño
que la tabla OC_DTL. Aunque es cierto que el espacio de almacenamiento en disco es
relativamente barato; esta forma de actuar dificulta la administración del sistema.
Cualquier modificación a la estructura de la tabla OC_DTL, también deberá
propagarse ala tabla X_OC_DTL. Podemos evitar el tema de la tabla mutante si
calculamos y actualizamos la OC de cada registro de la aplicación en lugar d e hacerlo
en el servidor.
Empleo de mayús. Para el desarrollo de índices
La comparación de cadenas de texto es realmente un problema de difícil
resolución.
Cuando se comparan direcciones es fácil encontrarse con distintas variedades de una
misma palabra. Por ejemplo, ‘AVDA’, ‘Avda’, ‘Avenida’, ‘AVENIDA’ y otras
variaciones adicionales tienen el mismo significado y se refieren a lo mismo, pero las
comparaciones dirán que no, salvo que previamente manipule las cadenas de texto para
mejorar la consistencia.
Utilizar un algoritmo como el que describimos anteriormente en este capitulo
tiene mucho sentido. Analizar cada cadena de texto en busca de entradas comunes y
sustituirles por un formato uniforme permite realizar búsquedas seguras, por desgracia,
las cadenas de texto pueden ser más complicadas de lo que inicialmente hemos
podido suponer.
Por ejemplo, en la dirección ‘avenida del parque de las avenidas ,120, Madrid,
28027’ nos encontramos dos veces con la palabra ‘avenida’. Deberá ser capaz de
identificar el formato uniformen sus datos de origen antes de intentar encontrar un
coincidencia para poder tener una mínima probabilidad de éxito. En otras palabras
deberá conocer que la referencia a la calle solo ocurrirá en los primeros caracteres de la
Sistema de Bases II Desarrollo Físico - Desnormalización
dirección, o alguna otra regla similar... en caso contrario, no garantizará que la
localización se realice de forma correcta.
Podrá llevar acabo esta lógica en un desencadenador en la tabla en la que se almacena
la cadena de texto. Solo interactuará con datos en el registro, por lo que no se
encontrará con el problema de la tabla mutante la siguiente asignación
:NEW.X_DIR_TXT :=UPPER(cadena _ texto)
Es la parte sencilla de la tarea. La parte complicada es la lógica de búsqueda. No hay
muchas empresas que deseen llevar adelante esta tarea.
Columnas adicionales de claves externas en el lugar al que no
pertenecen
Agregar nuevas claves externas puede, ciertamente aumentar la velocidad en la
elaboración de informes. El inconveniente es la complejidad añadida del modelo de
datos. El modelo de datos mostrado en la figura 18.1 indica una relación implícita entre
Detalle de Reclamaciones y Grupo mediante Reclamación, Póliza, Coste y Plan.
Por desgracia, la unión de varias tiende a formar cuello de botella desde el punto de
vista del rendimiento. El código ejemplo 18.2 muestra esta técnica.
CREATE TABLE GRP ( GRP_ID NUMBER (10) NOT NULL PRIMARY KEY, DESCR_TX VARCHAR2 (40) NOT NULL) /
CREATE TABLE PLAN ( PLAN_ID NUMBER (10) NOT NULL PRIMARY KEY, DESCR_TX VARCHAR2 (40) NOT NULL, GRP_ID NUMBER (10) NOT NULL REFERENCES GRP (GRP_ID)) /
CREATE TABLE COSTE ( COSTE_ID NUMBER (10) NOT NULL PRIMARY KEY, CANT NUMBER (10,2) NOT NULL, PLAN_ID NUMBER (10) NOT NULL REFERENCES PLAN (PLAN_ID)) /
CREATE TABLE POLIZA ( POLIZA_ID NUMBER (10) NOT NULL PRIMARY KEY, DESCR_TX VARCHAR2 (40) NOT NULL COSTE_ID NUMBER (10) NOT NULL REFERENCES COSTE (COSTE_ID)) /
Sistema de Bases II Desarrollo Físico - Desnormalización
CREATE TABLE RECLAM ( RECLAM_ID NUMBER (10) NOT NULL PRIMARY KEY, PACIENTE_APELL VARCHAR2 (40) NOT NULL, PACIENTE_NOMBP VARCHAR2 (40) NOT NULL, POLIZA_ID NUMBER (10) NOT NULL, REFERENCES POLIZA (PÓLIZA _ ID), CREAR _ FECHA DATE) / CREATE TABLE RECLAM_DTL ( RECLAM_ID NUMBER (10) NOT NULL, REFERENCES RECLAM (RECLAM_ID), RECLAM_DTL_ID NUMBER (3) NOT NULL, DESCR_TX VARCHAR2 (40), DIAGNOSIS_CD VARCHAR2 (10), GPR_ID NUMBER (10) NOT NULL REFERENCES GRP (GRP_ID)) /
Podemos evitar la unión de las seis tablas definiendo una clave externa desde
RECLAM_DTL a GRUPO, como demuestran las instrucciones en negrita del Código
ejemplo 18.2
Otra opción seria convertir al identificador único de cada una de estas tablas en una
clave concatenada. De esta forma, podrá informar de los Detalles de las Reclamaciones
mediante Reclamación, Póliza, Coste, Plan o Grupo, y su consulta solo tendrá que
acceder a Reclamación Detallada y, opcionalmente, a una o mas de otras tablas, si en la
consulta solicitada se comprueba que solo se podrá obtener información adicional de
una de estas tablas. El código correspondiente a esta opción se muestra en el Código
ejemplo 18.3.
CREATE TABLE GRP ( GRP_ID NUMBER (10) NOT NULL PRIMARY KEY, DESCR_TX VARCHAR2 (40) NOT NULL) /
CREATE TABLE PLAN ( PLAN_ID NUMBER (10) NOT NULL, DESCR_TX VARCHAR2 (40) NOT NULL, GRP_ID NUMBER (10) NOT NULL REFERENCES GRP (GRP_ID), PRIMARY KEY (PLAN_ID, GRP_ID)) /
CREATE TABLE COSTE ( COSTE_ID NUMBER (10) NOT NULL, CANT NUMBER (10,2) NOT NULL, PLAN_ID NUMBER (10) NOT NULL, GRP_ID NUMBER (10) NOT NULL, PRIMARY KEY (COSTE_ID, PLAN_ID, GRP_ID), FOREING KEY (PLAN_ID, GRP_ID)
Sistema de Bases II Desarrollo Físico - Desnormalización
REFERENCES PLAN (PLAN_ID, GRP_ID)) / CREATE TABLE POLIZA ( POLIZA_ID NUMBER (10) NOT NULL, DESCR_TX VARCHAR2 (40) NOT NULL, COSTE_ID NUMBER (10) NOT NULL, PLAN_ID NUMBER (10) NOT NULL, GRP_ID NUMBER (10) NOT NULL, PRIMARY KEY (PÓLIZA_ID, COSTE_ID, PLAN_ID, GRP_ID), FOREING KEY (COSTE_ID, PLAN_ID, GRP_ID) REFERENCES COSTE (COSTE_ID, PLAN_ID, GRP_ID)) /
CREATE TABLE RECLAM ( RECLAM_ID NUMBER (10) NOT NULL, PACIENTE_APELL VARCHAR2 (40) NOT NULL, PACIENTE_NOMBP VARCHAR2 (40) NOT NULL, CREAR _ FECHA DATE, POLIZA_ID NUMBER (10) NOT NULL, COSTE_ID NUMBER (10) NOT NULL, PLAN_ID NUMBER (10) NOT NULL, GRP_ID NUMBER (10) NOT NULL, PRIMARY KEY (RECLAM_ID,
PÓLIZA_ID, COSTE_ID, PLAN_ID, GRP_ID),
FOREING KEY (PÓLIZA_ID, COSTE_ID, PLAN_ID, GRP_ID)
REFERENCES POLIZA (PÓLIZA _ ID, COSTE_ID, PLAN_ID, GRP_ID)) / CREATE TABLE RECLAM_DTL ( RECLAM_ID NUMBER (10) NOT NULL, RECLAM_DTL_ID NUMBER (3) NOT NULL, DESCR_TX VARCHAR2 (40),
DIAGNOSIS_CD VARCHAR2 (10), POLIZA_ID NUMBER (10) NOT NULL, COSTE_ID NUMBER (10) NOT NULL, PLAN_ID NUMBER (10) NOT NULL, GRP_ID NUMBER (10) NOT NULL, PRIMARY KEY (RECLAM_ID, RECLAM_DTL_ID,
PÓLIZA _ ID, COSTE _ ID, PLAN_ID, GRP_ID),
FOREING KEY (RECLAM_ID, PÓLIZA _ ID,
COSTE _ ID,
Sistema de Bases II Desarrollo Físico - Desnormalización
PLAN_ID, GRP_ID),
REFERENCES RECLAM (RECLAM_ID, PÓLIZA _ ID, COSTE_ID, PLAN_ID, GRP_ID)) /
Como puede ver, la concatenación de claves primarias puede hacer que sus requisitos de almacenamiento de datos se vayan por las nubes. Realizando un cuidadoso análisis, podrá determinar la ruta óptima para su proyecto.
Columnas redundantes para el histórico
Podrá desarrollar las columnas redundantes para el histórico en la misma forma que utilizamos la técnica de la desnormalización para las columnas adicionales de claves externas no necesitara información adicional.
Escritura de tablas de detalle
Esta aproximación es similar a la clave externa adicional y a las columnas redundantes para las técnicas del historial, aunque en este caso una clave externa redundante es una clave externa de la tabla maestra ( al contrario de ser una clave primaria de la tabla maestra). De nuevo, esta forma de actuar sirve para facilitar la elaboración de informes, aunque dificultaría la comprensión del modelo.
Violaciones de la primera forma normal Violar la primera forma normal implica que esta codificando una regla del sistema que su empresa suele tener almacenada en la estructura de la base de datos. Una decisión tal como esta no deberá tomarse a la ligera. El código ejemplo 18.4 indica que la actual estrategia presupuestaria de esta empresa se lleva a cabo por trimestres. Pero ¿que ocurrirá si esta división temporal cambia en el futuro? Una modificación de esta magnitud exigiría cambios sustanciales en la base de datos, en todas las aplicaciones (por ejemplo, formularios e informes) y en cualquier interfaz que intercambia información con esta base de datos. Estos inconvenientes son lo suficientemente costosos y no se pueden tomar a la ligera.
CREATE TABLE PRESUP ( PRESUP_ID NUMBER (10) NOT NULL, DESCR_TXT VARCHAR2 (100) NOT NULL,
CUENT_CD VARCHAR2 (20) NOT NULL,/CREATE TABLE PRESUP_DTL (
PRESUP_ID NUMBER (10) NOT NULL, TRM1_CANT NUMBER (10, 2) NOT NULL, TRM2_CANT NUMBER (10, 2) NOT NULL, TRM3_CANT NUMBER (10, 2) NOT NULL, TRM4_CANT NUMBER (10, 2) NOT NULL,
Codigo ejemplo 18.4
Sistema de Bases II Desarrollo Físico - Desnormalización
Por el contrario podemos llevar a cabo un modelo mas flexible que pueda acomodar con
el tiempo las modificaciones que tengan lugar sobre la estructura del presupuesto. Esta
aproximación exigirá nuevas estructuras de datos que utilizaremos para definir la
estructura presupuestaria.
Columnas sobrecargadas
Para desarrollar el ejemplo de la subdivisión geográfica tendremos que definir dos
tablas, PAÍS y ST_PROV, tal y como se muestra en el código ejemplo 18.5.
CREATE TABLE PAÍS (PAÍS_ID NUMBER (10) NOT NULL PRIMARY KEY,DESCR_TXT VARCHAR2 (50) NOT NULL)/CREATE TABLE ST_PROV (PAÍS_ID NUMBER (10) NOT NULL
REFERENCES PAÍS (PAÍS_ID),ST_PROV_ID NUMBER (10) NOT NULL,DESCR_TXT VARCHAR2 (50) NOT NULL,ESTADO_YN VARCHAR2 (1) NOT NULL)/
Código ejemplo 18.5
La columna DESCR_TXT perteneciente a la tabla DT_PROV almacena los nombres de
estados y provincias. En principio, no está claro que un valor determinado contenido en
la columna DESCR_TXT de la tabla ST_PROV sea un estado o una provincia si no se
consulta también el contenido de la columna ESTADO_YN, que indica si un
determinado registro se ha definido como ESTADO O PROVINCIA.
Columnas Multiatributos
Un lugar muy normal en donde ocurre este tipo de desnormalización es en el caso de los
identificadores de inventario. En ocasiones, el identificador único de un elemento se
encuentra almacenado en una única columna, pero, en realidad, puede descomponerse
en varios componentes, tales como el número de almacén, tipo de elemento y número de
elemento. En lugar de introducir estos valores de datos distintos en una única columna,
recomendamos descomponerlos de manera individual. Ciertamente, esta forma de
actuar simplifica la lectura del modelo y los valores se podrán seguir mostrando juntos
Sistema de Bases II Desarrollo Físico - Desnormalización
para satisfacer las preferencias del usuario, tal y como se muestra en el código ejemplo
18.6.
CREATE TABLE ARTIC (ARTIC_ID NUMBER (10) NOT NULL, PRIMARY KEY,DESCR_TX VARCHAR2 NOT NULL)/INSERT INTO ARTIC (ARTIC_ID, DESCR_TX)
VALUES (‘0113561111’, ‘WIDGET’)/
Código ejemplo 18.6
El código ejemplo 18.6 utiliza la columna ARTIC_ID para almacenar una cadena
combinada que contiene el tipo del elemento (por ejemplo, ‘011’ – caracteres 1-3), el
identificador del elemento (por ejemplo, ‘1111’ –caracteres 7-10).
Esta forma de actuar presenta un par de problemas. En primer lugar, los usuarios de este
sistema deben saber lo que significa ‘011’, porque el sistema no almacena una
descripción para este código. En segundo lugar, consultar las ventas de artículos por
tipo de artículo es una tarea que no puede ser indexada, porque tendrá que analizar el
tipo empleo de la función SUBSTR, que, de manera inherente, ignora los índices. En
tercer lugar, ¿qué ocurriría si su elemento fuera reclasificado como algún otro tipo de
artículo o fuera almacenado en algún otro almacén en el futuro? Estos cambios exigirían
la actualización de la clave primaria, un extremo bastante inconveniente.
La forma más correcta de trabajar será normalizar cuando vea este tipo de escenarios y
esté convencido de que su estructura puede cambiar y, probablemente pueda, cambiar
en el futuro. Las estructuras mostradas en el Código ejemplo 18.7 serían seguras.
CREATE TABLE ALAC (ALMAC_CD VARCHAR2 (4) NOT NULL PRIMARY KEY,DESCR_TX VARCHAR (50) NOT NULL)/
CREATE TABLE ARTIC_TIPO (ARTIC_TIPO_CD VARCHAR2 (3) NOT NULL PRIMARY KEY,DESCR_TX VARCHAR2 (50))/CREATE TABLE ARTIC (ARTIC_CD VARCHAR2 (3) NOT NULL,DESCR_TX VARCHAR2 (50) NOT NULL,ALMAC_CD VARCHAR2 (4) NOT NULL
REFERENCES ALCAM (ALMAC_CD),
Sistema de Bases II Desarrollo Físico - Desnormalización
ARTIC_TIPO_CD VARCHAR2 (3) NOT NULLREFENBRENCES ARTIC_TIPO (ARTIC_TIPO_CD)
/Código ejemplo 18.7
Este ejemplo, identificaría de manera única los artículos utilizando el código de artículo,
no la combinación de código de artículo, código de almacén y código de tipo de
artículo. De esta forma, se permitiría que el tipo de artículos o el almacén cambien con
el tiempo. Si está convencido que estos valores no van a cambiar con el tiempo, podrá
seguir utilizando este diseño y , simplemente, modificar la clave primaria de la tabla
ARTIC para incluir los códigos de almacén y de tipo de artículo. De esta forma,
obtendrá el texto descriptivo de los tipos de almacenes y artículos, y garantizará que la
combinación de estos tres campos representa un único artículo.
Naturalmente, existen datos que nunca necesitarán este tipo de descomposición. El
típico ejemplo son los códigos postales. Muchos de nosotros no somos concientes de
que, en la mayoría de los países, el código postal esta realmente formado por varios
códigos de menor extensión. En realidad, la mayoría de nosotros nunca nos hemos
preocupado por este hecho. Lo que realmente interesa en la mayoría de los sistemas es
el código postal completo; por tanto, no requiere una normalización como sucedía en el
ejemplo del artículo que hemos analizado anteriormente.
Conclusión:
Cada uno de los sistemas de desnormalización que hemos comentado aquí, con la
excepción de la primera forma normal (que no se ha recomendado), comienzan con un a
base de datos en la tercera forma normal plenamente normalizada a la que se agrega una
columna redundante. Se trata de un concepto clave para obtener buenos modelos, ya que
ayuda a mantener un esquema conceptualmente claro. Todo lo que hemos tenido que
hacer es agregar al modelo sin sacrificar ni su flexibilidad ni su claridad conceptual.
Agregando trozos que han sido claramente identificados como desnormalizados gracias
a las convenciones de denominación utilizadas, generando una documentación
cuidadosa que indiquen de donde vienen dichos campos y plasmando estos mediante
desencadenadores de base de datos, obtendremos lo mejor de ambos mundos: Un
modelo claro y teóricamente correcto y un rendimiento adecuado.
Sistema de Bases II Desarrollo Físico - Desnormalización
¿Funcionaria siempre esta forma de trabajar? No. Para los grandes sistemas bancarios
de elevado número de transacciones, grandes sistemas comerciales al por menor,
sistemas de reservas de líneas aéreas, o cualquier otro sistema que tenga que almacenar
miles de transacciones por segundo, deberán sacrificarse ciertas correcciones teóricas
para alcanzar el rendimiento adecuado. Esta forma de proceder deberá utilizarse como
última alternativa. Los beneficios de un modelo de datos nítido son su facilidad de
mantenimiento y la facilidad con la que los nuevos grupos de diseñadores lo entenderán.
Sistema de Bases II Desarrollo Físico - Desnormalización
CONTENIDO
DESARROLLO FISICO DE LA DESNORMALIZACION 1
TECNICAS DE DESNORMALICACION 1
CAMPOS TOTAL REDUNDANTE 1
EMPLEO DE MAYUSCULAS 3
COLUMNAS REDUNDANTES PARA HISTORICO 7
ESCRITURA DE TABLAS DE DETALLE 7
VIOLACIONES DE PRIMERA FORMA NORMAL 7
COLUMNAS SOBRECARGADAS 8
COLUMNAS MULTIATRIBUTOS 8