sql - tipo - Diseño de base de datos para revisiones

modificar el tipo de dato de una columna sql (16)

Tenemos un requisito en el proyecto para almacenar todas las revisiones (Historial de cambios) para las entidades en la base de datos. Actualmente tenemos 2 propuestas diseñadas para esto:

por ejemplo, para la entidad "Empleado"

Diseño 1:

-- Holds Employee Entity "Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)" -- Holds the Employee Revisions in Xml. The RevisionXML will contain -- all data of that particular EmployeeId "EmployeeHistories (EmployeeId, DateModified, RevisionXML)"

Diseño 2:

-- Holds Employee Entity "Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)" -- In this approach we have basically duplicated all the fields on Employees -- in the EmployeeHistories and storing the revision data. "EmployeeHistories (EmployeeId, RevisionId, DateModified, FirstName, LastName, DepartmentId, .., ..)"

¿Hay alguna otra forma de hacer esto?

El problema con el "Diseño 1" es que tenemos que analizar XML cada vez que necesita acceder a los datos. Esto ralentizará el proceso y también agregará algunas limitaciones, como que no podemos agregar uniones en los campos de datos de revisiones.

Y el problema con el "Diseño 2" es que tenemos que duplicar todos y cada uno de los campos en todas las entidades (Tenemos alrededor de 70 a 80 entidades para las cuales queremos mantener revisiones).

No coloque todo en una tabla con un atributo discriminador IsCurrent. Esto solo causa problemas en el futuro, requiere claves sustitutivas y todo tipo de otros problemas.
El diseño 2 tiene problemas con los cambios de esquema. Si cambia la tabla de Empleados, debe cambiar la tabla de Hechos de empleado y todos los sprocs relacionados que la acompañan. Potencialmente duplica el esfuerzo de cambio de esquema.
El diseño 1 funciona bien y si se hace correctamente no cuesta mucho en términos de un golpe de rendimiento. Puede usar un esquema xml e incluso índices para superar posibles problemas de rendimiento. Su comentario sobre el análisis del xml es válido, pero puede crear fácilmente una vista utilizando xquery, que puede incluir en las consultas y unirse a. Algo como esto...

CREATE VIEW EmployeeHistory AS , FirstName, , DepartmentId SELECT EmployeeId, RevisionXML.value(''(/employee/FirstName)[1]'', ''varchar(50)'') AS FirstName, RevisionXML.value(''(/employee/LastName)[1]'', ''varchar(100)'') AS LastName, RevisionXML.value(''(/employee/DepartmentId)[1]'', ''integer'') AS DepartmentId, FROM EmployeeHistories

Creo que la pregunta clave que debe hacerse aquí es ''¿Quién / qué va a usar la historia''?

Si va a ser principalmente para informes / historia legible por humanos, hemos implementado este esquema en el pasado ...

Cree una tabla llamada ''AuditTrail'' o algo que tenga los siguientes campos ...

[ID] [int] IDENTITY(1,1) NOT NULL, [UserID] [int] NULL, [EventDate] [datetime] NOT NULL, [TableName] [varchar](50) NOT NULL, [RecordID] [varchar](20) NOT NULL, [FieldName] [varchar](50) NULL, [OldValue] [varchar](5000) NULL, [NewValue] [varchar](5000) NULL

A continuación, puede agregar una columna ''LastUpdatedByUserID'' a todas sus tablas, que debe establecerse cada vez que realice una actualización / inserción en la tabla.

Luego puede agregar un activador a cada tabla para capturar cualquier inserción / actualización que ocurra y crea una entrada en esta tabla para cada campo que se modificó. Debido a que la tabla también se suministra con el ''LastUpdateByUserID'' para cada actualización / inserción, puede acceder a este valor en el desencadenador y usarlo cuando se agrega a la tabla de auditoría.

Usamos el campo RecordID para almacenar el valor del campo clave de la tabla que se está actualizando. Si se trata de una clave combinada, solo hacemos una concatenación de cadenas con un ''~'' entre los campos.

