Base de datos histórica / auditable

Esta pregunta está relacionada con el esquema que se puede encontrar en una de mis otras preguntas aquí. Básicamente, en mi database, almaceno usuarios, ubicaciones, sensores, entre otras cosas. Todos estos elementos son editables en el sistema por los usuarios y pueden eliminarse.

Sin embargo, cuando se edita o borra un elemento, debo almacenar los datos anteriores; Necesito poder ver cuáles eran los datos antes del cambio.

También hay elementos no editables en la database, como "lecturas". Son más de un log realmente. Las lecturas se registran contra sensores, porque es la lectura de un sensor en particular.

Si genero un informe de lecturas, necesito poder ver cuáles eran los attributes para una location o sensor en el momento de la lectura .

Básicamente, debería ser capaz de rebuild los datos para cualquier punto en el time.

Ahora, he hecho esto antes y lo he hecho funcionar bien al agregar las siguientes columnas a cada tabla editable:

valid_from valid_to edited_by 

Si valid_to = 9999-12-31 23:59:59, ese es el logging actual. Si valid_to es igual a valid_from, se borrará el logging.

Sin embargo, nunca estuve contento con los desencadenantes que necesitaba usar para aplicar coinheritance de key externa.

Posiblemente pueda evitar los desencadenantes al usar la extensión a la database "PostgreSQL". Esto proporciona un tipo de columna llamado "período" que le permite almacenar un período de time entre dos dates, y luego le permite hacer restricciones de CHECK para evitar períodos de solapamiento. Esa podría ser una respuesta.

Me pregunto si hay otra manera.

He visto a personas mencionar tablas históricas especiales, pero realmente no me gusta la idea de mantener 2 tablas para casi cada 1 table (aunque podría ser una posibilidad).

Tal vez podría networkingucir mi implementación inicial para no molestarme en verificar la consistencia de los loggings que no son "actuales", es decir, solo molestarme en verificar las restricciones en los loggings donde el valid_to es 9999-12-31 23:59:59. Después de todo, las personas que usan tablas históricas no parecen tener controles de restricciones en esas tablas (por la misma razón, necesitaría activadores).

¿Alguien tiene alguna idea sobre esto?

PD: el título también menciona una database auditable. En el sistema anterior que mencioné, siempre existe el campo edited_by. Esto permitió rastrear todos los cambios para que siempre pudiéramos ver quién cambió un logging. No estoy seguro de la cantidad de diferencia que podría hacer.

Gracias.

Revisado 01 Ene 11

De acuerdo, entonces existe una brecha entre el lugar donde me encuentro (entrego bases de datos completamente auditables, el suyo es un requisito particular de eso) y el lugar donde se sienta: según sus preguntas y comentarios. Lo cual probablemente resolvamos en el comentario. Aquí hay una position para comenzar.

  • Para proporcionar este requisito, no hay necesidad de: desencadenantes; duplicación masiva; integridad rota; etc.

  • Este tampoco es un requisito clásico de Temporal, por lo que no es necesario tener la capacidad de "período", pero puedes hacerlo.

  • ValidFrom y ValidTo es un error de normalización: el ValidTo es información que se deriva fácilmente; ValidTo en cualquier fila está duplicado, en el ValidFrom de la siguiente fila; tiene una Anomalía de actualización (cuando actualiza una columna en una fila, también debe actualizar la otra columna en la siguiente fila); tienes que usar un valor ficticio para "actual".

    • Todo innecesario, use ValidFrom solamente y mantenga el db limpio y puro 5NF.

    • La advertencia es, si PostgreSQL no puede realizar subconsultas sin caer en un montón (ala Oracle), entonces bien, kep ValidTo.

Todos estos elementos son editables en el sistema por los usuarios y pueden eliminarse.

Bueno no. Es una database que contiene información importante; con Integridad Referencial, no un scratchpad, por lo que el usuario no puede simplemente acercarse y "borrar" algo. Contradirá el mismo requisito de los usuarios para mantener los datos históricos (en Reading, Alert, Ack; Action; Download).

  • Las eliminaciones en cascada no están permitidas. Esas funciones son casillas de verificación para types de bases de datos no MS Access. Para las bases de datos reales, las restricciones de RI impiden que los padres con hijos sean eliminados.

  • Las keys primarias no pueden (no deberían) cambiarse. P.ej. Identidad de usuario; LocationId; NetworkSlaveCode nunca cambia; recuerde, se los considera cuidadosamente Identificadores . Una característica de las PK es que son estables.

  • Puede agregar nuevos usuarios; puedes cambiar el nombre de un usuario actual ; pero no puede eliminar a un Usuario que tenga inputs en Descargar, Acuse de recibo, Acción.

