with sirve qué para nos cuando creamos control database design database-design versioning

database - sirve - Base de datos: control de versiones de datos



sql database version control (4)

He leído algunas preguntas sobre SO (como esta ) en cuanto a la versión de sus datos dentro de una base de datos.

Me gustaron algunas de las sugerencias que se mencionaron. Durante mucho tiempo he deseado (necesitado) revisar muchas de mis tablas, pero nunca lo hice. Siendo un programador con solo un simple trabajo de base de datos a mis espaldas, me preguntaba cómo uno realmente podría hacer esto.

No estoy pidiendo la solución real en sintaxis SQL. Eventualmente puedo resolverlo por mí mismo (o publicar SO cuando llegue el momento). Solo estoy pidiendo a las personas que comenten cómo lo harían y cualquier posible problema de rendimiento que pudiera existir si tuviera que "revisar" cientos de millones de registros. O cualquier otra sugerencia, siempre que se base en el ejemplo a continuación.

Dado un simple ejemplo:

Person ------------------------------------------------ ID UINT NOT NULL, PersonID UINT NOT NULL, Name VARCHAR(200) NOT NULL, DOB DATE NOT NULL, Email VARCHAR(100) NOT NULL Audit ------------------------------------------------ ID UINT NOT NULL, UserID UINT NOT NULL, -- Who TableName VARCHAR(50) NOT NULL, -- What OldRecID UINT NOT NULL, -- Where NewRecID UINT NOT NULL, AffectedOn DATE NOT NULL, -- When Comment VARCHAR(500) NOT NULL -- Why

No estoy seguro de cómo se vincularía la tabla de auditoría con otras tablas (como Persona) si TableName es una cadena.

Además, suponiendo que tengo tres GUI para poblar:

  1. Un registro completo para una identificación de persona específica
  2. Una vista de tabla que enumera todas las personas (por id)
  3. Una vista que muestra a cada persona con su información de revisión debajo de cada entrada (número de revisiones por persona, fechas de revisiones, comentarios de revisión, etc.) ordenadas por las revisiones más recientes.

Para lograr 1 y 2, ¿sería mejor consultar la tabla Person o la tabla Audit?

Para lograr 3, ¿un experto en bases de datos simplemente obtendría todos los registros y los pasaría al software para su procesamiento, o agruparía por ID de persona y fecha afectada? ¿Esto generalmente se maneja en una consulta o muchas?

¿Qué hay de crear la tabla de forma normal, tener una columna ModifiedDate en cada registro (y ModifiedBy si lo desea) y hacer todo su acceso a datos a través de una vista materializada que agrupa los datos por Id y luego tiene un HAVING ModifiedDate = MAX (ModifiedDate )?

De esta forma, agregar un nuevo registro con el mismo ID que otro eliminará el registro anterior de la vista. Si desea consultar el historial, no pase por la vista

Siempre he considerado que mantener diferentes tablas con las mismas columnas es complejo y propenso a errores.

Después de la publicación de DJ sobre el uso de una tabla de historial por tabla base y un comentario de Karl sobre posibles problemas de rendimiento, hice un poco de investigación SQL para encontrar la manera más rápida posible de transferir un registro de una tabla a otra.

Solo quería documentar lo que encontré:

Pensé que tendría que hacer una búsqueda de SQL para cargar el registro desde la tabla base, seguí con un comando SQL para colocar el registro en la tabla de historial, seguido de una actualización de la tabla base para insertar los datos modificados. Total de 3 transacciones.

Pero para mi sorpresa, me di cuenta de que puede hacer las dos primeras transacciones usando una declaración SQL usando la sintaxis SELECT INTO. Apuesto a que el rendimiento sería cien veces más rápido al hacer esto.

Entonces eso nos dejaría simplemente ACTUALIZAR el registro con los nuevos datos dentro de la tabla base.

Todavía no he encontrado una declaración SQL para hacer las 3 transacciones a la vez (lo dudo).

He realizado varios esquemas de auditoría a lo largo de los años y actualmente voy a implementar algo como esto:

Person ------------------------------------------------ ID UINT NOT NULL, PersonID UINT NOT NULL, Name VARCHAR(200) NOT NULL, DOB DATE NOT NULL, Email VARCHAR(100) NOT NULL Person_History ------------------------------------------------ ID UINT NOT NULL, PersonID UINT NOT NULL, Name VARCHAR(200) NOT NULL, DOB DATE NOT NULL, Email VARCHAR(100) NOT NULL AuditID UINT NOT NULL Audit ------------------------------------------------ ID UINT NOT NULL, UserID UINT NOT NULL, -- Who AffectedOn DATE NOT NULL, -- When Comment VARCHAR(500) NOT NULL -- Why

Los registros actuales están siempre en la tabla Person. Si hay un cambio, se crea un registro de auditoría y el anterior se copia en la tabla Person_History (tenga en cuenta que el ID no cambia y puede haber múltiples versiones)

El ID de auditoría se encuentra en las tablas * _History, por lo que puede vincular varios cambios de registro a un registro de auditoría si lo desea.

EDITAR:
Si no tiene una tabla de historial separada para cada tabla base y desea utilizar la misma tabla para guardar los registros antiguos y "eliminados", debe marcar los registros con un indicador de estado. El problema con eso es un verdadero dolor al consultar los registros actuales. Créeme que ya lo hice.

Me gusta su tabla de auditoría, es un buen comienzo. Tienes un problema de cardinalidad en tu tabla de auditoría, así que lo analizaría como dos tablas:

Person ------------------------------------------------ ID UINT NOT NULL, PersonID UINT NOT NULL, Name VARCHAR(200) NOT NULL, DOB DATE NOT NULL, Email VARCHAR(100) NOT NULL, AuditID UINT NOT NULL Audit ------------------------------------------------ ID UINT NOT NULL, TableName VARCHAR(50) NOT NULL, -- What TableKey UINT NOT NULL, CreateDate DATETIME NOT NULL DEFAULT(NOW), CreateUserID UINT NOT NULL, ChangeDate DATETIME NOT NULL DEFAULT(NOW), ChangeUserID UINT NOT NULL Audit_Item ------------------------------------------------ ID UINT NOT NULL, AuditID UINT NOT NULL, -- Which audit record UserID UINT NOT NULL, -- Who OldRecID UINT NOT NULL, -- Where NewRecID UINT NOT NULL, AffectedOn DATE NOT NULL, -- When Comment VARCHAR(500) NOT NULL -- Why

El diseño inicial propuesto tiene un único registro de auditoría que apunta a (supongo) dos registros de persona. Los desafíos de este diseño son:

  • ¿Qué registros en su tabla de personas son los registros "reales" actuales?
  • ¿Cómo se representa el historial completo de cambios en el registro de la persona? Si está apuntando a dos registros en la tabla Persona, entonces vea el punto # 1: ¿cuál es el registro actual?
  • Los campos Crear *, Cambiar * se acumulan desde una colección de registros de Audit_Item. Solo están ahí para facilitar el acceso.
  • La clave AuditID en la tabla Person le permite señalar la tabla de auditoría y acceder al historial de la persona individual sin necesidad de consultar la tabla de auditoría con la cláusula WHERE TableName=''Person''