php - Almacene todos los cambios de datos con todos los detalles(como Stackoverflow)

mysql revision-history

Question

Tengo un sistema escrito usando Codeigniter y como una base de datos usando MySQL. El sistema tiene usuarios, grupos de usuarios con diferentes privilegios y etc. Tiene muchas tablas mysql que tienen muchas o muchas relaciones.

Algunas de las mesas que tengo:

• artículos
• contratos
• clientes
• productos
• Características del producto
• pedidos
• order_features
• order_products
• etc ...

Actualmente estoy registrando todos los cambios en los datos de estas tablas que realizaron los usuarios. Los usuarios pueden cambiar estos datos debido a su privilegio. Almacenar cambios de registros solo de forma simple como

A user changed product features with id of A8767
B user added new customer with id 56
C user edited content of orderlist
A user added new product (id: A8767) to order (id: or67)
...

Quiero mantener todos los cambios realizados con cada detalle, como editar el historial de la pregunta Stackoverflow. Puedo pensar en el diseño log_table para mantener todos los cambios de datos de varias tablas. ¿Hay alguna manera, tutorial, motor, plugin para hacer eso? Solo puedo pensar en hacer un duplicado de cada tabla y seguir almacenando cambios en ellas, pero no creo que sea una buena manera.

Answer 1

¿Qué hay de usar la tabla de actualización general de Uni? Los campos de la tabla deben contener estos valores:

user,event,date,table,field,new value

• usuario - quien hizo el cambio
• evento - como código de eventos predefinidos (actualizar, guardar, insertar)
• fecha - cuando se hizo el cambio
• tabla y campo - se puede localizar automáticamente desde una consulta global
• valor - valor insertado

Los valores y la inserción se pueden crear con alguna función de la consulta general.

Answer 2

He estado pensando en eso por un tiempo y solo puedo pensar en dos maneras de hacer esto. Ambos pueden funcionar completamente transparentes cuando se crean en una capa / modelo de datos abstractos.

Por cierto, hay una implementación para datos de tabla "versionables" en la doctrina del asignador ORM. Vea este ejemplo en sus documentos . Tal vez eso se ajuste a tus necesidades, pero no se ajusta a las mías. Parece que se eliminan todos los datos del historial cuando se elimina el registro original, lo que hace que no sea realmente una revisión segura.

Opción A: tener una copia de cada tabla para contener los datos de revisión

Digamos que tienes una tabla de contactos simple:

CREATE TABLE contact (
    id INT NOT NULL auto_increment,
    name VARCHAR(255),
    firstname VARCHAR(255),
    lastname VARCHAR(255),
    PRIMARY KEY (id)
)

Deberías crear una copia de esa tabla y agregar datos de revisión:

CREATE TABLE contact_revisions (
    id INT NOT NULL,
    name VARCHAR(255),
    firstname VARCHAR(255),
    lastname VARCHAR(255),
    revision_id INT auto_increment,
    type ENUM(''INSERT'', ''UPDATE'', ''DELETE'') NOT NULL,
    change_time DEFAULT current_timestamp,
    PRIMARY KEY(revision_id)
)

Lleve un registro de INSERT y UPDATE utilizando los desencadenadores AFTER . En cada nueva revisión de datos en el original, inserte una copia de los nuevos datos en la tabla de revisión y configure el type modificación correctamente.

¡Para registrar un DELETE a DELETE revisiones, también debe insertar una nueva fila en la tabla de historial! Para esto, debe usar un disparador BEFORE DELETE y almacenar los últimos valores antes de que se eliminen. De lo contrario, también tendrá que eliminar todas las restricciones NOT NULL de la tabla de historial.

Algunas notas importantes sobre esta implementación.

• Para la tabla de historial, debe eliminar cada UNIQUE KEY (aquí: la PRIMARY KEY ) de la tabla de revisión porque tendrá la misma clave varias veces para cada revisión de datos.
• Cuando ALTER el esquema y los datos en la tabla original a través de una actualización (por ejemplo, una actualización de software), debe asegurarse de que los mismos datos o correcciones de esquema se apliquen a la tabla de historial y sus datos, también. De lo contrario, tendrá problemas al volver a una revisión anterior de un conjunto de registros.
• En una implementación del mundo real, querría saber qué usuario modificó los datos. Para que ese registro de usuario sea seguro a nivel de revisión, nunca debe eliminarse de la tabla de usuarios. Solo debes configurar la cuenta deshabilitada con una bandera.
• Por lo general, una sola acción de usuario involucra más de una tabla. En una implementación del mundo real, también tendría que hacer un seguimiento de qué cambios en varias tablas pertenecen a una transacción de un solo usuario y también en qué orden. En un caso de uso real, desearía revertir todos los cambios de una sola transacción juntos, en orden inverso. Eso requeriría una tabla de revisión adicional que realice un seguimiento de los usuarios y las transacciones y mantiene una relación flexible con todas esas revisiones individuales en las tablas de historial.

Beneficios:

• Completamente en la base de datos, independiente del código de la aplicación. (bueno, no cuando el seguimiento de las transacciones de los usuarios es importante, eso requeriría un poco de lógica fuera del alcance de la consulta única)
• Todos los datos están en su formato original, sin conversiones de tipo implícitas.
• Buen desempeño en la búsqueda en las revisiones.
• fácil retroceso. Simplemente haga una simple INSERT .. ON DUPLICATE KEY UPDATE .. en la tabla original, utilizando los datos de la revisión que desea revertir.

Méritos:

• Difícil de implementar manualmente.
• Difícil (pero no imposible) de automatizar cuando se trata de migraciones de base de datos / actualizaciones de aplicaciones.

Como ya se dijo anteriormente, las doctrinas versionable hacen algo similar.

Opción B: tener una tabla central de registro de cambios

Prefacio: mala práctica, que se muestra solo para ilustrar la alternativa.

Este enfoque depende en gran medida de la lógica de la aplicación, que debería estar oculta en una capa / modelo de datos.

Tienes una tabla de historial central que realiza un seguimiento de

• Quién lo hizo
• cuando
• modificar, insertar o eliminar
• que datos
• En qué campo
• de qué mesa

Al igual que en el otro enfoque, es posible que también desee realizar un seguimiento de qué cambios de datos individuales pertenecen a una acción / transacción de un solo usuario y en qué orden.

Beneficios:

• no es necesario mantenerse sincronizado con la tabla original al agregar campos a una tabla o al crear una nueva tabla. se escala de forma transparente.

Méritos:

• mala práctica usando un valor simple = almacén de claves en la base de datos
• rendimiento de búsqueda deficiente, debido a las conversiones de tipos implícitas
• puede ralentizar el rendimiento general de la aplicación / base de datos, cuando la tabla de historial central se convierte en un cuello de botella debido a los bloqueos de escritura (esto solo se aplica a motores específicos con bloqueos de tabla, es decir, MyISAM)
• Es mucho más difícil implementar rollbacks
• posibles errores de conversión de datos / pérdida de precisión debido a la conversión de tipo implícita
• no hace un seguimiento de los cambios cuando accede directamente a la base de datos en algún lugar de su código en lugar de usar su capa de datos / modelo y olvida que en este caso debe escribir manualmente en el registro de revisiones. Puede ser un gran problema cuando se trabaja en equipo con otros programadores.

Conclusión:

• La opción B puede ser muy útil para aplicaciones pequeñas como una simple "introducción" cuando es solo para cambios de registro.
• Si desea retroceder en el tiempo y poder comparar fácilmente las diferencias entre la revisión histórica 123 y la revisión 125 y / o revertir a los datos antiguos, la opción A es el camino más difícil.