Básicamente, si es editable, tiene que ser histórico (de modo que excluya lecturas y alertas).

También excluye: Descargas; Expresiones de gratitud; Comportamiento.

Y las tablas de reference: SensorType; Tipo de alerta; Tipo de acción.

Y las nuevas tablas de Historial: se insertan en, pero no se pueden actualizar ni eliminar.

El problema que encuentro con el indicador isObselete es … Digamos que si cambias la location, la key externa del sensor ahora apuntará a un logging obselete, lo que significa que tendrás que duplicar cada logging del sensor. Este problema empeora exponencialmente a medida que la jerarquía aumenta.

  • Bien, ahora entiendes que LocationId (FK) en Sensor no cambiará; no hay duplicación masiva, etc. No hay problema en primer lugar (¡y hay en ese estúpido libro!) Que empeora exponencialmente en el segundo lugar.

  • IsObsolete es inadecuado para su requerimiento. (Refiérase abajo)

  • The UpdatedDtm en cualquier fila real ( Reading , etc.) identifica la fila del Historial principal (FK al Sensor ) (su AuditedDtm ) que estaba vigente en ese momento.

  • Capacidad relacional completa; Integridad de la statement declarativa, etc.

  • Mantener el IDEF1X, concepto relacional de identificadores fuertes … Solo hay una fila principal actual (por ejemplo, location)

  • Las filas en el Historial son Imágenes de la fila actual, antes de su modificación, en el AuditedDtm indicado. La fila Actual (sin historial) muestra la última UpdatedDtm, cuando se cambió la fila.

  • AuditedDtm muestra la serie completa de UpdatedDtms para cualquier key dada; y así lo he usado para "particionar" la key real en un sentido temporal.

Todo lo que se requiere es una tabla de historial para cada tabla modificable. He proporcionado las tablas Hiistory para cuatro tablas de identificación: Ubicación; Sensor; NetworkSlave; y Usuario.

Lea esto para comprender Auditable en el sentido contable .

Modelo de datos

Enlace al model de datos del sensor con historial (la página 2 contiene las tablas del historial y el context).

Los lectores que no están familiarizados con el Estándar de modelado relacional pueden considerar útil la notación IDEF1X .

Respuesta a los comentarios

(1) Mi primer problema es el de la integridad referencel con los datos históricos, en el sentido de que no estoy seguro de que exista alguno, y si existe, no estoy seguro de cómo funciona. Por ejemplo, en SensororyHistory sería posible agregar un logging que tuviera un UpdatedDtm que indicara una date antes de que existiera la location en sí, si entiendes lo que quiero decir. Si esto es realmente un problema, no estoy seguro, haciendo cumplir que podría ser excesivo.

(Usted planteó un problema similar en la otra pregunta). Puede ser que los dbs que ha experimentado no tengan en realidad la integridad referencel establecida; que las líneas de relación estaban allí solo para la documentation; que el RI fue "implementado en el código de la aplicación" (lo que significa que no hay RI).

Esta es una database SQL estándar ISO / IEC / ANSI. Eso permite Integridad Referencial Declarativa. Cada línea de Relación se implementa como una Referencia PK :: FK, una Restricción real que se Declara. P.ej:

 CREATE TABLE Location ... CONSTRAINT UC_PK PRIMARY KEY (LocationId) ... CREATE TABLE Sensor ... CONSTRAINT UC_PK PRIMARY KEY (LocationId, SensorNo) CONSTRAINT Location_Sensor_fk FOREIGN KEY (LocationId) REEFERENCES Location(LocationId) ... CREATE TABLE SensorHistory ... CONSTRAINT UC_PK PRIMARY KEY (LocationId, SensorNo, UpdatedDtm)) CONSTRAINT Sensor_SensorHistory_fk FOREIGN KEY (LocationId, SensorNo) REEFERENCES Sensor (LocationId, SensorNo) ... 

Esas restricciones declaradas son aplicadas por el server; no a través de triggers; no en el código de la aplicación. Eso significa:

  • Un Sensor con LocationId que no existe en Location no se puede insert
  • Un LocationId en la Location que tiene filas en el Sensor no se puede eliminar
  • Un SensorHistory con un LocationId+SensorNo que no existe en Sensor no puede insertse
  • Un Sensor LocationId+SensorNo en el Sensor que tiene filas en SensorHistory no se puede eliminar.

(1.1) Todas las columnas deben tener REGLAS y restricciones de VERIFICACIÓN para restringir su range de valores. Además del hecho de que todos los INSERT / UPDATE / DELETE son programáticos, en los processs almacenados, por lo tanto, los crashs no ocurren, y las personas no caminan hacia la database y ejecutan commands contra ella (excepto SELECTS).

