lentas - Diseño de la tabla de historial de SQL

optimizar consultas lentas mysql

Question

Necesito diseñar una tabla de historial para realizar un seguimiento de los valores múltiples que se modificaron en un registro específico cuando se editó.

Ejemplo:
Al usuario se le presenta una página para editar el registro.

Título: Sr.
Nombre: Joe
Tele: 555-1234
FECHO: 1900-10-10

Si un usuario cambia alguno de estos valores, necesito hacer un seguimiento de los valores antiguos y registrar los nuevos.

Pensé en usar una tabla como esta:

Historia
---------------

carné de identidad
usuario modificado
fecha de modificación
nombre de la tabla
recordId
valor antiguo
nuevo valor

Un problema con esto es que tendrá múltiples entradas para cada edición. Estaba pensando en tener otra mesa para agruparlos pero todavía tienes el mismo problema.

También estaba pensando en mantener una copia de la fila en la tabla de historial, pero eso tampoco parece eficiente.

¿Algunas ideas?

¡Gracias!

Answer 1

Debe definir qué tipo de eficiencia le interesa: puede tener la eficiencia del espacio de almacenamiento, la eficiencia del esfuerzo requerido para registrar el historial (costo de transacción) o la eficiencia del tiempo para consultar el historial de un registro de una manera específica .

Observo que tiene un nombre de tabla en su tabla de historial propuesta, esto implica una intención de registrar el historial de más de una tabla, lo que excluiría la opción de almacenar una copia exacta del registro en su tabla de historial a menos que todas las tablas Tu seguimiento siempre tendrá la misma estructura.

Si trata las columnas por separado, es decir, si registra solo un valor de columna para cada registro histórico, deberá diseñar un tipo de datos polimórficos que sea capaz de representar con precisión cada valor de columna que encontrará.

Si la eficiencia del espacio de almacenamiento es su principal preocupación, entonces dividiría el historial en varias tablas. Esto significaría tener una nueva tabla de valores de columna vinculada tanto a una tabla de eventos de edición como a una tabla de definición de columnas. La tabla de eventos de edición registraría el usuario y la marca de tiempo, la tabla de definición de columna registraría la tabla, la columna y el tipo de datos. Como lo anotó @njk, no necesita el valor de columna anterior porque siempre puede consultar la edición anterior para obtener el valor anterior. La razón principal por la que se espera que este enfoque ahorre espacio es la suposición de que, en general, los usuarios editarán un pequeño subconjunto de los campos disponibles.

Si su principal preocupación es la eficiencia de las consultas, configuraría una tabla de historial para cada tabla que está siguiendo y agregaría un campo de marca de tiempo y usuario a cada tabla de historial. Esto también debería ser eficiente en términos de costo de transacción para una edición.

Answer 2

No es necesario registrar valores antiguos y nuevos en una tabla de historial. Solo registre el nuevo valor, autor y fecha. A continuación, puede obtener el registro más reciente para algunos user_id función de la fecha del registro. Es posible que este no sea el mejor enfoque si tratará con una gran cantidad de datos.

usuario (id, user_id, datetime, author, ...)

Data de muestra

id user_id datetime author user_title user_name user_tele ... 1 1 2012-11-05 11:05 Bob 2 1 2012-11-07 14:54 Tim 3 1 2012-11-12 10:18 Bob

Answer 3

Recomendaría que para cada tabla en la que desee realizar un seguimiento del historial, tenga una segunda tabla (es decir, tblCustomer y tblCustomer_History) con el formato idéntico, más una columna de fecha.

Cada vez que se realiza una edición, inserta el registro antiguo en la tabla de historial junto con la fecha / hora. Esto es muy fácil de hacer y requiere pequeños cambios de código (generalmente solo un disparador)

Esto tiene la ventaja de mantener sus tablas "reales" lo más pequeñas posible, pero le brinda un historial completo de todos los cambios realizados.

En última instancia, sin embargo, se reducirá a cómo desea utilizar estos datos. Si es solo para fines de auditoría, este método es simple y tiene pocos inconvenientes, excepto el espacio de disco adicional y poco o ningún impacto en su sistema principal.