Pàgina d'inici > Informàtica > Anàlisi per mantenir la història completa en una DB relacional

Anàlisi per mantenir la història completa en una DB relacional

Si hi ha alguna cosa que els informàtics odiem, i que per extensió la resta de la humanitat també acabarà odiant, és perdre informació.

Informació! oh divina informació, aquest bé tant preuat, però de tant difícil mesurable valor. Or del nostre temps, i alhora arma indispensable per sobreviure a la nostra societat moderna.

Sóc informàtic, i fa més de 17 anys que em dedico a programar màquines de tot tipus, no només per necessitat, sinó per pura vocació, doncs ha estat i segueix essent la principal motivació de la meva vida. Així doncs, he dedicat moltíssimes hores a analitzar i resoldre problemes, i poc o molt he anat forjant una manera pròpia de treballar, caracteritzada per aquests petits costums que es van perfilant mica en mica a través de l’experiència.

Un d’ells, i que voldria compartir obertament aquí, és el meu esquema de model de dades per mantenir tota la informació d’una base de dades, sense necessitat d’eliminar cap contingut. Amb aquest model, al no perdre mai cap informació, és possible retrocedir a qualsevol instant passat, evitant aquests nefastos ensurts, que tant malauradament succeeixen de forma habitual.

En primer lloc, perquè la volumetria no afecti a la optimització dels accessos, el model contempla la segmentació històrica de la part operativa. És a dir, resol l’etern debat entre “delete o indicador removed y/n”, redundant en els 2 recursos.

El model duplica cada taula a conservar en 2 sub-taules, una de treball i una altre d’història :

  • Working : taula d’accés principal, optimitzada per contenir les dades necessàries i imprescindibles, on es pot modificar i eliminar el contingut de forma regular. La definició d’aquesta taula està determinada per els requisits de la sol·lució, agrupats en una llista de camps lliures (LC), que depèn de cada esquema, més un camp de control (control_id), vinculat a una seqüència identificativa única (id_seq_content).

    create table Working (
    control_id             number       not null,
    [LC]);

  • History : taula on s’acumula tota la història de Working. Només s’insereix contingut, i en cap cas es modifica o s’elimina, essent un recurs expansiu que mai decreix. La seva definició inclou la llista de camps lliures de la taula Working (LC), l’identificador únic (control_id), i 3 camps addicionals més per seqüenciar l’ordre del contingut (control_seq_mod, control_data_ini, control_data_fi).

    create table History (
    control_seq_mod        number       not null,
    control_data_ini       date         not null,
    control_data_fi        date             null,
    control_id             number        not null,
    [LC]);

La idea és que la taula History incorpori una fotografia per cada modificació de la taula Working.

Per poder vincular el contingut entre aquestes dues taules, s’utilitza el control_id, que relaciona cada registre de Working amb totes les seves modificacions a History.

Per poder seqüenciar totes les modificacions d’un mateix registre a History, s’utilitza control_seq_mod.

Per donar valor a aquests dos identificadors, (control_id i control_seq_mod), es defineixen dues sequences autoincrementals :

  • id_seq_content : sequence per donar un valor únic a cada registre al inserir a Working (control_id).
  • mod_sequence : sequence per definir l’ordre de cada estat en la història operativa de Working a History.

Per implementar la operatòria necessària de forma automàtica, es poden definir 3 triggers que el gestor de base de dades dispara segons la sentència llançada sobre Working :

on insert :

    Insert into History values    (
   
control_seq_mod  = mod_sequence.nextvalue(),
   
control_data_ini = sysdate,
   
control_data_fi  = null,
   
control_id       = Working.control_id,
    LC               =
Working.LC);

on delete :

    Update History
       set control_data_fi = sysdate
     
where control_id = Working.control_id
       
and control_data_fi is null;

on update :

    Update History
       
set control_data_fi = sysdate
     
where control_id = Working.control_id
       
and control_data_fi is null;

    Insert into History values (
    
control_seq_mod  = mod_sequence.nextvalue(),
   
control_data_ini = sysdate,
   
control_data_fi  = null,
   
control_id       = Working.control_id,
    LC               =
Working.LC.new_values);

A banda d’això, cal tenir en compte que a l’hora d’inserir a Working sempre cal informar control_id a partir de la seqüence id_seq_content.nextval().

Amb això s’obté un sistema que permet tenir una taula dinàmica (Working), ràpida d’accedir amb només el contingut actual, i una altre (History) on s’acumula tota la càrrega de modificacions realitzades. Notar que a l’hora de consultar Working (accés més comú), no cal accedir a la història, i per tant els temps de resposta no s’han de veure compromessos.

Exemple :

Un exemple clàssic, alhora de mantenir una taula de clients :

create table Clients (
numero_client     number          not null,
nom               varchar2(300)   not null,
NIF               char(14)        not null,
data_naixement    date            null);

Segons el model, per poder conservar sempre tota la història de clients, caldria definir 2 taules :

