Desarrollar base de datos con históricos (Auditorías)

Hola grupo!

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,

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

de la aplicación pueda visualizar los datos de una póliza de seguros hace

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,

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

"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

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

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

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

Hola grupo!

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,

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

de la aplicación pueda visualizar los datos de una póliza de seguros hace

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,

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

"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

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

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

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

Hola Lara:
Mi opinion difiere un poco de los otros companeros del grupo, y creo

depende un poco de los requerimientos. Si la tabla de auditoria/historico

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

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

seguro.
Una alternativa de diseno es usar un patron de tablas como el

=> 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

la aplicacion que no que NO necesitan el Historico, usan la Vista de

Vigentes que filtra y esconde las columnas de auditoria.
Cuando insertas una fila que modifica una poliza existente, asignas a

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

o en un trigger instead of (SQL Server 2000) sobre la tabla

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" wrote in message
news:
> Hola grupo!
>
> He diseñado una base de datos para una aplicación que gestiona seguros

> coches. Cada vez que modifico un registro, actualizo un campo llamado
> FechaModificación para saber cuándo fué la última vez que modifiqué

> 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

> 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

2,
> 3, 4 meses...
>
> Estoy pensando cómo diseñar la base de datos para que me guarde toda

> 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

> é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

> sobre este tema.
>
> Muchísimas gracias! Siento no tener mucho tiempo para explicar mejor el
> problema
>
> Saludos!
> Lara
>
>
>
>
>
>
>
>