sql - ¿Cómo diseñar una base de datos con el historial de revisiones?
database database-design (2)
Soy parte de un equipo que construye un nuevo Sistema de gestión de contenido para nuestro sitio público. Estoy tratando de encontrar la forma más fácil y mejor de incorporar un mecanismo de Control de Revisión. El modelo de objetos es bastante básico. Tenemos una clase abstracta de "Artículo base" que incluye propiedades para datos / metadatos independientes de la versión, como "Título" y "Creado por". Varias clases heredan de esto, como "DocumentArticle", que tiene la propiedad "URL" que será una ruta de acceso a un archivo. "WebArticle" también se hereda de "BaseArticle" e incluye la propiedad "AdditionalInfo" y una colección de objetos "Tabs", que incluyen "Cuerpo" que contendrá el HTML que se mostrará (los objetos Tab no se derivan de nada). "NewsArticle" y "JobArticle" heredan de "WebArticle". Tenemos otras clases derivadas, pero estas proporcionan suficiente de un ejemplo.
Proponemos dos enfoques de la persistencia para el Control de Revisión. Los llamo "Enfoque1" y "Enfoque2". He usado SQL Server para hacer un diagrama básico de cada uno:
Con Approach1, el plan sería que las nuevas versiones de los artículos se conserven a través de una actualización de la base de datos. Se establecería un desencadenante para las actualizaciones e insertaría los datos antiguos también en la tabla xxx_Versions. Creo que habría que configurar un disparador en cada tabla. Este enfoque tiene la ventaja de que la única versión "principal" de cada artículo se encuentra en las tablas principales, y las versiones antiguas están desactivadas. Esto facilita la copia de las versiones principales de los artículos de la base de datos de desarrollo / puesta en escena al Live.
Con Approach2, el plan sería insertar nuevas versiones de los artículos en la base de datos. La versión principal de los artículos se identificaría a través de vistas. Esto parece tener la ventaja de tener menos tablas y menos código (por ejemplo, no activadores).
Tenga en cuenta que con ambos enfoques, el plan sería llamar a un procedimiento almacenado de Upsert para la tabla asignada al objeto relevante (debemos recordar manejar el caso de que se agregue un nuevo Artículo). Este procedimiento almacenado de upsert lo llamaría para la clase de la que deriva, por ejemplo, upsert_NewsArticle llamaría a upsert_WebArticle, etc.
Estamos utilizando SQL Server 2005, aunque creo que esta pregunta es independiente del tipo de base de datos. He realizado un gran seguimiento de Internet y he encontrado referencias a ambos enfoques. Pero no he encontrado nada que compare los dos y muestre que uno u otro son mejores. Creo que con todos los libros de bases de datos del mundo, esta elección de enfoques debe haber surgido antes.
Mi pregunta es: ¿cuál de estos enfoques es mejor y por qué?
En general, la mayor ventaja de las tablas de historial / auditoría es el rendimiento:
cualquier dato activo / activo consultado puede consultarse desde una tabla principal mucho más pequeña
Cualquier consulta "solo en vivo" no necesita contener una marca activa / última (o god forbid hace una subconsulta relacionada en la marca de tiempo para encontrar la última fila), simplificando el código tanto para los desarrolladores como para el optimizador de motor DB.
Sin embargo, para un CMS pequeño con 100s o 1000s de filas (y no millones de filas), el aumento de rendimiento sería bastante pequeño.
Como tal, para CMS pequeño, el Método 3 sería mejor, en cuanto a diseño más sencillo / menos código / menos piezas móviles.
El Enfoque 3 es CASI como el Enfoque 2, excepto que cada tabla que necesita un historial / control de versiones tiene una columna explícita que contiene una columna de bandera verdadera / falsa "activa" (también conocida como en vivo - también conocida como más reciente).
Sus actualizaciones son responsables de administrar correctamente esa columna al insertar una nueva versión en vivo (o eliminar una versión actual en vivo) de una fila.
Todas sus consultas de selección "en vivo" fuera de UPSERT serían triviales de enmendar, agregando " AND mytable.live = 1 " a cualquier consulta.
También, es de esperar que sea obvio, pero CUALQUIER índice en cualquier tabla debería comenzar con la columna "activa" a menos que se justifique lo contrario.
Este enfoque combina la simplicidad del Enfoque 2 (sin tablas / activadores adicionales), con el desempeño del Enfoque 1 (no es necesario realizar subconsultas relacionadas en ninguna tabla para encontrar la fila más reciente / actual; sus actualizaciones lo gestionan a través del indicador activo)
Ninguno.
CMS es complicado, y puede ser un dolor real tratar con archivos de base de datos bloqueados cuando se corta la conexión a Internet. Ya que está utilizando MSSQL, solo puede descargar y usar los programas GPL de Joomla!, MediaWiki o Magnolia y guardar los dolores de cabeza de su empresa cuando decida irse.
Dicho esto, algo parecido a Approch2 es cómo normalmente veo implementados los sistemas CMS.