Cómo controlar una versión de un registro en una base de datos

Digamos que tengo un registro en la base de datos y que tanto el administrador como los usuarios normales pueden hacer actualizaciones.

¿Alguien puede sugerir un buen enfoque / architecture de cómo controlar la versión de cada cambio en esta tabla para que sea posible retrotraer un registro a una revisión anterior?

Supongamos que tiene una tabla FOO que los administradores y los usuarios pueden actualizar. La mayoría de las veces puede escribir consultas en la tabla FOO. Días felices.

Entonces, crearía una tabla FOO_HISTORY . Esto tiene todas las columnas de la tabla FOO . La clave principal es la misma que FOO más una columna RevisionNumber. Hay una clave externa de FOO_HISTORY a FOO . También puede agregar columnas relacionadas con la revisión, como UserId y RevisionDate. *_HISTORY los *_HISTORY Revisión de manera creciente en todas las tablas *_HISTORY (es decir, desde una secuencia de Oracle o equivalente). No confíe en que solo haya un cambio en un segundo (es decir, no coloque RevisionDate en la clave principal).

Ahora, cada vez que actualiza FOO , justo antes de hacer la actualización, inserta los valores anteriores en FOO_HISTORY . Hace esto en un nivel fundamental en su diseño para que los progtwigdores no puedan perderse accidentalmente este paso.

Si desea eliminar una fila de FOO , tiene algunas opciones. Ya sea en cascada y elimine todo el historial, o realice una eliminación lógica marcando FOO como eliminado.

Esta solución es buena cuando usted está muy interesado en los valores actuales y solo ocasionalmente en la historia. Si siempre necesita el historial, puede poner fechas de inicio y finalización efectivas y mantener todos los registros en FOO . Cada consulta necesita verificar esas fechas.

Creo que está buscando versionar el contenido de los registros de la base de datos (como lo hace StackOverflow cuando alguien edita una pregunta / respuesta). Un buen punto de partida podría ser buscar algún modelo de base de datos que use el seguimiento de revisiones .

El mejor ejemplo que me viene a la mente es MediaWiki, el motor de Wikipedia. Compare el diagtwig de la base de datos aquí , particularmente la tabla de revisión .

Dependiendo de las tecnologías que esté utilizando, tendrá que encontrar algunos buenos algoritmos de diferencias / fusión.

Verifique esta pregunta si es para .NET.

En el mundo de BI, puede lograr esto agregando un startDate y endDate a la tabla que desea versionar. Cuando inserta el primer registro en la tabla, startDate se rellena, pero endDate es nulo. Cuando inserta el segundo registro, también actualiza el endDate del primer registro con el startDate del segundo registro.

Cuando desee ver el registro actual, seleccione el que endDate sea nulo.

Esto a veces se llama una Dimensión que Cambia Lentamente tipo 2. Ver también TupleVersioning

Actualice a SQL 2008.

Intente utilizar SQL Change Tracking, en SQL 2008. En lugar de marcar timestamping y tombstone column hacks, puede usar esta nueva función para rastrear cambios en los datos en su base de datos.

Seguimiento de cambios de MSDN SQL 2008

Solo quería agregar que una buena solución a este problema es usar una base de datos temporal . Muchos proveedores de bases de datos ofrecen esta característica ya sea de fábrica o mediante una extensión. He utilizado con éxito la extensión de tabla temporal con PostgreSQL pero otros también lo tienen. Cada vez que actualice un registro en la base de datos, la base de datos también conservará la versión anterior de ese registro.

Dos opciones:

  1. Tener una tabla de historial: inserte los datos antiguos en esta tabla de historial siempre que se actualice el original.
  2. Tabla de auditoría: almacena los valores de antes y después, solo para las columnas modificadas en una tabla de auditoría junto con otra información como quién actualizó y cuándo.

No dices qué base de datos, y no la veo en las tags de publicación. Si es para Oracle, puedo recomendar el enfoque que está incorporado en Designer: use las tablas de diario . Si es para cualquier otra base de datos, bueno, básicamente te recomiendo lo mismo, también …

La forma en que funciona, en caso de que desee replicarlo en otro DB, o tal vez si solo quiere entenderlo, es que para una tabla también se crea una tabla oculta, solo una tabla de base de datos normal, con las mismas especificaciones de campo , además de algunos campos adicionales: como última acción tomada (cadena, valores típicos “INS” para inserción, “UPD” para actualización y “DEL” para eliminación), fecha y hora para cuando tuvo lugar la acción, e identificación de usuario para quién lo hizo eso.

A través de los factores desencadenantes, cada acción en cualquier fila de la tabla inserta una nueva fila en la tabla de diario con los nuevos valores, qué acción se tomó, cuándo y por qué usuario. Nunca borres ninguna fila (al menos no en los últimos meses). Sí, crecerá grande, fácilmente millones de filas, pero puede rastrear fácilmente el valor de cualquier registro en cualquier momento desde que comenzó el diario o hasta que se borraron por última vez las filas del diario anterior, y quién hizo el último cambio.

En Oracle todo lo que necesita se genera automáticamente como código SQL, todo lo que tiene que hacer es comstackrlo / ejecutarlo; y viene con una aplicación CRUD básica (en realidad solo “R”) para inspeccionarla.

