database design - montar - Cómo controlar una versión de un registro en una base de datos

montar base de datos sql

Question

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 / arquitectura 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?

Answer 1

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

Answer 2

Creo que está buscando versionar el contenido de los registros de la base de datos (como lo hace 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 diagrama de la base de datos here , 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.

Answer 3

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.

Answer 4

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

Answer 5

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 bloqueo 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.

Answer 6

No dices qué base de datos, y no la veo en las etiquetas 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 compilarlo / ejecutarlo; y viene con una aplicación CRUD básica (en realidad solo "R") para inspeccionarla.

Answer 7

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 programador; 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).

Answer 8

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.

Answer 9

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.

Answer 10

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íes 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 programadores no puedan perderse este paso por accidente.

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 en sí mismo. Cada consulta necesita verificar esas fechas.