Base de datos: historico de filas. ¿Como?

bLaKnI #1 Jun '14 Inocente

Buenas, tengo una duda de diseño para una base de datos.

Supongamos una tabla padre, en la que sus registros, tienen registros dependientes múltiples en tablas diferentes.

Pongamos un ejemplo:

Tabla padre: Personas.
Tabla dependiente: Direcciones.
Tabla dependiente: Cuentas corrientes.
Tabla dependiente: Comunicaciones.

Así, para una persona, esta puede tener varias direcciones y cuentas corrientes por ejemplo. Típico esquema 1 a N.

Ahora añadamos el concepto de histórico.
Para cada cambio realizado en una persona, tiene que constar el estado anterior al cambio para poder mantener un histórico completo de TODAS las modificaciones.

A la tabla padre, es claro que un 1 a 1 con un Personas_histórico, se soluciona.
La Tabla de histórico replica a la de Personas y se establece una foreign key con el ID de persona desde histórico a personas.

Bien, PERO:

que sucede con las dependientes?

Si se borra una dirección de una persona, por ejemplo, debe constar en el histórico.

Eh aquí mi problema de diseño:

¿Como se mapea esto CORRECTAMENTE respetando las 3 formas normales? BFN en arriba no es necesario...

Es decir, se hacen tablas históricas gemelas de las dependientes y se las hace depender del registro estricto de estas? Esto esta mal teniendo en cuenta que se pueden borrar...
Hay que replicar TODA la estructura para hacer la copia? Es decir, crear otro registro de persona y de todas las tablas dependientes menos del registro eliminado por ejemplo?

Me parece que el histórico, mal diseñado es absolutamente redundante y muy caro a la larga... :\

eXtreM3 #2 Jun '14

Añade un campo "active" a las tablas dependientes, de manera que el estado más actual (el último que insertes) tenga valor 1 y todos los anteriores tengan valor 0. Así no replicas nada y te queda todo acumulado en las dependientes.

2 respuestas

DarkSoldier #3 Jun '14

#2 #1 o un autonumerico, para saber el orden, aunq independientemente de como lo hagas, te recomendaría timestamps xD

bornex #4 Jun '14

#1 Yo lo solucionaria con un trigger para cada tabla. Cuando se hace un AFTER UPDATE, haces un INSERT de la info OLD en una tabla nueva llamada Historico.

PD: Los logs, de toda la vida son caros y salen caros. Según como sea tu base de datos. Lo que se suele hacer es trocearlo, es decir, cada mes le haces un backup y lo dejas limpio para cada mes.

Pero ya te digo, es según si el lob que quieres guardar es necesario o no.

Soltrac #5 Jun '14 MV Wizard

Te había escrito un post esta mañana diciendo lo que proponía #2 y veo que no le di a enviar. Tb colocaría un índice sobre esa columna.

1 respuesta

eXtreM3 #6 Jun '14

#5 de todas formas no es necesario lo del active. Puedes ir guardando registros secuencialmente y a la hora de consultar el actual simplemente tiras el último (cuya id corresponda al cliente, por supuesto)

1 respuesta

Soltrac #7 Jun '14 MV Wizard

#6 Eso te limita a algo que no tiene que ser real, como es el añadir un histórico después del actual.

DarkSoldier #8 Jun '14

un campo "historial" en las tablas dependientes autonumérico, siempre selecciona el max(historico) y cuando añadas le sumas uno y listo no?

bLaKnI #9 Jun '14 Inocente

Veamos, esto lo tenia en mente, para todas las tablas.
Es decir, la continente principal de persona, duplicas registro con los cambios y en las dependientes, 3/4 de lo mismo, marcando las filas anteriores respectivas como historicas.
Se puede hacer con un "active" como decis o con lo que sea.

Pero esto lleva a que en una misma tabla, el registro esta N veces con los cambios y en su ultimo estado. Esto es redundancia de datos. Engrandece el dataset y a la larga sale mas caro. Cierto que indexando correctamente, no deberia repercutir en gran cantidad al retrieving de datos pero no deberia ser la forma, pues por eso existen las tablas gemelas, para contener las filas eliminadas o modificadas, y dejar en la tabla correcta, el dato presente/actual.