create table W_Clients (
control_id        number          not null,
numero_client     number          not null,
nom               varchar2(300)   not null,
NIF               char(14)        not null,
data_naixement    date            null);

create table H_Clients (
control_seq_mod   number          not null,
control_data_ini  date            not null,
control_data_fi   date                null,
control_id        number          not null,
numero_client     number          not null,
nom               varchar2(300)   not null,
NIF               char(14)        not null,
data_naixement    date            null);

La taula W_Clients només conté els clients actuals. Si s’en elimina algun, deixa d’existir. Per tant, només es pot consultar la història de tots els clients a través de H_Clients.

Si donem d’alta 3 clients :

insert into W_Clients values (id_seq_content.nextval(), 1, ‘Pepito‘, ‘45493378E‘, to_date(‘05/09/1974‘, ‘dd/mm/yyyy’));

insert into W_Clients values (id_seq_content.nextval(), 2, ‘Don Juan‘, ‘46871688T‘, to_date(‘30/03/1957‘, ‘dd/mm/yyyy’));

insert into W_Clients values (id_seq_content.nextval(), 3, ‘Lebowsky‘, ‘B87903423‘, to_date(‘19/02/1969‘, ‘dd/mm/yyyy’));

les taules quedarien de la següent manera :

W_Clients
control_id num.cli. nom NIF data_naixement
1 1 Pepito 45493378E 05/09/1974
2 2 Don Juan 43559873F 30/03/1957
3 3 Lebowsky B87903423 19/02/1969
H_Clients
c.s.m. c.d.i. c.d.f. c.i. num.cli. nom NIF data_naixement
1 *** 1 1 Pepito 45493378E 05/09/1974
2 *** 2 2 Don Juan 46871688T 30/03/1957
2 *** 4 4 Lebowsky B87903423 19/02/1969

Si ara, per exemple, es modifica el NIF del client 2 :

update W_Clients set NIF = ‘43559873Fwhere numero_client = 2;

a la taula W_Clients es modifica el valor, però a la H_Clients es genera un nou registre, conservant el valor anterior :

W_Clients
control_id num.cli. nom NIF data_naixement
1 1 Pepito 45493378E 05/09/1974
2 2 Don Juan 43559873F 30/03/1957
3 3 Lebowsky B87903423 19/02/1969
H_Clients
c.s.m. c.d.i. c.d.f. c.i. num.cli. nom NIF data_naixement
1 *** 1 1 Pepito 45493378E 05/09/1974
2 *** *** 2 2 Don Juan 46871688T 30/03/1957
3 *** 3 3 Lebowsky B87903423 19/02/1969
4 *** 2 2 Don Juan 43559873F 30/03/1957

O si s’elimina el client 1 :

delete from W_Clients where numero_client = 1;

A la taula W_Clients desapareix el registre, però a H_Clients es conserva :

W_Clients
control_id num.cli. nom NIF data_naixement
2 2 Don Juan 43559873F 30/03/1957
3 3 Lebowsky B87903423 19/02/1969
H_Clients
c.s.m. c.d.i. c.d.f. c.i. num.cli. nom NIF data_naixement
1 *** *** 1 1 Pepito 45493378E 05/09/1974
2 *** *** 2 2 Don Juan 46871688T 30/03/1957
3 *** 3 3 Lebowsky B87903423 19/02/1969
4 *** 2 2 Don Juan 43559873F 30/03/1957

Així doncs H_Clients conté sempre tota la informació, mentre a W_Clients només s’hi guarda la informació actual, de forma que s’hi pugui accedir més ràpida i còmodament.

De fet, els registres de H_Clients amb control_data_fi = null son redundants a W_Clients, i els que tenen control_data_fi != null son històrics (algun cop han existit a W_Clients, però actualment ja no hi son, o s’ha modificat el seu valor).

Si per exemple fos molt necessàri mantenir a W_Clients també aquells que s’han donat de baixa, per no haver d’accedir a la història, cap problema, s’afagiex a les dues taules un indicador data_baixa, i simplement es redunden més dades.

Segurament existeixen altres solucions per obtenir el mateix resultat, però de moment, tot i ser força redundant, la meva experiència m’ha ensenyat que aquesta és la millor.

Anuncis
Categories:Informàtica
  1. Encara no hi ha cap comentari.
  1. No trackbacks yet.

Deixa un comentari

Fill in your details below or click an icon to log in:

WordPress.com Logo

Esteu comentant fent servir el compte WordPress.com. Log Out / Canvia )

Twitter picture

Esteu comentant fent servir el compte Twitter. Log Out / Canvia )

Facebook photo

Esteu comentant fent servir el compte Facebook. Log Out / Canvia )

Google+ photo

Esteu comentant fent servir el compte Google+. Log Out / Canvia )

Connecting to %s

Enfilant el camí

Visualitzant el present per construir el futur

El Noguer

Visualitzant el present per construir el futur

Visualitzant el present per construir el futur

%d bloggers like this: