Estamos planeando introducir un Sendero de Auditoría simple en nuestra base de datos utilizando disparadores y una tabla de historial separada para cada tabla que requiera auditoría.

Por ejemplo, considere la tabla StudentScore, tiene pocas claves foráneas (por ejemplo, StudentID, CourseID) que la vinculan a las tablas principales 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 una tabla de auditoría StudentScoreHistory -

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

Si se modifica alguna fila de StudentScore, trasladaremos la fila anterior a StudentScoreHistory.

Uno de los puntos planteados durante la discusión del diseño fue hacer de StudentID y CourseID en la tabla StudentHistory un FK, para mantener la integridad referencial. El argumento a favor de esto fue cuando nosotrossiempr en su mayoría realiza una eliminación suave (bandera booleana lógica) en lugar de una eliminación completa, es bueno mantener la integridad referencial para garantizar que no tengamos identificadores huérfanos en la tabla de auditorí

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@ Comentario de Jonathan Leffler ese registro de auditoría no debe detener la eliminación de datos primarios. En cambio, si esto es necesario, debe manejarse a través de 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 algún valor al extender 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 del historial?

odos los detalles sobre argumentos clave (por ejemplo, rendimiento, mejores prácticas, flexibilidad de diseño, etc.) serían muy apreciado

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

Propósito

Mantener historial de datos críticos Permitir la auditoría de la actividad del usuario con soporte para recrear el escenario Hasta cierto punto, permite revertir la actividad del usuario

Medio ambiente

Base de datos transaccional No todas las tablas requieren auditoría Utiliza soft-delete en la medida de lo posible, específicamente para datos estáticos / de referencia Algunas tablas altamente transaccionales usan eliminaciones duras