La problematica que presento pero, no la habeis abordado como queria.
El tema que me preocupa es que cuando se hace un historico de una persona, por que se le ha cambiado por ejemplo la fecha de nacimiento (pues estaba incorrecta, por ejemplo), hay que duplicar dicha fila, dejando en una tabla aparte la historica o en la misma con el "activa" que deciais. Ok.
Pero que sucede con las tablas dependientes, si estas por ejemplo contienen varios records para la misma persona? Supongamos, dos direcciones para la misma persona. Se duplican también para mantener la coherencia con el historico recien creado?

No se si me explico... :\

2 respuestas

DarkSoldier #10 Jun '14

#9 añadiendo un campo active o como lo quieras llamar a cada tabla, cuando se haga una modificación de los campos, internamente, en vez de hacer un update a esa tabla, haces un insert con los datos nuevos y sumándole 1 al campo active o histórico, no se si me explico...

lo de que se haga mas grande la tabla y demás pues si, si crees que puede ser muy grande y afectará a la larga pues será mejor hacerlo en una tabla diferente y como te han comentado, a base de triggers

persona:
id_persona: 1, nombre: oscar, edad: 26, active: 1

se hace un update, pues la tabla quedaría así:
id_persona: 1, nombre: oscar, edad: 26, active: 1
id_persona: 1, nombre: oscar, edad: 27, active: 2

bornex #11 Jun '14

#9 La verdad es que si tuvieras el diagrama de tablas con sus relaciones por ahí, sería de ayuda.

¿Quieres hacer algo como una actualización en cascada? y luego ¿los datos viejos, guardarlos en un histórico?

1 respuesta

bLaKnI #12 Jun '14 Inocente

#11 Exacto!
No hace falta el diagrama, porque precisamente no lo tengo correctamente diseñado!

Pero voy a hacer algo rapido de ejemplo. Wait.

EDIT:

Veamos un ejemplo sencillo tal que así:

entonces mi idea es hacer algo así:

PEEEERO como veis, que sucede con las direcciones, que son de relacion 1 (personas) a N (direcciones)?

Habria una tabla direcciones_historico? Y como la modelo?
Y entonces, cuando añado la fila vieja a personas_historico, que deberia suceder en esta supuesta nueva tabla?

eXtreM3 #13 Jun '14

No entiendo nada. Para qué sirve la tabla personas_historico, si es igual que personas.

Necesitas 4 tablas: personas, direcciones, cuentas y comunicaciones. Me centro en direcciones, las demás serán iguales.

PERSONAS:

id_persona
nombre
apellidos
fecha_nacimiento

DIRECCIONES:

id_direccion
id_persona
direccion
active

Añades una persona. Su dirección la metes en la tabla direcciones con active 1.
La persona se muda. Antes de añadir haces un

select max(id_direccion) from direcciones where id_persona = $id

y le pones active = 0.

Añades una nueva dirección (misma id_persona, distinta dirección, por lo que no estás duplicando nada), con active 1.

Y listo, ahí tienes tu histórico de direcciones para una persona, sin redundancia ninguna. Además el active te permitirá seleccionar una dirección como activa, en caso de que quizás tenga 2 o 3 domicilios posibles y te interese mantener el actual, si se muda de nuevo pues activas uno de los domicilios que ya tienes.

1 respuesta

bLaKnI #14 Jun '14 Inocente

#13 Coño claro que es iguala!
En la tabla personas SOLO hay el registro único de persona correcta. Para cada cambio, en vez de UPDATE en esta tabla personas, se hace un insert en la tabla personas_historico del estado actual y en la personas, se hace el update de los campos. Asi en personas SIEMPRE hay solo una sola fila para cada persona y en la de histórico, tantas como cambios haya habido. De ahí la consistencia de datos. Pero eso, con las dependientes que son de tipo N no se como mapearlo.

En cuanto a la solución que planteas, que sucede con las personas? Lo mismo? Contiene un "active"?

