database-design logging audit

database design - Diseño de base de datos para registro de auditoría



database-design logging (5)

Crearemos una pequeña base de datos de ejemplo para una aplicación de blog. Se requieren dos tablas:

blog : almacena una identificación de publicación única, el título, el contenido y una marca eliminada. audit : almacena un conjunto básico de cambios históricos con una ID de registro, la ID de la publicación de blog, el tipo de cambio (NUEVO, EDITAR o ELIMINAR) y la fecha / hora de ese cambio. El siguiente SQL crea el blog e indexa la columna eliminada:

CREATE TABLE `blog` ( `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `title` text, `content` text, `deleted` tinyint(1) unsigned NOT NULL DEFAULT ''0'', PRIMARY KEY (`id`), KEY `ix_deleted` (`deleted`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT=''Blog posts'';

El siguiente SQL crea la tabla de audit . Todas las columnas están indexadas y se define una clave externa para audit.blog_id que hace referencia a blog.id. Por lo tanto, cuando ELIMINAMOS físicamente una entrada de blog, también se elimina el historial de auditoría completo.

CREATE TABLE `audit` ( `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `blog_id` mediumint(8) unsigned NOT NULL, `changetype` enum(''NEW'',''EDIT'',''DELETE'') NOT NULL, `changetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `ix_blog_id` (`blog_id`), KEY `ix_changetype` (`changetype`), KEY `ix_changetime` (`changetime`), CONSTRAINT `FK_audit_blog_id` FOREIGN KEY (`blog_id`) REFERENCES `blog` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Cada vez que necesito diseñar una nueva base de datos, dedico bastante tiempo a pensar en cómo debo configurar el esquema de la base de datos para mantener un registro de auditoría de los cambios.

Aquí ya se han formulado algunas preguntas al respecto, pero no estoy de acuerdo en que haya un mejor enfoque para todos los escenarios:

También me encontré con este interesante artículo sobre Cómo mantener un registro de cambios en la base de datos que intenta enumerar los pro y los contras de cada enfoque. Está muy bien escrito y tiene información interesante, pero ha hecho que mis decisiones sean aún más difíciles.

Mi pregunta es: ¿hay alguna referencia que pueda usar, tal vez un libro o algo así como un árbol de decisiones al que pueda referirme, decidir de qué manera debo ir en función de algunas variables de entrada, como:

  • La madurez del esquema de la base de datos
  • Cómo se consultarán los registros
  • La probabilidad de que sea necesario recrear registros
  • Lo que es más importante: escribir o leer el rendimiento
  • Naturaleza de los valores que se registran (cadena, números, blobs)
  • Espacio de almacenamiento disponible

Los enfoques que conozco son:

1. Agregue columnas para la fecha y el usuario creados y modificados

Ejemplo de tabla:

  • carné de identidad
  • value_1
  • value_2
  • value_3
  • Fecha de creación
  • modifed_date
  • creado por
  • modificado por

Principales inconvenientes: Perdemos el historial de las modificaciones. No se puede deshacer después de la confirmación.

2. Insertar solo tablas

Ejemplo de tabla :

  • carné de identidad
  • value_1
  • value_2
  • value_3
  • de
  • a
  • eliminado (booleano)
  • usuario

Principales inconvenientes: ¿Cómo mantener actualizadas las claves extranjeras? Se necesita un gran espacio

3. Cree una tabla de historia separada para cada tabla

Ejemplo de tabla de historial:

  • carné de identidad
  • value_1
  • value_2
  • value_3
  • value_4
  • usuario
  • eliminado (booleano)
  • marca de tiempo

Principales inconvenientes: Necesita duplicar todas las tablas auditadas. Si el esquema cambia, será necesario migrar todos los registros también.

4. Cree una tabla de historial consolidado para todas las tablas

Ejemplo de tabla de historial:

  • nombre de la tabla
  • campo
  • usuario
  • nuevo valor
  • eliminado (booleano)
  • marca de tiempo

Principales inconvenientes: ¿Seré capaz de recrear los registros (reversión) si es necesario? La columna new_value debe ser una cadena enorme para que pueda admitir todos los tipos de columna diferentes.

Creo que no hay nada como un árbol de decisión. Dado que algunos de los pros y contras (o los requisitos) no son realmente contables. ¿Cómo se mide la madurez, por ejemplo?

Así que solo alinee los requisitos de su negocio para su registro de auditoría. Intente predecir cómo estos requisitos podrían cambiar en el futuro y genere sus requisitos técnicos. Ahora puede compararlo con los pros y contras y elegir la opción correcta / mejor.

Y tenga la seguridad, no importa cómo decida, siempre habrá alguien que piense que tomó una decisión equivocada. Sin embargo, hiciste tu tarea y justificas tu decisión.

No sé de ninguna referencia, pero estoy seguro de que alguien ha escrito algo.

Sin embargo, si el propósito es simplemente tener un registro de lo que sucedió, el uso más típico de un registro de auditoría, entonces ¿por qué no mantener todo simplemente?

timestamp username ip_address procedureName (if called from a stored procedure) database table field accesstype (insert, delete, modify) oldvalue newvalue

Es de suponer que esto es mantenido por un disparador.

Si está utilizando SQL Server 2008, probablemente debería considerar la opción Cambiar captura de datos. Esto es nuevo para 2008 y podría ahorrarle una considerable cantidad de trabajo.

Un método que utilizan algunas plataformas wiki es separar los datos de identificación y el contenido que está auditando. Agrega complejidad, pero termina con un registro de auditoría de registros completos, no solo listados de campos que fueron editados, que luego tiene que combinar para darle al usuario una idea de cómo era el registro anterior.

Entonces, por ejemplo, si tuviera una tabla llamada Oportunidades para rastrear ofertas de venta, realmente crearía dos tablas separadas:

Oportunidades
Opportunities_Content (o algo así)

La tabla de Oportunidades tendría información que usaría para identificar de manera única el registro y albergaría la clave primaria a la que haría referencia para sus relaciones de claves foráneas. La tabla de Contenidos_Oportunidades contendría todos los campos que sus usuarios pueden cambiar y para los cuales le gustaría mantener un registro de auditoría. Cada registro en la tabla de contenido incluiría su propia PK y los datos de fecha modificada por y modificada. La tabla Oportunidades incluiría una referencia a la versión actual, así como información sobre cuándo se creó originalmente el registro principal y quién lo hizo.

Aquí hay un ejemplo simple:

CREATE TABLE dbo.Page( ID int PRIMARY KEY, Name nvarchar(200) NOT NULL, CreatedByName nvarchar(100) NOT NULL, CurrentRevision int NOT NULL, CreatedDateTime datetime NOT NULL

Y los contenidos:

CREATE TABLE dbo.PageContent( PageID int NOT NULL, Revision int NOT NULL, Title nvarchar(200) NOT NULL, User nvarchar(100) NOT NULL, LastModified datetime NOT NULL, Comment nvarchar(300) NULL, Content nvarchar(max) NOT NULL, Description nvarchar(200) NULL

Probablemente convertiría el PK de la tabla de contenidos en una clave de varias columnas desde PageID y la Revisión provista Revisión era un tipo de identidad. Utilizaría la columna Revisión como FK. A continuación, extrae el registro consolidado al UNIRSE así:

SELECT * FROM Page JOIN PageContent ON CurrentRevision = Revision AND ID = PageID

Puede haber algunos errores allá arriba ... esto está fuera de mi cabeza. Sin embargo, debería darle una idea de un patrón alternativo.

Josh