Buenas Practicas en Diseño de Base Datos

Introducción

Un DBMS no facilita diseñar correctamente una base de datos. Muchas veces debemos haber pasado por lo mismo. Nos piden diseñar la base de datos para un sistema, entonces procedemos a crear tablas y nombrarlas del modo que se nos ocurra o entendamos, pero cuando empezamos a desarrollar reportes para el sistema, puede ser dificultoso.

¿Haces eso? Pues ¡¡para!!. Recuerda que es un trabajo, y es un reflejo de tí como empleado y habla también acerca de tí como persona. Claro que a veces toma tiempo extra para entender el modelo de datos, pero ese tiempo extra pagará dividendos cuando necesites extraer datos o permitir el acceso a ellos de los usuarios.

Veamos cual sería una buena forma de llamar a nuestras bases de datos, tablas, columnas, procedimientos almacenados e incluso vistas.

Convenciones para tablas o vistas

Tipo de Tabla o Vista	Sufijo para Tabla	Descripción
Tabla de Históricos	hist	Esta tabla almacena información histórica. Típicamente es una relación de una a varios.
Tabla de Referencia	ref	Este tipo de tabla almacena nombres y descripciones. Ej. 1=Perros.
Snapshot	current	Almacena información actualizada, típicamente se utiliza con tablas históricas para almacenar el último registro para una clave externa.
Primera Instancia	orig	Almacena la primera instancia de una clave externa, es lo opuesto al Snapshot, pero contiene los mismo datos si es que sólo hay un registro de la clave externa.

Casos especiales de Vistas

Muchos DBA no dejan que los usuarios accedan directamente a las tablas, para eso utilizan vistas para las consultas contra las tablas. Es una buena práctica siempre que no haya demasiados joins.

Para una vista, podemos nombrarla con una letra "v" o "vw" al comienzo del nombre, que ayuda cuando cuando se lista las vistas fuera del Administrador Corporativo tal como Microsoft Access. Gracias a ese prefijo el usuario puede distinguir si es una vista o una tabla, y cual es el tipo de información almacenada en la tabla y que tipo de tabla es.

Notarán que no se indica utilizar un prefijo para una tabla. Esto es para establecer que siempre que un objeto no tenga prefijo es una tabla, y cuando tenga prefijo es una vista o procedimiento almacenado.

Convenciones para nombrar columnas

A veces uno tiene reglas para nombrar tablas, vistas o procedimientos almacenados. Sin embargo cuando se trata de columnas no lo tenemos. Una regla que puede ayudarnos mucho y ahorrarnos tiempo es llamar a la columna A de la tabla 1, llamarla también A en la tabla 2. Un simple movimiento que puede salvarnos de muchos problemas.

Tipo de Columna	Sufijo de Columna	Descripción
Nombre del elemento	name	Usada para describir el nombre de una clave principal
Descripción del elemento	desc	Usada para describir el nombre con más detalle
Fecha de los datos	entry_date	Usada para marcar con fecha una fila
Usuario que ingresó datos	ingresado_por	Usada para registrar que usuario o aplicación introdujo los datos
Fecha de actualización de los datos	update_date	Muestra la fecha en que se actualizaron los datos
Usuario que actualizó los datos	modificado_por	Utilizada para almacenar el usuario que modificó los datos
Clave primaria númerica	<<>>_id	Utilizada describir la clave primaria cuando la clave es un valor númerico

Procedimientos Almacenados

Una convenció que puede utilizarse es 'usr_<>, de modo que cuando se depure la aplicación se sabe que un objeto con 'usr_' es un procedimiento almacenado. Hay que incluir dentro del nombre algo que indique que es lo que hace el procedimiento almacenado. Por ejemplo si un procedimiento almacenado actualiza la categoría de un producto, se le puede llamar 'usr_ActualizarCategoria'.

Bases de Datos

Ultimamente el uso de una convención para llama a la base de datos. Similar a la convención de nombres para procedimientos almacenados, como usar el prefijo 'dev' y 'prod' para distinguir entre una base de datos de desarrollo y una de producción.

Conclusión