El histórico, es porque después la app deberá poder cantarlo con creces, es decir:

Persona X eliminó la siguiente dirección: "sdfsdfdfsdfsdfsdf"
Presona X añade una nueva dirección "sdfsdfsdfsdf".
Persona X añade cuenta corriente "sdfsdfsdf"
Persona X añade cuenta corriente "fdsgiiofsfdifdiooifds"
Persona X cambia fecha de nacimiento "asdasdasd".

Algo así. Evidentemente hay una tabla para acciones con asociación al histórico.

1 respuesta

eXtreM3 #15 Jun '14

#14 jajaja, una de dos, o te explicas como el culo o soy incapaz de comprender lo que buscas.

Si la solución no es #13 voy chapando el hilo, lo siento porque no te entiendo xD

Buen edit.

A ver, el fin de una base de datos es guardar información correcta. No te interesa en absoluto guardar el histórico de fechas de nacimiento incorrectas, qué sentido tiene? Si te equivocas metiéndola la editas y se acabó.

Para todos los demás datos sensibles a cambios (domicilio, cuenta corriente...) creas una tabla por cada campo y la asocias al id_persona.

2 respuestas

DarkSoldier #16 Jun '14

bLaKnI #17 Jun '14 Inocente

#15 Pero si yo ya lo se... :_(
Pero es lo que quiere el cliente!!! Quiere un puto historico absoluto para TODA LA MALDITA DB! xDD

Y claro, historicos he hecho mil! Para tablas individuales y sus registros!
Pero cuando un registro de una tabla especifica, tiene multiples dependencias foraneas a otras tablas, no se como se hace un historico para dichas dependencias, relacionandolo con la linia historica del registro a modificar.

Es que ya no se puede explicar de mas formas!!!!!!! :_____(

1 Persona (tabla 1), 2 direcciones asociadas (tabla 2). Osea, un registro de tabla 1, con un ID. Dos registros de la tabla 2 con el id de el registro de la tabla 1.
Se quiere modificar algun aspecto del registro de la tabla 1 (o eliminarlo, o añadirlo, o hacer lo que sea en las direcciones por ejemplo, es decir, en la stablas asociadas), luego se copia el registro a una tabla historica que es una replica de la tabla 1. Se modifica el registro en la tabla 1 original.
Si? Ahora tenemos el registro en tabla 1 correcto, y en tabla 1 historica, el registro que habia hasta el momento. PERO QUE PASA con los dos registros que cuelgan de la tabla 1 via ID?
Hay que duplicarlos tambien en respectivas tablas historicas? A pesar de ser distintas tablas y registros, el concepto "persona" son todo el conjunto informativo formado por los registros de las distintas tablas! La principal y las subjetivas/asociadas. Por lo tanto, al realizar una copia de la persona para mantenerla como historico hay que hacerlo de TODAS sus partes entiendo, no solo de una parte (que seria el registro de la tabla 1)! Esto es lo que quiero saber! Y esto es lo que no se mapear si lo planteo con tablas gemelas "historicas".

Y con el metodo "active", habria que replicar igualmente toda la info en todas las tablas que toque, poniendo el active a 0, no?

Nos entendemos ahora? :S :S

2 respuestas

eXtreM3 #18 Jun '14

#17 vale, pues si quieres un histórico de TODOS los datos de la persona, lo más eficiente (creo) para no replicar nada, es crear una tabla dependiente por cada campo que quieras guardar el histórico.

PERSONAS: id_persona
FECHAS_NAC: id_persona, fecha
DIRECCIONES: id_persona, direccion
NOMBRE: id_persona, nombre
APELLIDO: id_persona, apellido

Es que no sé... es o eso, o hacerte una tabla personas con todos los campos, y replicar en personas_historico toda la tupla siempre que haya un cambio, con lo que tendrás redundancia a porrillo. Pero me surge la duda de qué sería más rápido una vez aumente el volumen de datos de manera considerable.

DaRk-eXe #19 Jun '14

jajajjaaj #15 que bueno, #17 eres el amo.

Base de datos: historico de filas. ¿Como?

Usuarios habituales

Tags