Estoy seguro de que este sistema puede tener inconvenientes: para bases de datos muy actualizadas, el rendimiento puede verse afectado, pero para mi aplicación web, obtenemos muchas más lecturas que escrituras y parece funcionar bastante bien. Incluso escribimos una pequeña utilidad VB.NET para escribir automáticamente los desencadenantes en función de las definiciones de la tabla.

¡Solo un pensamiento!

El artículo de History Tables en el blog Database Programmer podría ser útil: trata algunos de los puntos planteados aquí y analiza el almacenamiento de deltas.

Editar

En el ensayo de History Tables , el autor ( Kenneth Downs ) recomienda mantener una tabla de historial de al menos siete columnas:

Marca de tiempo del cambio,
Usuario que hizo el cambio,
Un token para identificar el registro que se modificó (donde el historial se mantiene por separado del estado actual),
Si el cambio fue una inserción, actualización o eliminación,
El viejo valor,
El nuevo valor,
El delta (para cambios a valores numéricos).

Las columnas que nunca cambian, o cuya historia no es necesaria, no se deben rastrear en la tabla del historial para evitar la saturación. Almacenar el delta para valores numéricos puede facilitar las consultas posteriores, aunque se puede derivar de los valores antiguos y nuevos.

La tabla de historial debe ser segura, y los usuarios que no pertenezcan al sistema no pueden insertar, actualizar o eliminar filas. Solo se debe admitir el purgado periódico para reducir el tamaño general (y si lo permite el caso de uso).

Evitar el diseño 1; no es muy útil una vez que necesite, por ejemplo, retroceder a las versiones anteriores de los registros, ya sea de forma automática o "manual" utilizando la consola de los administradores.

Realmente no veo las desventajas del Diseño 2. Creo que el segundo, la tabla de Historia debe contener todas las columnas presentes en la primera, tabla de Registros. Por ejemplo, en mysql puede crear fácilmente una tabla con la misma estructura que otra tabla ( create table X like Y ). Y, cuando esté a punto de cambiar la estructura de la tabla de Registros en su base de datos en vivo, debe usar comandos de alter table todos modos, y no hay un gran esfuerzo en ejecutar estos comandos también para su tabla de Historial.

Notas

La tabla de registros contiene solo la última revisión;
La tabla de historial contiene todas las revisiones previas de registros en la tabla Registros;
La clave principal de la tabla de historial es una clave principal de la tabla Registros con una columna RevisionId agregada;
Piense en campos auxiliares adicionales como ModifiedBy : el usuario que creó una revisión en particular. Es posible que también desee tener un campo DeletedBy para rastrear quién eliminó una revisión en particular.
Piense en lo que debe significar DateModified , ya sea que signifique dónde se creó esta revisión en particular o cuando esta revisión en particular fue reemplazada por otra. El primero requiere que el campo esté en la tabla Registros, y parece ser más intuitivo a primera vista; la segunda solución, sin embargo, parece ser más práctica para los registros eliminados (fecha en que se eliminó esta revisión en particular). Si busca la primera solución, probablemente necesite un segundo campo DateDeleted (solo si lo necesita, por supuesto). Depende de usted y de lo que realmente desea grabar.

Las operaciones en Design 2 son muy triviales:

Modificar

copie el registro de la tabla de registros a la tabla de historial, asígnele un nuevo archivo de revisión (si no está ya presente en la tabla de registros), manipule DateModified (depende de cómo lo interprete, vea las notas anteriores)
continuar con la actualización normal del registro en la tabla de Registros

Borrar

haga exactamente lo mismo que en el primer paso de la operación Modificar. Maneje DateModified / DateDeleted en consecuencia, según la interpretación que haya elegido.

Recuperar (o deshacer)

tome la revisión más alta (¿o alguna particular?) de la tabla Historial y cópiela en la tabla Registros

Lista de historial de revisión para un registro particular

seleccionar de la tabla de historial y la tabla de registros
piense qué es exactamente lo que espera de esta operación; probablemente determine qué información necesita de los campos DateModified / DateDeleted (ver notas arriba)