En general, me mantengo alejado de los factores desencadenantes. Si está utilizando procs almacenados y los permissions normales, entonces esto:

en SensororyHistory sería posible agregar un logging que tuviera un UpdatedDtm que indicara una date antes de que existiera Location, si entiendes lo que quiero decir

se previene Entonces, es insert un SensorHistory con un UpdatedDtm antes que el Sensor mismo. Pero los procs no son Reglas Declarativas. Sin embargo, si quieres estar doblemente seguro (y quiero decir doblemente, porque los INSERTS son todos a través de un command directo de procuración por parte de los usuarios), entonces seguro que debes usar un disparador. Para mí, eso es exagerado.

(2) ¿cómo señalo la eliminación? Podría simplemente agregar una bandera a la versión no histórica de la tabla, supongo.

No estoy seguro todavía. P.ej. ¿Acepta que cuando se elimina un Sensor , es final … (sí, se mantiene el historial) … y luego cuando se agrega un nuevo Sensor a la Location , tendrá un nuevo SensorNo … no hay Sensor se reemplaza lógicamente por el nuevo, con o sin un espacio en el time?

Desde el punto de vista del usuario final, a través del software, ellos deberían poder agregar, editar y eliminar sensores a voluntad sin ninguna limitación. Pero sí, una vez que se elimina, se elimina y no se puede recuperar. No hay nada que les impida volver a agregar un sensor más tarde con los mismos parameters exactos.

Y "eliminar" Locations, NetworkSlaves y Users también.

De acuerdo. Entonces el nuevo Sensor con los mismos parameters, es realmente nuevo, tiene un nuevo SensorNo y es independiente de cualquier Sensor lógico anterior. Podemos agregar un IsObsolete BOOLEAN a las cuatro tablas de identificación; ahora se identifica como adecuado. La eliminación ahora es una eliminación suave.

(2.1) Para NetworkSensor y LoggerSensor , que en realidad dependen de dos padres: están obsoletos si alguno de sus padres está obsoleto. Por lo tanto, no tiene sentido proporcionarles una columna IsObsolete , que tiene un doble significado, que puede derivarse de la matriz correspondiente.

(2.2) Para que quede claro, los usuarios no pueden eliminar ninguna fila de ninguna tabla de transactions e historial, ¿verdad?

(3) Al actualizar una tabla, ¿qué método sería el mejor para insert la nueva fila en la tabla histórica y actualizar la tabla principal? ¿Solo las sentencias SQL normales dentro de una transacción pueden?

Sí. Ese es el uso clásico de una transacción, según las properties de ACID, es atómico; o tiene éxito in toto o falla in toto (para volver a intentarlo más tarde cuando se resuelva el problema).

(4) Libro de reference

El text definitivo y seminal es Temporal Data y el Modelo Relacional CJ Date, H Darwen, NA Lorentzos. Al igual que en, aquellos de nosotros que adoptamos el RM estamos familiarizados con las extensiones, y lo que se requiere en el sucesor del RM; en lugar de algún otro método.

El libro al que se hace reference es horrible y gratis. El PDF no es un PDF (sin búsqueda, sin indexing). Abrir mi MS y Oracle es revelador; unos buenos pedazos en una gran cantidad de pelusa. Muchas tergiversaciones. No vale la pena responder en detalle (si desea una revisión adecuada, abra una nueva pregunta).

(4.1) ValidTo además de ValidFrom . Grave error (como se identifica en la parte superior de mi respuesta) que hace el libro; luego, laboriosamente resuelve. No cometas el error en primer lugar, y no tienes nada que resolver en segundo lugar. Según lo entiendo, eso eliminará tus triggers.

(4.2) Reglas simples, tomando en count los requisitos de Normalización y Temporal. Primero y ante todo, necesita comprender profundamente (a) el requisito temporal y (b) los types de datos, el uso correcto y las limitaciones. Siempre almacenar:

  • Instantáneo como DATETIME, ej. UpdatedDtm

  • Intervalo como INTEGER, identificando claramente la Unidad en el nombre de la columna, ej. IntervalSec

  • Período. Depende de la conjunción o disyunción.

    • Para el set, que es este requisito, (4.1) se aplica: use un DATETIME; el final del período se puede derivar desde el comienzo del período de la siguiente fila.
    • Para períodos de disyunción, sí, necesita 2 x DATETIME, por ejemplo, RentedFrom y un RentedTo con espacios intermedios.

(4.3) Se meten con la "key principal temporal", que complica el código (además de requerir desencadenadores para controlar la anomalía de actualización). Ya he entregado una Clave primaria temporal limpia (probada y probada).

(4.4) Se meten con valores falsos, valores no reales y nulos para "Ahora". No permito tales cosas en una database. Como no estoy almacenando el ValidTo duplicado, no tengo el problema, no hay nada que resolver.

