sql - restricciones - Tablas de auditoría: Mantenimiento de la integridad referencial: buena o mala

restricciones de entidad base de datos

Question

Estamos planeando introducir un simple Audit Trail en nuestra base de datos usando triggers y una tabla de historial separada para cada tabla que requiera auditoría.

Por ejemplo, considere la tabla StudentScore, tiene pocas claves externas (por ej., StudentID, CourseID) vinculándola a las tablas padre correspondientes (Student & Course).

Table StudentScore (
    StudentScoreID, -- PK
    StudentID ref Student(StudentID),  -- FK to Student
    CourseID ref Course(CourseID),   -- FK to Course
)

Si StudentScore requiere auditoría, estamos planeando crear la tabla de auditoría StudentScoreHistory -

Table StudentScoreHistory (
    StudentScoreHistoryID, -- PK
    StudentScoreID,
    StudentID,
    CourseID,
    AuditActionCode,
    AuditDateTime,
    AuditActionUserID
)

Si se modifica cualquier fila en StudentScore moveremos la fila anterior a StudentScoreHistory.

Uno de los puntos planteados durante el debate sobre el diseño fue hacer que el ID del alumno y el ID de curso en la tabla de Historia del estudiante fueran FK, para mantener la integridad referencial. El argumento a favor de esto fue que como siempre hacemos un borrado suave (indicador booleano lógico) en lugar de eliminarlo, es bueno mantener la integridad referencial para garantizar que no tengamos identificadores huérfanos en la tabla de auditoría.

Table StudentScoreHistory (
    StudentScoreHistoryID, -- PK
    StudentScoreID,
    StudentID ref Student(StudentID), -- FK to Student
    CourseID ref Course(CourseID), -- FK to Course
    AuditActionCode,
    AuditDateTime,
    AuditActionUserID
)

Este parece ser un diseño un poco extraño para mí. Estoy de acuerdo con el comentario de @Jonathan Leffler de que el registro de auditoría no debe detener la eliminación de los datos principales. En cambio, si es necesario, debe manejarse mediante claves externas en la tabla principal y no en la tabla de auditoría. Quiero obtener su opinión, para asegurarme de que no me falta algo de valor en la extensión de claves externas a las tablas de auditoría.

Ahora mi pregunta es: ¿es un buen diseño tener estas claves foráneas en las tablas de Historial?

Cualquier detalle sobre los argumentos clave (ex desempeño, mejores prácticas, flexibilidad de diseño, etc.) sería muy apreciado.

Para beneficio de cualquiera que busque un propósito específico y nuestro entorno:

Propósito:

1. Mantener el historial de datos críticos
2. Permitir auditar la actividad del usuario con soporte para recrear escenario
3. Hasta cierto punto, permite la reversión de la actividad del usuario

Ambiente:

• Base de datos transaccional
• No todas las tablas requieren auditoría
• Utiliza borrado suave en la medida de lo posible, específicamente para datos estáticos / de referencia
• Pocas tablas altamente transaccionales usan eliminaciones difíciles

Answer 1

Al estar en el medio de la implementación de un sistema de auditoría muy similar por primera vez, actualmente estoy enfrentando esa misma preocupación. Mi opinión es similar a la de BiggsTRC: su tabla "en vivo" mantiene la relación FK con el registro del curso y su tabla de historial solo mantiene una relación con su contraparte "en vivo" (StudentScore). Esto, creo, logra no tener huérfanos en la mesa de auditoría.

Ahora, hay algo más que no vi mencionado en las respuestas: en nuestro proyecto actual, vimos el valor de mantener un FK en la tabla de historia en la tabla de Historia del Curso, para que sepamos cuál era el "estado" del Curso registrar al momento de la entrada de auditoría de StudentScoreHistory. Por supuesto, eso puede o no importarle, dependiendo de la lógica de su sistema.

Nuestra solución a su preocupación (en su respuesta a BiggsTRC), que podría tener el mismo CourseId varias veces, era hacer referencia no al CourseId real, sino a la columna PK de la tabla CourseHistory. Todavía no tenemos una decisión firme sobre cómo lograr esto, si queremos crear una entrada de auditoría del registro del Curso incluso si no hubo un cambio, o si intentamos introducir alguna lógica para buscar el registro de Historial del Curso que coincide con el Curso relevante. estado en el momento de la entrada de StudentScoreHistory.

Answer 2

Cuando hablaba de auditoría, volvía al propósito detrás de esto. No es realmente una copia de seguridad sino más bien un historial de lo que ha sido. Por ejemplo, para StudentScore , querrá asegurarse de no perder el hecho de que el alumno originalmente tenía un 65% cuando ahora tiene un 95%. Esta pista de auditoría le permitiría revisar los cambios para ver qué sucedió y quién lo hizo. A partir de esto, puede identificar lo que un usuario particular hizo para abusar del sistema. De alguna manera, esto podría ser un tipo de copia de seguridad, ya que podría revertir estos cambios a sus estados previos sin tener que revertir tablas completas.