Las convenciones de nombres que menciono no son dificiles de seguir. Usualmente, bueno, casi siempre somos presionados en el tiempo para diseñar una base de datos. No hay que sentirse mal por eso, no es nuestra culpa. Pero hay que tomarnos el tiempo para establecer y seguir nuestras convenciones de nombres. Tres meses después, cuando revisemos el código nos agradeceremos a nosotros mismos por eso. La clave de todo esto es ser consistente. Si somos consistentes, modificar el código será mucho más fácil.

¿Ustedes tienen alguna convención de nombres?

Auditoría en Bases de Datos Sql Server

Estoy recibiendo algunas consultas a través del correo electrónico, y trato de responder lo mejor posible, a pesar que prefiero que dejen un comentario en uno de mis blogs y otra cosa es que no me apuren, los deseos de ayudar son grandes, pero no el tiempo, que siempre queda chico, este mes de Marzo ha sido muy ocupado y he descuidado mis blogs, dandome cuenta que he disminuido la cantidad de entradas, cuando mi intención era aumentarlas. Pero hoy día trataré de responder a una interesante consulta hecha por un lector, que pregunta cual sería la estructura de una tabla de auditoría para una base de datos.

La primera forma: Añadir campos a una tabla

El modo más simple de auditar una tabla es registrando cada cambio fila por fila, sin embargo también es la menos recomendable. Se basa en añadir uno o dos campos testigos que registren la fecha en que se realizen cambios y el usuario que los efectúe, sin embargo cambios en cada columna no son registrados ni auditados, sólo se registra el último usuario que haya hecho un cambio. Por ejemplo si Pilar hace un cambio en la columna "Precio" y luego Juan cambia el valor de la columna "Cantidad" sólo quedará registrado que Juan hizo un cambio. Este tipo de auditoría es algo ingenua pero que puede servir en algunos casos.

Una segunda forma: Triggers y Tablas Espejo

Sql Server tiene en los triggers (desencadenadores o disparadores) una ayuda para llevar a cabo registros de auditoría. Esta forma tiene el siguiente procedimiento:
1. Crear una tabla espejo con los mismo campos que la tabla auditada pero con campos adicionales, como el usuario que haya hecho el cambio, la fecha y la operación realizada que puede ser fila agregada, fila modificada o fila eliminada.
2.Añadir triggers INSERT, UPDATE y DELETE a la tabla origen, de modo que al efectuarse cualquiera de las operaciones indicadas, grabe el mismo registro en la tabla de auditoría, incluyendo los campos de auditoría (usuario,fecha,tipo de operacion).
Esta forma de auditoría, si bien es mejor que la anterior ya que registra cada cambio realizado y almacena un historial completo para cada fila, impone una sobrecarga de operaciones por cada fila, ya que se vuelve a registrar la misma fila con datos adicionales en caso sea insertada, actualizada o eliminada. Y a la vez aumenta el espacio a ocupar.

Manejar datos de auditoría

El almacenar datos de auditoría ocupa una gran cantidad de espacio en disco duro, y manejar esa información se hace muy complicada, especialmente si se realiza con tablas espejo. Hay que saber manejar esa información, y auditar las tablas necesarias.
Una forma de manejarla es almacenando la información de auditoría en otra base de datos. Se crea una base de datos, y preferiblemente almacenarla en otra partición u otro disco duro si la actividad es intensa. Esto aumenta la complejidad de la auditoría, ya que hay que propocionar permisos a los usuarios para grabar información en la segunda base de datos. Se podría crear una vista en la base de datos auditada que muestre los datos en la base de datos de auditoría para darle un nivel de abstracción.

Archivamiento

Generar información de auditoría es un problema para el espacio, y generar mucha data es peor aún. Hay que diseñar un plan de archivamiento de esos datos, no tenerlo es lo peor que se puede hacer.
Un plan de archivamiento podría ser simplemente separar la base de datos de auditoría y crear una nueva. La desventaja sería el extraer información de la base de datos separada. Otra sería programar un script que extraiga o inserte la información, según sea el caso, y grabarla en cintas o cd. En cualquier caso la recuperación y volcado se hace manual y complica la tarea.

Conclusión

Generar data de auditoría en una base de datos es una ventaja y a la vez un problema ya que acarrea costos en espacio de almacenamiento y tiempo en manejarla. Pero aún así es necesaria en ciertas actividades, especialmente la financiera. Como recomendación, la auditoría debería realizarse a ciertas tablas de una base de datos y no a todas, ya que complicaría aún más el proceso de administración.