Puede realizar auditorías en una tabla SQL a través de activadores de SQL. Desde un disparador puede acceder a 2 tablas especiales ( insertadas y eliminadas ). Estas tablas contienen las filas exactas que se insertaron o borraron cada vez que se actualiza la tabla. En el SQL de activación, puede tomar estas filas modificadas e insertarlas en la tabla de auditoría. Este enfoque significa que su auditoría es transparente para el progtwigdor; no requieren ningún esfuerzo de ellos o ningún conocimiento de implementación.

La ventaja añadida de este enfoque es que la auditoría se realizará independientemente de si la operación sql se realizó a través de sus archivos DLL de acceso a datos o mediante una consulta SQL manual; (ya que la auditoría se realiza en el servidor mismo).

También estoy haciendo lo mismo. Estoy haciendo una base de datos para los planes de lecciones. Estos planes necesitan flexibilidad de versión de cambio atómico. En otras palabras, cada cambio, sin importar cuán pequeño sea, a los planes de las lecciones debe permitirse, pero la versión anterior también debe mantenerse intacta. De esta forma, los creadores de las lecciones pueden editar los planes de lecciones mientras los estudiantes los usan.

La forma en que funcionaría es que una vez que un estudiante ha hecho una lección, sus resultados se adjuntan a la versión que completaron. Si se realiza un cambio, el resultado siempre apuntará a su versión.

De esta forma, si se elimina o mueve un criterio de lección, sus resultados no cambiarán.

La forma en que estoy haciendo esto es manejando todos los datos en una tabla. Normalmente solo tendría un campo de identificación, pero con este sistema, estoy usando un id y un sub_id. El sub_id siempre se queda con la fila, a través de actualizaciones y eliminaciones. La identificación se incrementa automáticamente. El software del plan de lecciones se vinculará con el sub_id más nuevo. Los resultados del estudiante se vincularán a la identificación. También incluí una marca de tiempo para realizar un seguimiento cuando ocurrieron cambios, pero no es necesario manejar el control de versiones.

Algo que podría cambiar, una vez que lo haya probado, es que podría usar la idea nula endDate antes mencionada. En mi sistema, para encontrar la versión más nueva, tendría que encontrar el máximo (id). El otro sistema solo busca endDate = null. No estoy seguro si los beneficios externos tienen otro campo de fecha.

Mis dos centavos.

Mientras @WW. La respuesta es una buena respuesta. Otra forma es crear una columna de versión y mantener todas sus versiones en la misma tabla.

Para un acercamiento de una tabla usted:

  • Use una bandera para indicar el último ala Word Press
  • O haz una desagradable outer join mayor que la versión.

Un ejemplo de SQL del método de outer join que usa números de revisión es:

 SELECT tc.* FROM text_content tc LEFT OUTER JOIN text_content mc ON tc.path = mc.path AND mc.revision > tc.revision WHERE mc.revision is NULL AND tc.path = '/stuff' -- path in this case is our natural id. 

La mala noticia es que lo anterior requiere una outer join externa y las uniones externas pueden ser lentas. La buena noticia es que crear nuevas entradas es teóricamente más económico porque puede hacerlo en una operación de escritura sin transacciones (suponiendo que su base de datos sea atómica).

Un ejemplo de hacer una nueva revisión para '/stuff' podría ser:

 INSERT INTO text_content (id, path, data, revision, revision_comment, enabled, create_time, update_time) ( SELECT (md5(random()::text)) -- {id} , tc.path , 'NEW' -- {data} , (tc.revision + 1) , 'UPDATE' -- {comment} , 't' -- {enabled} , tc.create_time , now() FROM text_content tc LEFT OUTER JOIN text_content mc ON tc.path = mc.path AND mc.revision > tc.revision WHERE mc.revision is NULL AND tc.path = '/stuff' -- {path} ) 

Insertamos usando los datos antiguos. Esto es particularmente útil si, por ejemplo, solo desea actualizar una columna y evitar un locking y / o transacciones optimistas.

El enfoque de indicador y el método de tabla de historial requieren la inserción / actualización de dos filas.

La otra ventaja con el enfoque del número de revisión de la outer join es que siempre se puede refactorizar al enfoque de tabla múltiple más adelante con desencadenadores, ya que su desencadenador debería esencialmente hacer algo como lo anterior.

Alok sugirió la Audit table anterior, me gustaría explicarla en mi publicación.

Adopté este diseño de tabla única sin esquema en mi proyecto.

Esquema:

  • id – INTEGER INCREMENTO AUTOMÁTICO
  • nombre de usuario – STRING
  • nombre de tabla – STRING
  • oldvalue – TEXT / JSON
  • nuevo valor – TEXTO / JSON
  • createdon – DATETIME

Esta tabla puede contener registros históricos de cada tabla en un solo lugar, con el historial de objetos completo en un registro. Esta tabla puede llenarse utilizando desencadenantes / ganchos donde los datos cambian, almacenando una instantánea de valor viejo y nuevo de la fila de destino.

Pros con este diseño:

  • Menos cantidad de tablas para administrar para la administración del historial.
  • Almacena una instantánea completa de cada fila de estado antiguo y nuevo.
  • Fácil de buscar en cada mesa.
  • Puede crear partición por tabla.
  • Puede definir la política de retención de datos por tabla.

Contras con este diseño:

  • El tamaño de los datos puede ser grande, si el sistema tiene cambios frecuentes.