Con esto en mente (si mis suposiciones sobre para qué estás usando esto son correctos), el único lugar donde querrías una relación FK / PK es entre la tabla de historial y su contraparte "en vivo". Su tabla de auditoría (historial) no debe hacer referencia a ninguna otra tabla porque ya no forma parte de ese sistema. En cambio, es simplemente un registro de lo que sucedió en una mesa. Período. La única integridad referencial que puede considerar es entre la tabla de historial y la tabla activa (por lo tanto, la posible relación FK / PK). Si permite que los registros se eliminen de la tabla activa, no incluya el FK en la tabla de historial. Entonces, la tabla de historial podría incluir registros eliminados (que es lo que desea si permite eliminaciones).

No te confundas con la integridad relacional en la base de datos principal con esta tabla de historial. Las tablas de historial son todas independientes. Solo sirven como historial de una tabla (no un conjunto de tablas).

La relación de dos tablas de historial juntas es posible e incluso las relaciones más avanzadas entre las tablas en vivo y la historia juntas (Estudiantes y Cursos tanto en vivo como en historia, por ejemplo) para que puedas manejar incluso la posibilidad de que un estudiante haya sido eliminado (estremecimiento) ya que el registro aún estaría en la tabla de historial. El único problema aquí sería si no conserva el historial de una tabla en particular, en cuyo caso elige perder esos datos (si permite las eliminaciones).

Answer 3

No crearía un segundo conjunto de tablas para las filas ''auditadas'', simplemente integre sus funciones de auditoría en su esquema de producción existente. Parece que su propósito no es hacer una copia de seguridad ni restaurar a partir de una fecha / desastre determinado, sino que debe rastrear el historial de cambios por usuario o estudiante, y esa es una función de su aplicación. Creo que sus campos adicionales están bien, simplemente no necesitan ser agregados a otro conjunto de tablas.

Un problema con los procesos de copia de seguridad y restauración son los cambios de esquema. Los esquemas tienden a cambiar con el tiempo, lo que significa que es posible que no pueda restaurarlos directamente desde una copia de seguridad. Si mantiene sus funciones de auditoría integradas en su esquema de producción, no tiene que preocuparse por interrumpir nada cuando necesite admitir funciones adicionales.

Answer 4

Si necesita volver a crear el escenario, entonces diría que sí, necesita los FK, y tenerlos creo que sería una forma más fácil de seguir los registros de detalles relacionados relevantes. Sin embargo, esto hace que las eliminaciones sean un problema, así como información que puede cambiar en las tablas de claves principales. En este caso, diría que no desea eliminar registros que tengan FK en otras tablas, sino que utilice una eliminación suave como ya indicó.

En cuanto a la información en las tablas PK cambiando, caveat emptor. Configurar FK sería una forma sencilla de obtener cierta capacidad de rastreo, pero no será perfecto. Hay intercambios. Para obtener un historial absolutamente perfecto, básicamente necesitaría crear copias de seguridad de todos los registros relacionados, cada vez que un registro de candidato de auditoría tiene algo que pasar en él. Debe averiguar el nivel de granularidad que sea apropiado e ir con él, porque un registro perfecto de eventos podría ser complicado de configurar y consumiría mucho espacio en el proceso.

Además, esto puede o no ser una opción para usted, pero consideraría una combinación de herramientas como ApexSQL Audit + ApexSQL Log en lugar de una solución de auditoría interna . Según sus necesidades, esas dos herramientas combinadas con el archivo periódico de sus registros de transacciones cubrirían lo que necesita hacer. La herramienta de auditoría puede almacenar datos en la misma base de datos o en otro lugar, y la herramienta de registro puede recuperar datos de forma selectiva. Solo un pensamiento.

Answer 5

Si solo planea hacer eliminaciones suaves como describe, entonces no veo ninguna razón por la cual no deba usar claves externas.

Answer 6

Si su sistema está realmente enfocado en el procesamiento de transacciones, entonces mi respuesta puede no serle útil, pero en el mundo del datawarehouse / BI, este problema a menudo se resuelve mediante el uso de un "esquema en estrella". En este enfoque, se desnormalizaría la información indicativa importante de las tablas vinculadas junto con sus registros de auditoría. Esto podría incluir los valores PK de las tablas padre (es decir, los valores FK en su tabla auditada). Sin embargo, no conservaría las restricciones de la integridad referencial real.

