Pbs de conception / Historisation de base de donnée Résolu

Question

Boujour,

Je réfléchis actuellement à une architecture me permettant de garder un historique des modifications effectuer dans une base.

Pour simplifier je prendrais comme exmple la table suivante :

T_Utilisateur
id_utilisateur
nom_utilisateur
civ_utilisateur
adresse_utilisateur

Je souhaite historiser le champ adresse_utilisateur et d'autres champs de la même table par la suite.

----------------------------------------------------------------------------------------------------------------------------------
Ma première solution consite à dupliquer les enregistrements en ajoutant une date et un flag actif ou non. La clé primaire devient la date + id_utilisateur.

Ex : 
id_utilisateur    Nom_utilisateur    Civ_utilisateur    Actif    Date    adresse_utilisateur
1234567            toto                                Mr                        0        01/01/05    rue de la bete
1234567            toto                                Mr                        1        01/03/06    rue delidiot

-----------------------------------------------------------------------------------------------------------------------------------
Ma seconde solution est la suivante : on crée une table historisation des champs. Les champs de la T_Utilisateur peuvent être modifier au minimum 0 et n fois au max. Pour un champ donné ; celui ci peut être modifié 0 fois et 1 fois au maximum. La clé étrangère id_utilisateur devient la clé primaire de la table champs.

Ex :
table id_utilisateur
id_utilisateur    Nom_utilisateur    Civ_utilisateur   adresse_utilisateur

1234567            toto                                Mr                        rue de la bete

1234567            toto                                Mr                        rue delidiot (<--- Après Update)

table champ
id_utilisateur    Nom_champs    valeurs_champs    Date
1234567
1234567           adresse_utilisateur    rue de la bete    06/01/06
-----------------------------------------------------------------------------------------------------------------------------------
J'aimerais conaître vos avis et vos propositions sur cette problèmatique.

Comment modéliser au mieux l'historisation de Champs d'un table ?

Je vous souhaite une bonne journée.

Flux__

cs_Malkuth · Accepted Answer

ce qu'il serait bon a savoir c'est combient de champs diférent sont modifier en moyenne a chaque fois:
si a chaque modif tu modifie plusieurs enregistrement a la fois dans ce cas je te conseil un truc dans ce genre:

===================
TBL_SRC
ID      Champ1   Champ2   Champ3   ...
Clef primaire : ID.
===================
TBL_HISTO
DATEVERSION   ID      Champ1   Champ2   Champ3   ...
Clef primaire : DATEVERSION et ID.
===================
CREATE TRIGER TBL_SRC_UPDATE 
ON TBL_SRC AFTER UPDATE,DELETE
BEGIN
   INSERT INTO TBL_HISTO 
      (DATEVERSION   ID      Champ1   Champ2   Champ3)
   SELECT
      GETDATE (), ID, Champ1, Champ2, Champ3
   FROM deleted;
END
===================
Cette méthode te permet de travailler sur la table TBL_SRC sans te préocuper de l'historique.
Mais a chaque modification l'ancienne version serat créer dans la table d'historique.
Ensuite pour récupérer toutes les version précédente d'un enregistrement :
SELECT * FROM Tbl_Histo WHERE ID = @ID ORDER By DATEVERSION DESC
Pour revenir a une ancienne version :
UPDATE TBL_SRC
SET
   Champ1=Champ1,
   Champ2=Champ2,
   Champ3=Champ3
FROM Tbl_Histo inner join TBL_SRC ON Tbl_Histo.ID=TBL_SRC.ID
WHERE [mailto:Tbl_Histo.ID=@ID Tbl_Histo.ID=@ID] AND Tbl_Histo.DATEVERSION = @DATE

ce qui est marrant c'est que même revenir à une ancienne version créer un nouvelle enregfistrement dans la table HISTO

enfin tu peux même récupérer les enregistrement supprimer (je te laisse trouver comment).

nhervagault · Answer

Salut

Alternative 3 un mixe des deux.

En se basant sur la methode 2.
Tous les enregistrements sont historisée dans la table et même le courant

Avantage :
1-> Acces courant table non historisée.
2-> Acces historique table historisée, si date du jour --> enregistrement courant.

Inconvenient la premiere methode c'est que la table peut grossir assez vite et au noveau des performances, il peut y avoir des problèmes.
Ensuite les requetes peut être plus complxe car il faut faire intervenir un critere pour selectionnner l'enregistrement courant.