Si opta por el Diseño 2, todos los comandos SQL necesarios para hacerlo serán muy fáciles, ¡así como el mantenimiento! Tal vez, será mucho más fácil si usa las columnas auxiliares ( RevisionId , DateModified ) también en la tabla de Registros - para mantener ambas tablas exactamente en la misma estructura (¡excepto para las claves únicas)! Esto permitirá comandos SQL simples, que serán tolerantes a cualquier cambio en la estructura de datos:

insert into EmployeeHistory select * from Employe where ID = XX

¡No te olvides de usar las transacciones!

En cuanto a la escala , esta solución es muy eficiente, ya que no transforma ningún dato de XML hacia adelante y hacia atrás, simplemente copiando filas de tablas enteras (consultas muy simples, usando índices) ¡muy eficientes!

Hemos implementado una solución muy similar a la solución que sugiere Chris Roberts, y eso nos funciona bastante bien.

La única diferencia es que solo almacenamos el nuevo valor. El valor anterior es, después de todo, almacenado en la fila de historial anterior

Digamos que tienes una mesa con 20 columnas. De esta forma, solo tiene que almacenar la columna exacta que ha cambiado en lugar de tener que almacenar toda la fila.

Hemos tenido requisitos similares, y lo que encontramos fue que muchas veces el usuario simplemente quiere ver lo que se ha cambiado, no necesariamente revertir los cambios.

No estoy seguro de cuál es su caso de uso, pero lo que hemos hecho es crear y una tabla de auditoría que se actualiza automáticamente con cambios en una entidad comercial, incluido el nombre descriptivo de cualquier referencia y enumeración de clave externa.

Cada vez que el usuario guarda sus cambios, recargamos el objeto viejo, ejecutamos una comparación, registramos los cambios y guardamos la entidad (todos se hacen en una única transacción de base de datos en caso de que haya algún problema).

Esto parece funcionar muy bien para nuestros usuarios y nos ahorra el dolor de cabeza de tener una tabla de auditoría completamente separada con los mismos campos que nuestra entidad comercial.

La forma en que he visto esto hecho en el pasado es tener

Employees (EmployeeId, DateModified, < Employee Fields > , boolean isCurrent );

Nunca "actualiza" en esta tabla (excepto para cambiar el valor de isCurrent), simplemente inserte nuevas filas. Para cualquier EmployeeId dado, solo 1 fila puede tener isCurrent == 1.

La complejidad de mantener esto puede ocultarse mediante vistas y desencadenantes "en lugar de" (en Oracle, presumo cosas similares a otros RDBMS), incluso puede ir a vistas materializadas si las tablas son demasiado grandes y no pueden ser manejadas por índices) .

Este método está bien, pero puede terminar con algunas consultas complejas.

Personalmente, soy muy aficionado a su forma de diseñar 2, así es como lo hice en el pasado. Es simple de entender, simple de implementar y simple de mantener.

También crea muy poca sobrecarga para la base de datos y la aplicación, especialmente cuando se realizan consultas de lectura, que es lo que probablemente hará el 99% del tiempo.

También sería bastante fácil la creación automática de las tablas de historial y los desencadenadores para mantener (suponiendo que se realizaría a través de activadores).

Las revisiones de datos son un aspecto del concepto de " valid-time " de una Base de datos temporal. Se ha investigado mucho sobre esto, y han surgido muchos patrones y pautas. Escribí una larga respuesta con un montón de referencias a this pregunta para los interesados.

Parece que desea rastrear los cambios en entidades específicas a lo largo del tiempo, por ejemplo, ID 3, "bob", "123 main street", luego otro ID 3, "bob" "234 elm st", y así sucesivamente, en esencia, poder para vomitar un historial de revisión que muestra cada dirección en la que ha estado "bob".

La mejor manera de hacerlo es tener un campo "es actual" en cada registro, y (probablemente) una marca de tiempo o FK en una tabla de fecha / hora.

Las inserciones tienen que configurar el "es actual" y también el "actual" en el registro "actual". Las consultas deben especificar "es actual", a menos que desee todo el historial.