Entonces, para su ejemplo, su tabla StudentScoreHistory podría retener su columna StudentID, sin la restricción FK, así como quizás el Nombre del Estudiante (o lo que sea que crea que pueda necesitar del Estudiante). De esta forma, puede regresar a su registro de auditoría para reconstruir lo que sucedió y cuándo, sin preocuparse por si es difícil o blando borrar los registros principales. Esto tiene la ventaja adicional (o la desventaja, dependiendo de su perspectiva) de realizar un seguimiento de los atributos de la tabla principal intercambiable tal como eran cuando se grabó originalmente la grabación secundaria. Por ejemplo, podría ser útil saber que la estudiante 123456, que ahora es la señora Marriedlady, solía ser la señorita soltera cuando se le confirió su título de biología.

Answer 7

Su esquema en vivo impone la integridad relacional, por lo que no necesita claves externas en el esquema del Historial. O póngalo de otra manera: la única razón para imponer claves foráneas entre las tablas en el esquema del Historial es si hay algún mecanismo para ejecutar DML contra el esquema del Historial que no sea rellenarlo de los cambios en el esquema en vivo. En cuyo caso, su esquema de Historial es bastante inútil como una pista de auditoría.

Plantea la cuestión de las eliminaciones suaves, lo que confunde el problema. Eso solo sería relevante si está considerando tener claves foráneas entre los dos esquemas, por ejemplo, StudentScoreHistory referencias StudentScore . Ese puede ser un diseño válido, pero una vez más, sugiere que no confías en tu mecanismo de auditoría. Personalmente, preferiría tener eliminaciones difíciles en las tablas activas y registrar el hecho de eliminación en la tabla de Historial. Las eliminaciones suaves son solo otra fuente de dolor.

Pero de todos modos esta es una pregunta diferente. Es perfectamente posible tener claves externas entre las versiones en vivo y la historia de cada tabla, por ejemplo, StudentScoreHistory -> StudentScore sin que también se aplique la integridad relacional dentro del esquema del Historial, por ejemplo, StudentScoreHistory -> StudentHistory .

Answer 8

Su kilometraje obviamente variará con la situación, pero según mi experiencia, mantenga la integridad referencial con la clave principal de la tabla de origen y nada más. Esto permite evitar identificaciones huérfanas en el historial, al tiempo que permite interacciones fluidas con las tablas relacionadas.

Supongamos, por ejemplo, que tienes algo como esto:

table scores (
 score_id,
 student_id ref students (student_id),
 course_id ref courses (course_id),
 score_date,
 score,
 pkey (score_id)
)

En ese caso, tener una opción para eliminar cascadas fkey haciendo referencia a los puntajes (score_id) en score_logs tiene sentido. Es el objeto; si se elimina con dificultad, también podría descartarse el historial.

Las claves foráneas en student_id y course_id, por el contrario, tienen menos sentido en mi experiencia. Significan que no se puede hacer una eliminación (difícil) en los estudiantes y los cursos, incluso cuando no existan filas activas que los refieran. Esto podría ser lo que desea lograr, en cuyo caso ignore la sugerencia. En mi caso, me encuentro en la necesidad de eliminar usuarios, comentarios, productos, pedidos, etc. las claves externas en los registros de historial lo hacen inconveniente.

Además, tenga en cuenta que hay un caso en que los fkeys trabajan en su contra. Si tiene una línea de pedido en un pedido y la línea de pedido se borra, aún desea el historial en esa línea de pedido. El pkey correcto para usar en este caso es el order_id, no el order_line_id.

Una última nota, en caso de que termine optando por quedarse con las llaves: considere a lo que deberían estar apuntando. Con datos desacoplados (por ejemplo, estudiantes y cursos), es razonable suponer que la fila en vivo está bien. Sin embargo, con datos fuertemente acoplados (por ejemplo, productos y promociones) lo que realmente querrá es hacer referencia tanto a la clave como a su versión.

En los dos puntos anteriores, puede encontrar interesante este hilo relacionado y respuesta:

¿Cómo se crea una pista de auditoría para las raíces agregadas?

Answer 9

Sugeriría que no extienda claves externas a las tablas de auditoría. Mi recomendación es que los datos de una auditoría se amplíen a los valores de clave externa.

En lugar de almacenar CourseID como "1", será "HTML4". De esta forma, si se elimina el valor de clave externa, la tabla de auditoría sigue siendo válida. Esto también se aplicará si el valor de clave externa se cambia de "HTML4" a "HTML5" en cualquier momento en el futuro. Si solo almacenara la clave externa, le estaría diciendo al auditor que los alumnos anteriores hicieron "HTML5", lo cual no es correcto.

Otro gran beneficio es la capacidad de enviar los registros de auditoría a otro servidor para la extracción de datos sin ningún problema.

He usado la configuración anterior por un tiempo y funciona para mí.