Inconvenient de la 2 methode 
Tu es obligé d'avoir un lien sur l'enregistrement courant si auun enregistrement est historiser ou la date est proche de la date courante.

Inconvient methode 2 et 3 
2 insert ou 1 insert + 1 update
2 tables a manipuler

Apres il y a peut etre d'autres avis.

A toi de juger la meilleure methode, j'ai peut etre oublier des inconvients ;-)

Flux__ · Answer

Bonjour,

Merci pour ce complément d'informations.

Malehuresement je ne comprend par l'aternative 3. Quand tu dis :

    En se basant sur la methode 2.
Tous les enregistrements sont historisée dans la table et même le courant

J'ai l'impression qu'il s'agite de la méthode 1 je ne vois pas les améliorations à apportés ??

Peut-tu me réexpliquer ?

Merci de ton aide.

Flux__

nhervagault · Answer

Non tu as 2 tables alors que dans la 1 tu n'as qu'une seule table.

Cette methode est utilisable si utilise tres peu les historisations.
Lors du travail journalier tu ajoutes et modifies tes données. dans la table facture
pour les suppression tu supprimes l'enregistremnt dans facture 
et tu flag la colonne (nouvelle colonne)detruit de la table histo par exemple
Il y a d'autre facon de gerer la detruction, 
et meme l'historisation mais cela depend du contexte aussi.
De la frequence de modification ..etc..

et tu repercutes en inserant toujours dans la table  facture_histo
Donc la tu travailles sur la table facture.

Mais si tu veux retrouver par exemple uen vielle factures 
ou les factures d'un client tu travailles sur la table facture_histo
et cette table ci contient la derniere facture du client

Car si tu insere pas le courant tu devras faire un union sur la table facture et facture_histo.
Ce qui alourdis la requete.

that's all

cs_Malkuth · Answer

PS : je te conseille d'enregistrer les historique dans un fichier de base de donnée diférent je pense que c'est plus rapide aprés pour le travaille normal sur les autres tables parceque effectivement une table d'historique peux devenir extremmeme"nt grose selon le volume de modification appliquer sur la table d'orrigine.

A mon avis l'avantage du trigger avec une table d'historique a pars c'est que tu vas pouvoir garder une structure plus conforme aux donnée (sans divers éléments de contrôle qui vienne polluer tes données).

Flux__ · Answer

Salut et merci pour vos réponses.

Effectivement je n'avais pas envisagé les Triggers, c'est une solution qui ma l'air efficace.

Je l'ai crée comme cela sous mysql (si ca peut etre utile à qq)

CREATE TRIGGER trig_tab_src AFTER UPDATE
ON tab_src FOR EACH ROW
INSERT INTO tab_histo(DATEVERSION, ID, Champ1, Champ2, Champ3) SELECT curtime(), ID, Champ1, Champ2, Champ3
FROM tab_src

Le seul problème c'est que le champ DATEVERSION contient toujours 0000-00-00.
Alors que dans la requete select cutime() renvoit correctement l'heure.

Si vous avez des pistes.

++

Flux__

cs_Malkuth · Answer

DSL je connait trés peu mySQL donc je peux pas te dire ce qui ne vas pas pour le champ DATEVERSION

Une petite remarque : je ne connait pas les triggers  sous my sql mais soit sur de ne pas recopier toutes les lignes de la table TBL SRC a chaque fois SOUS SQL server on utilise les pseudos table inserted et deleted qui on la même structure que la table du trigger(en l'occurence TBL_SRC)et qui contienne respectivement pour inserted les nouvelle valeur (trigger UPDATE) ou les valeur insérer (TRIGGER INSERT), et pour deleted : les valeurs originale(avant modif )(TRIGER UPDATE) ou les valeur supprimer (TRIGER DELETE). encore une fois je ne connait pas les Triggers MYSQL donc ton code est peut etre bon (fais des essais si t'est pas sur).

seconde remarque : tu devrais aussi faire un trigger DELETE en effet tu devrais en cas de suppression soit 
supprimer toutes les version dans la table d'historique,
soit
ajouter la version supprimer dans la table historique.

Bon courage A+

Pbs de conception / Historisation de base de donnée

7 réponses

Votre réponse

Discussions similaires