ventajas que español desventajas caracteristicas database-design

database design - que - Cómo almacenar datos históricos



mongodb español (11)

Algunos compañeros de trabajo y yo entramos en un debate sobre la mejor manera de almacenar datos históricos. Actualmente, para algunos sistemas, utilizo una tabla separada para almacenar datos históricos, y guardo una tabla original para el registro activo actual. Entonces, digamos que tengo una tabla FOO. Debajo de mi sistema, todos los registros activos irán en FOO, y todos los registros históricos irán en FOO_Hist. El usuario puede actualizar muchos campos diferentes en FOO, por lo que quiero mantener una cuenta precisa de todo lo actualizado. FOO_Hist contiene exactamente los mismos campos que FOO, con la excepción de HIST_ID que se incrementa automáticamente. Cada vez que se actualiza FOO, realizo una declaración de inserción en FOO_Hist similar a: insert into FOO_HIST select * from FOO where id = @id .

Mi compañero de trabajo dice que esto es un mal diseño porque no debería tener una copia exacta de una tabla por razones históricas y debería simplemente insertar otro registro en la tabla activa con una bandera que indique que es para propósitos históricos.

¿Existe un estándar para tratar el almacenamiento de datos históricos? Me parece que no quiero saturar mis registros activos con todos mis registros históricos en la misma tabla, considerando que puede haber más de un millón de registros (estoy pensando a largo plazo).

¿Cómo manejan esto usted o su empresa?

Estoy usando MS SQL Server 2008, pero me gustaría mantener la respuesta genérica y arbitraria de cualquier DBMS.

Conozco esta publicación anterior, pero solo quería agregar algunos puntos. El estándar para tales problemas es lo que funciona mejor para la situación. comprender la necesidad de dicho almacenamiento y el uso potencial de los datos históricos / de auditoría / seguimiento de cambios es muy importante.

Auditoría (propósito de seguridad) : use una tabla común para todas sus tablas auditables. define la estructura para almacenar el nombre de la columna, antes del valor y después de los campos de valor.

Archivo / Historial : para casos como rastrear dirección anterior, número de teléfono, etc., crear una tabla separada FOO_HIST es mejor si su esquema de tabla de transacciones activa no cambia significativamente en el futuro (si su tabla de historial debe tener la misma estructura). si anticipa la normalización de la tabla, la adición / eliminación de columnas de tipo de datos, almacena sus datos históricos en formato xml. define una tabla con las siguientes columnas (ID, Fecha, Versión de esquema, Datos XML). esto manejará fácilmente los cambios de esquema. pero tienes que lidiar con xml y eso podría introducir un nivel de complicación para la recuperación de datos.

Creo que te acercas es correcto. La tabla histórica debe ser una copia de la tabla principal sin índices, asegúrese de tener también una marca de tiempo de actualización en la tabla.

Si pruebas el otro enfoque pronto, enfrentarás problemas:

  • gastos generales de mantenimiento
  • más banderas en selects
  • ralentización de consultas
  • crecimiento de tablas, índices

El soporte de datos históricos directamente dentro de un sistema operativo hará que su aplicación sea mucho más compleja de lo que sería de otra manera. En general, no recomendaría hacerlo a menos que tenga un requisito estricto para manipular versiones históricas de un registro dentro del sistema.

Si observa detenidamente, la mayoría de los requisitos para los datos históricos se clasifican en una de dos categorías:

  • Registro de auditoría: esto está mejor hecho con tablas de auditoría. Es bastante fácil escribir una herramienta que genere scripts para crear tablas de registro de auditoría y desencadenantes mediante la lectura de metadatos del diccionario de datos del sistema. Este tipo de herramienta se puede usar para actualizar el registro de auditoría en la mayoría de los sistemas. También puede utilizar este subsistema para la captura de datos modificada si desea implementar un depósito de datos (ver a continuación).

  • Informes históricos : informes sobre el estado histórico, las posiciones "como en" o el informe analítico a lo largo del tiempo. Puede ser posible cumplir con los requisitos de informes históricos simples al consultar las tablas de registro de auditoría del tipo descrito anteriormente. Si tiene requisitos más complejos, puede ser más económico implementar una plataforma de datos para los informes que tratar de integrar el historial directamente en el sistema operativo.

    Las dimensiones que cambian lentamente son, de lejos, el mecanismo más simple para rastrear y consultar el estado histórico y gran parte del seguimiento del historial se puede automatizar. Los manipuladores genéricos no son tan difíciles de escribir. En general, los informes históricos no tienen que usar datos actualizados al minuto, por lo que un mecanismo de actualización por lotes normalmente es correcto. Esto mantiene su núcleo y la arquitectura del sistema de informes relativamente simple.

Si sus requisitos se encuentran en una de estas dos categorías, probablemente sea mejor que no almacene datos históricos en su sistema operativo. La separación de la funcionalidad histórica en otro subsistema probablemente será un esfuerzo menor en general y producirá bases de datos transaccionales y de auditoría / informes que funcionan mucho mejor para su propósito previsto.