Hay otros ajustes a esto si se trata de una tabla muy grande, o se esperan un gran número de revisiones, pero este es un enfoque bastante estándar.

Qué tal si:

ID de empleado
Fecha modificada
- y / o número de revisión, dependiendo de cómo quiera seguirlo
ModifiedByUSerId
- más cualquier otra información que desee rastrear
Campos de empleados

Usted crea la clave primaria (EmployeeId, DateModified), y para obtener el (los) registro (s) "actual" (es) solo selecciona MAX (DateModified) para cada employeeid. Almacenar un IsCurrent es una muy mala idea, porque en primer lugar, se puede calcular, y en segundo lugar, es demasiado fácil para que los datos se desincronicen.

También puede hacer una vista que enumera solo los últimos registros, y la usa principalmente mientras trabaja en su aplicación. Lo bueno de este enfoque es que no tiene duplicados de datos, y no tiene que recopilar datos de dos lugares diferentes (actuales en Empleados y archivados en EmployeesHistory) para obtener todo el historial o reversión, etc. .

Ramesh, estuve involucrado en el desarrollo del sistema basado en el primer acercamiento.
Resultó que el almacenamiento de revisiones como XML está llevando a un gran crecimiento de la base de datos y ralentizando significativamente las cosas.
Mi enfoque sería tener una tabla por entidad:

Employee (Id, Name, ... , IsActive)

donde IsActive es un signo de la última versión

Si desea asociar alguna información adicional con revisiones, puede crear una tabla separada que contenga esa información y vincularla con tablas de entidad usando la relación PK / FK.

De esta forma puede almacenar todas las versiones de empleados en una sola tabla. Pros de este enfoque:

Estructura de base de datos simple
No hay conflictos ya que la tabla se convierte en solo apéndice
Puede retroceder a la versión anterior simplemente cambiando la bandera IsActive
No hay necesidad de uniones para obtener el historial de objetos

Tenga en cuenta que debe permitir que la clave principal no sea única.

Si de hecho solo necesita una pista de auditoría, me inclinaría hacia la solución de la tabla de auditoría (completa con copias desnormalizadas de la columna importante en otras tablas, por ejemplo, UserName ). Tenga en cuenta, sin embargo, que la amarga experiencia indica que una sola mesa de auditoría será un gran cuello de botella en el camino; probablemente valga la pena el esfuerzo de crear tablas de auditoría individuales para todas sus tablas auditadas.

Si necesita rastrear las versiones históricas (y / o futuras) reales, entonces la solución estándar es rastrear la misma entidad con múltiples filas usando una combinación de valores de inicio, fin y duración. Puede usar una vista para hacer que acceder a los valores actuales sea conveniente. Si este es el enfoque que toma, puede tener problemas si sus datos versionados hacen referencia a los datos mutables pero no versionados.

Si desea confiar en los datos del historial (por razones de informes) debe usar una estructura como esta:

// Holds Employee Entity "Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)" // Holds the Employee revisions in rows. "EmployeeHistories (HistoryId, EmployeeId, DateModified, OldValue, NewValue, FieldName)"

O solución global para la aplicación:

// Holds Employee Entity "Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)" // Holds all entities revisions in rows. "EntityChanges (EntityName, EntityId, DateModified, OldValue, NewValue, FieldName)"

Puede guardar sus revisiones también en XML, luego solo tiene un registro para una revisión. Esto se verá así:

Si desea hacer el primero, también puede usar XML para la tabla Empleados. La mayoría de las bases de datos más nuevas le permiten realizar consultas en campos XML, por lo que esto no siempre es un problema. Y podría ser más simple tener una forma de acceder a los datos de los empleados, independientemente de si es la última versión o una versión anterior.

Aunque probaría el segundo enfoque. Puede simplificar esto teniendo solo una tabla de Empleados con un campo DateModified. EmployeeId + DateModified sería la clave principal y puede almacenar una nueva revisión simplemente agregando una fila. De esta forma, archivar versiones anteriores y restaurar versiones de archivos también es más fácil.