(4.5) Uno tiene que preguntarse por qué un "libro de text" de 528 páginas está disponible gratis en la web, en un formatting PDF pobre.

(5) I [un usuario] podría silenciar felizmente borrar todas las filas de LocationHistory, por ejemplo, (dejando solo la versión actual en la tabla Location), incluso aunque exista una fila SensorHistory que conceptualmente "pertenece" a una versión anterior del Ubicación, si eso tiene sentido.

No tiene sentido para mí, todavía hay una brecha en la comunicación que tenemos que cerrar. Por favor sigue interactuando hasta que se cierre.

  • En una database real (estándar ISO / IEC / ANSI SQL), NO otorgamos permissions INSERT / UPDATE / DELETE a los usuarios. Solo GRANT SELECT y REFERENCES (para usuarios elegidos) Todos los INSERT / UPDATE / DELETE se codifican en Transacciones, lo que significa procs almacenados. Luego otorgamos EXEC en cada process almacenado a los usuarios seleccionados (use ROLES para networkingucir la administración).

    • Por lo tanto, nadie puede eliminar de ninguna tabla sin ejecutar un process.

    • No escriba un process para eliminar de ninguna tabla de Historial. Estas filas no deben ser eliminadas. En este caso, el no permiso y la inexistencia de código es la Restricción.

    • Técnicamente, todas las filas de Historial son válidas, no hay un Período del que preocuparse. La fila más antigua de LocationHistory contiene la image anterior de la fila original de Location antes de que se cambiara. La hilera LocationHistory más joven es la image anterior de la fila de location actual. Cada fila intermedia de LocationHistory es por lo tanto válida y se aplica al Período intermedio.

    • No es necesario "podar" o search algunas filas de LocationHistory que se pueden eliminar sobre la base de que se aplican a un período que no se utiliza: todos se utilizan . (Definitivamente, sin la necesidad de verificar cualquier mapeo de niños de Location a cualquier fila (s) de LocationHistory, para probarlo).

    • En pocas palabras: un usuario no puede eliminar de ninguna tabla de historial (o transacción).

    • ¿O quieres decir algo diferente otra vez?

    • Tenga en count que he agregado (1.1) arriba.

(6) Corregido un error en el DM. Una Alert es una expresión de Reading , no de Sensor .

(7) corrigió las reglas comerciales en la otra pregunta / respuesta para reflejar eso; y las nuevas reglas expuestas en esta pregunta.

(8) ¿Entiende / aprecia que, dado que tenemos un model completamente compatible con IDEF1X, identificadores re:

  • Los identificadores se llevan a través de toda la database, conservando su poder. P.ej. al enumerar Acknowledgements , se pueden unir directamente con Location y Sensor ; las tablas intermedias no necesitan ser leídas (y deben serlo si se usan keys Id ). Esta es la razón por la cual hay en los hechos less combinaciones requeridas en una Base de datos relacional (y más combinaciones requeridas en una database no normalizada).

  • los Subtypes, etc. necesitan navegarse solo cuando ese context particular es relevante.

Eliminado debido a una revisión sustancial de la respuesta. Referir nueva respuesta.

Me he encontrado con esta situación antes también. Dependiendo de la cantidad de datos que está tratando de hacer un seguimiento, puede ser desalentador. La tabla histórica funciona muy bien para facilitar el uso a veces porque puede tomar una "instantánea" del logging en la tabla de historial, luego realizar los cambios según sea necesario en la tabla de producción. Es bastante sencillo de implementar, sin embargo, dependiendo de la cantidad de datos que tenga y con qué frecuencia cambie, puede terminar con tablas históricas muy grandes.

Otra opción es registrar todos los cambios que permiten a alguien "reproducir" lo que sucedió y rastrearlo. Cada cambio se registra en una tabla o campo (según sus necesidades) que realiza un seguimiento de quién, cuándo y qué fue cambiado a lo que, por ejemplo, el 31 de diciembre de 2010, Bob cambió el estado de 'Abierto' a 'Cerrado'.

El sistema que quiera usar generalmente depende de cómo deba conservar / revisar / usar los datos más adelante. Informes automáticos, revisión por una persona, alguna combinación de los dos, etc.

Dependiendo de su presupuesto y / o entorno, puede considerar usar la function de file de flashback de Oracle.

Puede activar el "file" automático de filas en una tabla, y luego ejecutar una statement en la tabla base usando algo como

  SELECCIONAR *
 FROM important_data
 A PARTIR DE TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' DAY)

Oracle se ocupa de mantener el historial en una tabla separada (sombra). Puede hacer esto para cualquier tabla para que también pueda hacer una consulta con una combinación.