database - Tablas de auditoría: cada campo para la tabla o una tabla

Question

Todo está bien en mi proyecto, excepto en los campos de auditoría. Solo insertar y actualizar está siendo auditado en nuestro universo imaginario.

Propuse una tabla similar a los siguientes ejemplos:

• ¿El mejor diseño para una tabla de base de datos de registro de cambios / auditoría?

• ¿Sugerencias para implementar tablas de auditoría en SQL Server? Solo nombre de tabla, columna de tabla, usuario, acción y fecha.

Pero mi equipo no pensó lo mismo, pusieron una columna en cada tabla para rastrear una actualización o insertar el tiempo. Y cuando pregunté por qué? Me dijeron que esa es la forma en que mantienen la pista en su trabajo.

Al final me rindo y pongo cada campo en cada mesa. Dado que todo el equipo excepto yo, me dijo que pusiera esos campos.

Ejemplo:

Su enfoque

Table Customer
+-------------+-------------+-----+--------------------------------+-------------+
| Name        | LastName    | ... | LastModification (Audit Field) | User        |
+-------------+-------------+-----+--------------------------------+-------------+
| varchar(30) | varchar(50) | ... | datetime                       | varchar(30) |
+-------------+-------------+-----+--------------------------------+-------------+

Mi acercamiento

Table Customer
+-------------+-------------+-----+
| Name        | LastName    | ... |
+-------------+-------------+-----+
| varchar(30) | varchar(50) | ... |
+-------------+-------------+-----+

Table Audit
+-----------+------------+--------+------+-------------+
| TableName | TableField | Action | User | DateAndTime |
+-----------+------------+--------+------+-------------+

Así que la pregunta es:

¿Cuál es un mejor diseño, una tabla que guarda el historial de transacciones o un campo para cada tabla? (Pros y contras)

Answer 1

¿Cuál es un mejor diseño, una tabla que guarda el historial de transacciones o un campo para cada tabla? (Pros y contras)

En lugar de centrarse en las 2 opciones, aquí hay una respuesta sobre los 4 enfoques con los que he trabajado a lo largo de los años. Cada uno con sus pros y sus contras.

1. Solo tres campos.

Solo agregue tres campos (última acción, time_stamp, update_user) a cada tabla y llámelo un día.

Pros super fácil. Se desempeña bien

Contras No puede informar sobre los datos que no tiene, por lo que esta estructura no le dice casi nada (excepto los borrados)

2. Tabla de clonar

Cada tabla tiene una copia más los tres campos de auditoría y cada vez que un usuario cambia un registro, la tabla de auditoría se inserta.

Pros realiza bastante bien. Fácil de crear un historial fila por fila que el usuario puede explorar.

Contras

• Cada cambio a la tabla base necesita un cambio correspondiente a la tabla de auditoría.
• Si los usuarios no quieren que se hojeen un historial fila por fila y quieren un informe de lo que cambió exactamente, puede resultar desagradable en una furia. Consulte las respuestas en ¿Cómo puedo escribir una consulta para extraer cambios individuales de las instantáneas de datos?

3. Tabla de historial solamente

No hay una tabla base solo una tabla de historial. Básicamente, es lo mismo que Clone Table, excepto que ahora siempre debe obtener el registro actual.

Pros de 2 pero todo es un inserto. Menos mantenimiento entonces la opción 2.

Contras Terminarás perdiendo la ganancia de mantenimiento porque terminarás manteniendo vistas o esparcirás la lógica de obtener el registro de la corriente por todas partes

4. Tabla genérica de auditoría.

Esta tabla tiene cuatro columnas (Table *, Column_name, old_value, new_value) y los tres campos de auditoría.

Pros Fácil de configurar y mantener.

Contras

• Es poco intuitivo, pero ocupa mucho espacio porque los campos old_value y old_value deben ser nvarchar(max) o su equivalente para que pueda aceptar cualquier cosa que esté en su tabla base.

• Se desempeña mal en las lecturas y escrituras.

• Es un dolor configurar un informe histórico fila a fila

• Si hay algún tipo de flujo de trabajo en los registros, los informes de auditoría pueden volverse no triviales. Por ejemplo, obtiene el requisito de que los usuarios solo quieran ver los cambios que se producen después de que el estado de los registros sea "aprobado". Eso es difícil incluso en las opciones 2 y 3, pero se convierte en un desastre en el enfoque de auditoría genérica.

Resumen

Prefiero # 2 el enfoque de la tabla de Clones ya que parece funcionar mejor para mí. He tenido problemas con el número 1 de ser insuficiente y el # 4 puede ser una pesadilla grave que requiere mucho trabajo para deshacer.