Otra forma de hacerlo podría ser el modelo datavault de Dan Linstedt. Hice un proyecto para la oficina de estadísticas holandesa que utilizó este modelo y funciona bastante bien. Pero no creo que sea directamente útil para el uso diario de la base de datos. Sin embargo, puede obtener algunas ideas de la lectura de sus documentos.

Si tiene que almacenar el historial, cree una tabla oculta con el mismo esquema que la tabla que está rastreando y una columna de "Fecha de revisión" y "Tipo de revisión" (por ejemplo, "eliminar", "actualizar"). Escriba (o genere, vea a continuación) un conjunto de factores desencadenantes para rellenar la tabla de auditoría.

Es bastante sencillo crear una herramienta que lea el diccionario de datos del sistema para una tabla y genere una secuencia de comandos que cree la tabla oculta y un conjunto de activadores para completarla.

No intente utilizar XML para esto, el almacenamiento XML es mucho menos eficiente que el almacenamiento de tabla de base de datos nativo que utiliza este tipo de desencadenador.

Voy a compartir con ustedes mi diseño y es diferente de sus diseños ya que requiere una tabla por cada tipo de entidad. Encontré que la mejor manera de describir cualquier diseño de base de datos es a través de ERD, aquí está el mío:

En este ejemplo, tenemos una entidad llamada empleado . la tabla de usuario contiene los registros de tus usuarios y la entidad y la entidad_revisión son dos tablas que mantienen el historial de revisiones para todos los tipos de entidades que tendrás en tu sistema. Así es como funciona este diseño:

Los dos campos de entity_id y revision_id

Cada entidad en su sistema tendrá una identificación de entidad única. Su entidad puede pasar por revisiones pero su entity_id seguirá siendo el mismo. Debe mantener esta identificación de entidad en su tabla de empleados (como una clave externa). También debe almacenar el tipo de su entidad en la tabla de entidades (por ejemplo, ''empleado''). Ahora, en cuanto a revision_id, como muestra su nombre, realiza un seguimiento de las revisiones de tu entidad. La mejor manera que encontré para esto es usar el employee_id como su revision_id. Esto significa que tendrá identificadores de revisión duplicados para diferentes tipos de entidades, pero esto no es un placer para mí (no estoy seguro de su caso). La única nota importante que debe hacer es que la combinación de entity_id y revision_id debe ser única.

También hay un campo de estado dentro de la tabla entity_revision que indica el estado de la revisión. Puede tener uno de los tres estados: latest , obsolete o deleted (no depender de la fecha de las revisiones lo ayuda mucho a impulsar sus consultas).

Una última nota sobre revision_id, no creé una clave foránea que conectara employee_id con revision_id porque no queremos alterar la tabla entity_revision para cada tipo de entidad que podamos agregar en el futuro.

INSERCIÓN

Para cada empleado que desee insertar en la base de datos, también agregará un registro a entidad y entidad_revisión . Estos dos últimos registros le ayudarán a realizar un seguimiento de quién y cuándo se ha insertado un registro en la base de datos.

ACTUALIZAR

Cada actualización de un registro de empleado existente se implementará como dos insertos, uno en la tabla de empleados y otro en entity_revision. El segundo le ayudará a saber por quién y cuándo se ha actualizado el registro.

SUPRESIÓN

Para eliminar un empleado, se inserta un registro en entity_revision indicando la eliminación y listo.

Como puede ver en este diseño, ningún dato se altera o elimina de la base de datos y, lo que es más importante, cada tipo de entidad requiere solo una tabla. Personalmente encuentro que este diseño es realmente flexible y fácil de usar. Pero no estoy seguro de ti ya que tus necesidades podrían ser diferentes.

[ACTUALIZAR]

Habiendo admitido particiones en las nuevas versiones de MySQL, creo que mi diseño también viene con uno de los mejores desempeños también. Uno puede particionar la tabla de entity usando el campo de type while partition entity_revision usando su campo de state . Esto aumentará las consultas SELECT por mucho tiempo, mientras que mantendrá el diseño simple y limpio.