La verdadera pregunta es ¿necesita usar datos históricos y datos activos juntos para informar? De ser así, guárdelos en una tabla, particione y cree una vista para los registros activos para usar en las consultas activas. Si solo necesita mirarlos de vez en cuando (para investigar problemas legales o algo así), póngalos en una tabla separada.

No creo que haya una manera estándar particular de hacerlo, pero pensé que usaría un método posible. Trabajo en Oracle y en nuestro framework interno de aplicaciones web que utiliza XML para almacenar datos de aplicaciones.

Usamos algo llamado Master - Modelo de detalle que en su forma más simple consiste en:

Master Table, por ejemplo llamada Widgets menudo solo contiene una ID. A menudo contendrá datos que no cambiarán con el tiempo / no son históricos.

La tabla Detail / History por ejemplo llamada Widget_Details contiene al menos:

  • ID - clave principal. Detalle / ID histórico
  • MASTER_ID - por ejemplo en este caso llamado ''WIDGET_ID'', este es el FK al registro maestro
  • START_DATETIME - marca de tiempo que indica el inicio de esa fila de la base de datos
  • END_DATETIME - marca de tiempo que indica el final de esa fila de la base de datos
  • STATUS_CONTROL - columna de un solo carácter indica el estado de la fila. ''C'' indica actual, NULL o ''A'' sería histórico / archivado. Solo usamos esto porque no podemos indexar el END_DATETIME siendo NULL
  • CREATED_BY_WUA_ID: almacena el ID de la cuenta que provocó la creación de la fila
  • XMLDATA - almacena los datos reales

Entonces, esencialmente, una entidad comienza teniendo 1 fila en el maestro y 1 fila en el detalle. El detalle tiene una fecha de finalización NULL y STATUS_CONTROL de ''C''. Cuando se produce una actualización, la fila actual se actualiza para tener END_DATETIME de la hora actual y status_control se establece en NULL (o ''A'', si se prefiere). Se crea una nueva fila en la tabla de detalles, todavía vinculada al mismo maestro, con control de estado ''C'', la identificación de la persona que hace la actualización y los datos nuevos almacenados en la columna XMLDATA.

Esta es la base de nuestro modelo histórico. La lógica Crear / Actualizar se maneja en un paquete PL / SQL de Oracle, por lo que simplemente transfiere la función al ID actual, su ID de usuario y los nuevos datos XML e internamente hace toda la actualización / inserción de filas para representar eso en el modelo histórico . Los tiempos de inicio y fin indican cuándo está activa esa fila en la tabla.

El almacenamiento es barato, generalmente no eliminamos datos y preferimos mantener un registro de auditoría. Esto nos permite ver cómo se veían nuestros datos en un momento dado. Al indexar status_control = ''C'' o al usar una Vista, el desorden no es exactamente un problema. Obviamente, sus consultas deben tener en cuenta que siempre debe usar la versión actual (NULL end_datetime y status_control = ''C'') de un registro.

Otra opción es archivar los datos operativos en una base [diaria | por hora | cualquiera]. La mayoría de los motores de base de datos admiten la extracción de los datos en un archivo .

Básicamente, la idea es crear un trabajo programado de Windows o CRON que

  1. determina las tablas actuales en la base de datos operacional
  2. selecciona todos los datos de cada tabla en un archivo CSV o XML
  3. Comprime los datos exportados en un archivo ZIP, preferiblemente con la marca de tiempo de la generación en el nombre del archivo para facilitar el archivado.

Muchos motores de base de datos SQL vienen con una herramienta que se puede usar para este propósito. Por ejemplo, al utilizar MySQL en Linux, el siguiente comando se puede usar en un trabajo CRON para programar la extracción:

mysqldump --all-databases --xml --lock-tables=false -ppassword | gzip -c | cat > /media/bak/servername-$(date +%Y-%m-%d)-mysql.xml.gz

Solo quería agregar una opción que comencé a usar porque uso Azure SQL y la tabla múltiple era demasiado engorrosa para mí. Agregué un disparador de inserción / actualización / eliminación en mi mesa y luego convertí el cambio antes / después en json usando la función "FOR JSON AUTO".

SET @beforeJson = (SELECT * FROM DELETED FOR JSON AUTO) SET @afterJson = (SELECT * FROM INSERTED FOR JSON AUTO)

Eso devuelve una representación JSON para el registro antes / después del cambio. Luego almaceno esos valores en una tabla de historial con una marca de tiempo de cuando ocurrió el cambio (también guardo la ID para el registro actual de preocupación). Utilizando el proceso de serialización, puedo controlar cómo se vuelven a llenar los datos en el caso de los cambios en el esquema.

Aprendí sobre esto desde este enlace here

Usted podría simplemente particionar las tablas no?

"Tablas particionadas e estrategias de índice utilizando SQL Server 2008 Cuando una tabla de base de datos crece en tamaño a cientos de gigabytes o más, puede ser más difícil cargar nuevos datos, eliminar datos antiguos y mantener índices. Solo el tamaño de la tabla hace que estas operaciones tarden mucho más, incluso los datos que se deben cargar o eliminar pueden ser muy grandes, lo que hace que las operaciones INSERTAR y ELIMINAR no sean prácticas. El software de base de datos Microsoft SQL Server 2008 proporciona partición de tablas para que estas operaciones sean más manejables ".