Desarrollar base de datos con históricos (Auditorías)
Lara
He diseñado una base de datos para una aplicación que gestiona seguros de
coches. Cada vez que modifico un registro, actualizo un campo llamado
FechaModificación para saber cuándo fué la última vez que modifiqué dicho
registro. Todas las tablas de la base de datos tienen este campo
FechaModificación.
El problema viene porque necesito "rediseñar" la base de datos para que me
almacene todas las modificaciones que se van a realizar sobre los datos, no
me basta con conocer la FechaModificacion, sino que necesito conocer los
datos que había en cada momento...de tal manera, que por ejemplo, un usuario
de la aplicación pueda visualizar los datos de una póliza de seguros hace 2,
3, 4 meses...
Estoy pensando cómo diseñar la base de datos para que me guarde toda esta
información y que mis datos mantengan la integridad.
Una forma podría ser duplicando cada tabla. Tabla1----Tabla1_Historico;
Tabla2---->Tabla2_Historico. Y cada vez que modifique un registro, modificar
e insertar datos en todas las tablas del histórico que tengan relación con
ésta.
Otra forma podría ser, relacionar los datos de una tabla y los datos
modificados de esa misma tabla, mediante un árbol, en el que hay un registro
"padre" y varios registros "hijos" que dependen de él y que contendrá el
registro modificado con su FechaModificación.
Os pongo un ejemplo para ver si se entiende mejor:
Tabla: TipoPoliza
IdTipoPoliza IdPadre Descripcion FechaModificacion
1 0 Poliza1 01/09/2004
2 0 Poliza2 01/09/2004
3 1 Poliza1.1 05/09/2004
4 1 Poliza1.2 02/10/2004
......
En el ejemplo, se ve que la "Poliza1" es modificada dos veces, y como el
IdTipoPoliza es el número 1, en el IdPadre de Poliza1.1 y Poliza1.2 aparace
el 1.
De esta forma, y filtrando por la FechaModificacion, siempre puedo saber
cómo se denominaba el TipoPoliza en diferentes fechas..
Añado al ejemplo una tabla Poliza que va a tener un IdTipoPoliza
Tabla: Poliza
IdPoliza IdPadre Descripcion IdTipoPoliza
FechaModificacion
1 0 Descripcion Poliza1 1
01/09/2004
En cuanto modifique el registro 1 de la tabla TipoPoliza, debería modificar
también la tabla Póliza, para guardar ese cambio, ya que la Poliza con
identificador 1, tiene como TipoPoliza el identificador 1, y como el
05/09/2004 ha sido modificado su descripción de Poliza1 a Poliza1.1, lo que
yo añadiría en la tabla Poliza sería lo siguiente:
Tabla: Poliza
IdPoliza IdPadre Descripcion IdTipoPoliza
FechaModificacion
1 0 Descripcion Poliza1 1
01/09/2004
2 1 Descripcion Poliza1 3
05/09/2004
Espero que se entienda en qué consiste esta segunda forma de guardar un
histórico de cambios..
Lo que me gustaría es que me dierais vuestra opinión, sobre si alguna de
esta formas es óptima, o bien, que me enviárais algún link con información
sobre este tema.
Muchísimas gracias! Siento no tener mucho tiempo para explicar mejor el
problema
Saludos!
Lara
qwalgrande
Te voy a dar mi opinión basada en una experiencia parecida que se me
presentó no hace mucho.
La primera de las opciones es más fácil de implementar y sobre todo más
fácil de explotar. Las estructuras en árbol, a la larga, complican la
existencia sobre manera, sobre todo para realizar consultas. Almacenar
cambios en histórico puedes hacerlo incluso con triggers, aunque yo lo haría
con procedimientos almacenados, es decir, aprovecharía los procedimientos
almacenados de alta, borrado y modificación para incluir la grabación en el
histórico. También entiendo que si desde el inicio no optaste por el uso de
procedimientos almacenados, ahora quizá te sea mucho más cómodo usar
triggers. Plantéatelo de todos modos.
Otra cosa que facilita mucho la primera opción (además de permitirte
independizar los datos que valen de los que sólo son histórico) es el
necesario mantenimiento que todo histórico precisa. Debes plantearte que cada
cierto tiempo, esas tablas de log hay que aligerarlas, llevarlas a otra bd,
hacer backup y borrar, en fin, lo que tú veas.
qwalgrande.
Lara
tabla, y utilizar procedimientos almacenados. Lo que ocurre es que hay gente
del equipo de proyecto que es muy reacia a su utilización, prefiere utilizar
métodos para estas operaciones.
En este artículo de Microsoft se habla del tema de los triggers para generar
una Auditoría:
http://www.microsoft.com/spanish/msdn/comunidad/mtj.net/voices/art168.asp
pero no estoy segura que todo lo pueda hacer con triggers, pienso que es más
seguro utilizar procedimientos y controlar yo las operaciones.
El tema del mantenimiento del histórico, también se ha planteado, que cada
año, seguramente haya que "limpiar" datos y volver a empezar el histórico.
La verdad, que todo lo que me cuentas me anima más a seguir tu experiencia.
Ahora tengo que "luchar" con el equipo para convencerles...
Muchas gracias!
Y si alguien tienen alguna opinión más es de agradecer!
"qwalgrande" <qwalgrande*nospam*@yahoo.es> escribió en el mensaje
news:8945669C-952F-43C6...@microsoft.com...
Lara
Partiendo que el histórico lo quiero realizar creando una tabla_histórico
por cada tabla, me surge el siguiente problema:
Tenemos una tabla TipoPoliza y otra Poliza. La relación entre ambas tablas
es que cada póliza es de un tipo.
Hacemos los siguientes pasos:
1.- Insertamos un tipo de póliza, tanto en la tabla TipoPoliza como en la
tabla TipoPoliza_Historico
Tabla TipoPoliza:
IdTipoPoliza Descripcion FechaModificacion
1 TipoPoliza1 01/01/2004
Tabla TipoPoliza_Historico:
IdTipoPoliza Descripcion FechaModificacion
1 TipoPoliza1 01/01/2004
2.- Insertamos una póliza, con un determinado tipo de póliza, tanto en la
tabla Poliza como en Poliza_Historico.
Tabla Poliza:
IdPoliza IdTipoPoliza Descripcion FechaModificacion
1 1 Poliza1 01/01/2004
Tabla Poliza_Historico:
IdPoliza_Historico IdTipoPoliza IdPoliza Descripcion FechaModificacion
1 1 1 Poliza1
01/01/2004
3.- Modificamos la descripción de un tipo de póliza (modificando la tabla
TipoPoliza), y para guardar este cambio insertamos un nuevo registro en
TipoPoliza_Historico.
Tabla TipoPoliza:
IdTipoPoliza Descripcion FechaModificacion
1 TipoPoliza1Modificada 25/01/2004
Tabla TipoPoliza_Historico:
IdTipoPoliza Descripcion FechaModificacion
1 TipoPoliza1 01/01/2004
1 TipoPoliza1Modificada 25/01/2004
4.- Recordemos que nuestra Póliza estaba asignada a un tipo de póliza, que
justamente es el tipo de póliza que acabamos de modificar. Qué ocurre? que
estamos perdiendo la relación de la Póliza con su tipo de póliza inicial.
Cómo podemos mantener en el histórico esta relación? Qué pasos debemos
seguir?
Muchas gracias!!
"qwalgrande" <qwalgrande*nospam*@yahoo.es> escribió en el mensaje
news:8945669C-952F-43C6...@microsoft.com...
Salvador Ramos
Personalmente también es la de las tablas históricas la solución que más me
gusta. Lo que no coincido es en cuanto a la implementación, yo la haría
mediante triggers. Además sería bastante sencilla que sería cuestión de
almacenar el contenido de la "tabla virtual" deleted que tienes disponible
cuando se dispara el trigger. Además tienes la ventaja que si alguien
modifica cualquier dato desde fuera de tu aplicación, también se registraría
esta información en tu histórico.
--
Un saludo
Salvador Ramos
Murcia - España
--------------------------------------
[Microsoft MVP SQL Server]
www.helpdna.net
--------------------------------------
¿Te interesa participar en las reuniones
del grupo de Usuarios de SQL-Server y .NET
Se harán en levante de España, (Alicante o Murcia)?
"Lara" <lecha...@gpm.es> escribió en el mensaje
news:O4X9iMak...@TK2MSFTNGP14.phx.gbl...
Maxi
--
Salu2
-----------------------------------------------------------
Maxi
Buenos Aires - Argentina
Desarrollador Microsoft 3 Estrellas .NET
-----------------------------------------------------------
Nunca consideres el estudio como una obligación sino como
una oportunidad para penetrar en el bello y maravillosos
mundo del saber.
- Albert Einstein
"Salvador Ramos" <webmaster...@YEstoHelpdna.net> escribió en el
mensaje news:Oauesua...@TK2MSFTNGP11.phx.gbl...
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.748 / Virus Database: 500 - Release Date: 01/09/2004
Javier Loria
Mi opinion difiere un poco de los otros companeros del grupo, y creo que
depende un poco de los requerimientos. Si la tabla de auditoria/historico se
hace con fines forenses (tener una bitacora de eventos interesantes de
seguridad, despues de la aplicacion ha sido comprometida), creo que tiene
sentido las sugerencias de tablas historicas. Principalmente por "faciles",
pero tambien porque los usuarios son diferentes (Usuarios
Normales/Auditores), el volumen de datos es diferente, etc. Entonces aun
cuando se puede considerar que son la misma entidad (Polizas/Polizas
Historicas), creo hay razones para "desnormalizar". Pero si la aplicacion
debe brindar servicio de historicos a los usuarios "normales", no estoy tan
seguro.
Una alternativa de diseno es usar un patron de tablas como el siguiente:
========================================================
CREATE TABLE PolizasHistoricas(
IdTipoPoliza INT NOT NULL
, Descripcion VARCHAR(30) NOT NULL
, FechaInicioVigencia DATETIME NOT NULL
, FechaFinalVigencia DATETIME NULL
, CONSTRAINT PK_PolizasHistoricas
PRIMARY KEY (IdTipoPoliza, FechaInicioVigencia)
, CONSTRAINT CH_PolizasFechasVigencia
CHECK(FechaInicioVigencia<FechaFinalVigencia OR FechaFinalVigencia IS
NULL)
)
INSERT PolizasHistoricas(IdTipoPoliza, Descripcion, FechaInicioVigencia,
FechaFinalVigencia)
SELECT 1, 'Poliza1' , '2004/01/09', '2004/05/09' UNION ALL
SELECT 2, 'Poliza2' , '2004/01/09', '2004/02/10' UNION ALL
SELECT 1, 'Poliza1.1', '2004/05/09', NULL UNION ALL
SELECT 2, 'Poliza2.1', '2004/02/10', NULL
CREATE VIEW PolizasVigentes
AS
SELECT IdTipoPoliza, Descripcion
FROM PolizasHistoricas
WHERE FechaFinalVigencia IS NULL
========================================================
La tabla de polizas historicas tiene los datos actuales y las
"versiones" anteriores. Las versiones anteriores tienen una FechaFinal de
Vigencia, las actuales tienen el valor en Null. Los usuarios o las partes de
la aplicacion que no que NO necesitan el Historico, usan la Vista de Polizas
Vigentes que filtra y esconde las columnas de auditoria.
Cuando insertas una fila que modifica una poliza existente, asignas a la
fila existente un valor de FechaFinalVigencia y creas la nueva fila con la
nueva fecha. Esto lo puedes hacer de 2 formas en un procedimiento almacenado
o en un trigger instead of (SQL Server 2000) sobre la tabla PolizasVigentes.
Espero que te guste este diseno.
Saludos,
Javier Loria
Costa Rica
Se aprecia la inclusion de DDL (CREATE, INSERTS, etc.)
que pueda ser copiado y pegado al Query Analizer.
La version de SQL y Service Pack tambien ayuda
"Lara" <lecha...@gpm.es> wrote in message
news:enOlKyZk...@TK2MSFTNGP11.phx.gbl...
qwalgrande
No sé si he entendido bien lo que quieres plantear, ya que en lo que
comentas sólo veo un problema conceptual, a nivel de análisis, y no de
estructura.
Como con TipoPoliza1 y TipoPoliza1Modificada no lo veo claro, te cambio el
ejemplo: "TipoPoliza1" será "Seguro de Vida".
Si la modificación consiste en pasar de "Seguro de Vida" a "Póliza Vida", es
decir, no hay cambio de concepto, entonces tu tabla Polizas está
perfectamente historificada. Si quieres saber la descripción que tenía
"Póliza Vida" en el momento en que se grabó, ahí tienes el histórico de la
tabla TipoPoliza (consultando el registro más reciente que sea anterior a la
fecha de alta de la póliza 1).
Si la modificación consiste en pasar de "Seguro de Vida" a "Seguro de
Coche", es decir, hay cambio de concepto, entonces lo que yo haría sería
crear una nueva entrada en la tabla TipoPoliza para "Seguro de Coche", anular
o desactivar "Seguro de Vida" (ojo, no puedes borrar, recuerda la integridad
del histórico) y después modificar todos los registros de la tabla Polizas
que tengan que pasar de ser "Seguro de Vida" a ser "Seguro de Coche". Todo
ello quedará grabado en tu histórico.
Por cierto, haciendo referencia también a lo comentado por Salvador Ramos,
si es posible que las tablas sean modificadas desde fuera de la aplicación o
más genéricamente, desde puntos que no estén controlados, o si vas a tener
que hacer estos cambios en bloque, también soy de la opinión de que es mejor
que uses triggers y además te grabes también quién ha hecho los cambios (si
puedes, te guardas pelos y señales, si no al menos puedes utilizar las
funcion User_Id(), User_Name() y sistem_user para guardarte el usuario de
base de datos que hizo el cambio), sobre todo pensando en tu salud, ya que
será a ti a quien le preguntarán (por lo general muy cabreados) quién ha
podido hacer éste o aquel cambio.
qwalgrande
Antonio Ortiz
esto es al registro de la table agregue:
Activo int
IDAnt int
FechaModificacion Datetime
De tal manera que puedes tener una vista con solo los registros 'Activos' y
cuando se desee hacer cambios, desactivas el registro actual y creas un
registro duplicado con el campo Activo=Verdadero, ademas el nuevo registro
almacena el ID del anterior.
De esta manera siempre podras consultar las modificaciones y Estatus en X
fecha o periodo.
Saludos,
Antonio Ortiz Ramirez
asesor en sistemas
ant(a)aortiz.net
www.aortiz.net
www.progvisual.com
"Lara" <lecha...@gpm.es> escribió en el mensaje
news:enOlKyZk...@TK2MSFTNGP11.phx.gbl...
Lara
Muchas gracias por vuestra ayuda.
La idea es tener el histórico para una Auditoría, y sólo será consultada por
un usuario Administrador. Así que al final creo que voy a crear tablas para
el histórico y utilizaré triggers. No se si más adelante me encontraré con
algún problema, y necesitaré utilizar algún procedimiento almacenado... pero
de momento, estoy haciendo pruebas con triggers, y parece que va bien la
cosa...
La solución que me plantea Javier, es como tener una árbol no? Está bien
pensada, pero no nos interesa tener todos los datos en una única base de
datos debido a que hay muchísmas transacciones y me encontraría con Vistas
sobre tablas muy grandes.
Una pregunta para todo el grupo: ¿Es posible realizar la Auditoría haciendo
uso de las copias de seguridad transaccionales? Es decir,... de la misma
forma que yo puedo recuperar una base de datos, en un momento determinado,
se podría automatizar este proceso para que me generara esa base de datos??
Claro.. estas bases de datos se eliminarían nada más terminar la consulta, y
cada vez que un Administrador quisiera ver los datos en una fecha
determinada, se tendría que recuperar la base de datos para dicha fecha...
Estoy liándome? hay alguna idea parecida que pueda llevarse a cabo?
Muchísimas gracias!
Lara
"Javier Loria" <nos...@nospam.com> escribió en el mensaje
news:%23WwoZ0b...@TK2MSFTNGP12.phx.gbl...