Création d'une table d'historisation

[waters]

Bonjour,

Je souhaite créer une table pour historiser les données d’une table toute simple :
Personne (id_personne , nom, prenom, age)

Le MCD :

• Une personne peut faire l’objet d’une historisation 0 ou plusieurs fois
• Une historisation ne concerne qu’une seule personne

Personne (id_personne, nom, prenom, age) ----0,N--historique----1,1---personne_hist(id_hist, id_personne_hist, nom_hist, prenom_hist, age_hist, date-modif)

Le passage au MLD donne:

• Personne (id_personne, nom, prenom, age)
• Personne_hist (id_hist, #id_personne, id_personne_hist, nom_hist, prenom_hist, age_hist, date_modif)

La création des tables :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE personne(
Id_personne int(3) AUTO_INCREMENT,
Nom VARCHAR (20),
Prenom VARCHAR (20),
Age int (2),
CONSTRAINT pk_personne PRIMARY KEY (id_personne)
) ;
 
CREATE TABLE personne_hist(
Id_hist int(3) AUTO_INCREMENT,
Id_personne_hist int(3),
Id_personne int(3),
Nom_hist VARCHAR(20),
Prenom_hist VARCHAR(20),
Age_hist int (2),
date_modif DATETIME,
CONSTRAINT pk_personne_hist PRIMARY KEY (id_hist),
CONSTRAINT fk_personne_hist FOREIGN KEY (id_personne) REFERENCES personne (id_personne)
);

Je ne suis pas très convaincu de mon raisonnement. Pouvez-vous me dire si j'ai juste ou pas?

Merci !

[CinePhil]

D'après ton modèle, toutes les personnes resteront dans la table personne, qu'elles soient historisées ou non. Vu les colonnes de la table personne, quel est l'intérêt d'historiser cette table ?

De plus, l'âge est une donnée calculable qui ne devrait pas être stockée en BDD. Il vaudrait mieux y stocker la date de naissance et calculer l'âge chaque fois que nécessaire. La formule pour le faire en SQL est donnée dans ce message de SQLPro.

[waters]

ah non, je voulais juste m'essayer à l'historisation Ma table n'est qu'un exemple, elle n'a aucun sens pratique

Je viens de découvrir le "concept" d'historisation (enfin en classe, la prof en avait parlé 30 sec vite fait) et je souhaiterai le mettre en pratique

[Yanika_bzh]

De plus, l'âge est une donnée calculable qui ne devrait pas être stockée en BDD. Il vaudrait mieux y stocker la date de naissance et calculer l'âge chaque fois que nécessaire.

Si la table sert d'historisation, il peut tout aussi bien garder l'age dedans.
Dans la pluspart des cas, l'historisation se base sur des données non evolutives.
Il lui est peut etre seulement utile de savoir que lors de l'insertion de son nème tuple, la personne avait 54 ans... Sinon il devra etre obligé aussi d'inserer une date référence pour le calcul de son age (qui n'est pas forcément la date du jour), ce qui revient a la meme chose.

Bon courage

[waters]

Dans la table d'historisation j'ai rajouté un id qui servira de clé ainsi qu'un attribut pour connaitre la date à laquelle a été apporté la modification.

Mainenant que mes tables sont créées, je voulais savoir quels étaient les moyens pour alimenter la table d'historisation. Je suppose qu'il faut pour cela mettre en place un trigger de type LDD car les modifications seront du type DELETE ou UPDATE.

Et c'est là que les choses se corsent, en effet, j'ai fait plein de tests sur la console de phpmyadmin et aucun code ne semble juste

Voici le dernier en date:

Code :

1
2
3
4
5
6
7
8
CREATE TRIGGER hist
AFTER DELETE ON personne
FOR EACH ROW
BEGIN
INSERT INTO personne_hist (' ', 'id_personne', 'id_personne_hist',' nom_hist', 'prenom_hist', 'age_hist', 'date_modif')
SELECT id_personne, nom, prenom, age
FROM personne;
END;

pouvez-vous me dire ce qui cloche?
à mon avis, le hic se trouve dans le SELECT, il prend en compte 4 valeurs et le INSERT en prend 7. Désolé je débute

[CinePhil]

Envoyé par Yanika_bzh

Dans la pluspart des cas, l'historisation se base sur des données non evolutives.

Euh... je dirais plutôt que ça sert justement pour garder en mémoire les données qui ont évolué !

Envoyé par waters

ah non, je voulais juste m'essayer à l'historisation Ma table n'est qu'un exemple, elle n'a aucun sens pratique

OK. Alors prenons un exemple plus concret : l'évolution des produits.

Soit la table des produits suivante :
produit (prd_id, prd_reference, prd_nom, prd_qte_par_lot, prd_prix_vente_ht)

Un produit peut :
- changer de prix de vente ;
- voir son nom modifié (un autre produit similaire est créé et on apporte une précision au nom du premier produit pour le différencier) ;
- changer de référence sans changer de caractéristiques (nouveau codage de la nomenclature des produits) ;
- voir sa quantité par lot changer (vendu à l'origine par 10 puis plus tard par 50).

Pour historiser ces modifications successives, on peut tout simplement créer une table de structure identique + une colonne de date de changement :
produit_histo (prh_id_produit, prh_date_changement, prh_reference, prh_qte_par_lot, prh_prix_vente_ht)

[Yanika_bzh]

Non évolutives a partir du moment ou elles sont historisées, bien sur ...

[Yanika_bzh]

Envoyé par CinePhil

Un produit peut :
- changer de prix de vente ;
- voir son nom modifié (un autre produit similaire est créé et on apporte une précision au nom du premier produit pour le différencier) ;
- changer de référence sans changer de caractéristiques (nouveau codage de la nomenclature des produits) ;
- voir sa quantité par lot changer (vendu à l'origine par 10 puis plus tard par 50).

Des exemples concrets
Donc je vais apporter ma touche personnelle sur mon experience dans ce domaine.
Generalement on parle de produits techniques et commerciaux (plusieurs produits commerciaux pouvant etre issus d'un seul et meme produit technique en fonction de son packaging, des clients, ...).
La quantité de lot est l'altération d'un produit commercial (le produit technique de base est le meme). Dans la pluspart des cas (enfin toujours selon mon experience), la réference commerciale change, ce qui donne naissance a un nouveau produit commercial. Modifier le nom d'un produit, j'ai jamais vu cela, car les impacts (commerciaux, publicitaires, ...) sont extremement importants.

Effectivement comme le dit Cinephil, une date de reference d'historisation doit etre présente.

Bon courage

[Rei Ichido]

Envoyé par Yanika_bzh

Modifier le nom d'un produit, j'ai jamais vu cela, car les impacts (commerciaux, publicitaires, ...) sont extremement importants.

[HS] Mais ça arrive ! Cf Raider / Twix, c'est l'exemple qui me vient en tête (mhhh, faiiiim).[/HS]
J'ajouterai qu'en plus de la date de modification, il est souvent utile d'avoir une / plusieurs colonnes qui permettent de tracer les modifications : utilisateur et/ou raison de la modification.

[Yanika_bzh]

Envoyé par Rei Ichido

[HS] Mais ça arrive ! Cf Raider / Twix, c'est l'exemple qui me vient en tête (mhhh, faiiiim).[/HS]
J'ajouterai qu'en plus de la date de modification, il est souvent utile d'avoir une / plusieurs colonnes qui permettent de tracer les modifications : utilisateur et/ou raison de la modification.

Pas sur que cela ne represente qu'une modification d'un libéllé commercial, mais plutot la création d'un nouveau produit commercial (nouveau packaging, CG de vente différentes, liens vers un service client différents, référencement commercial différent [Il serait interessant de comparer les 2 codes barres générés par exemple] ... )

[waters]

Envoyé par CinePhil

Soit la table des produits suivante :
produit (prd_id, prd_reference, prd_nom, prd_qte_par_lot, prd_prix_vente_ht)

Pour historiser ces modifications successives, on peut tout simplement créer une table de structure identique + une colonne de date de changement :
produit_histo (prh_id_produit, prh_date_changement, prh_reference, prh_qte_par_lot, prh_prix_vente_ht)

ok super exemple

Passons au trigger:

Code :

1
2
3
4
5
6
7
8
CREATE TRIGGER hist
AFTER DELETE ON produit
FOR EACH ROW
BEGIN
INSERT INTO produit_histo ('prh_id_produit', 'prh_date_changement', 'prh_reference','prh_nom', 'prh_qte_par_lot',' prh_prix_vente_ht')
SELECT prd_id, prd_reference, prd_nom, prd_qte_par_lot, prd_prix_vente_ht
FROM produit;
END;

je sais que le code est faux pour les mêmes raisons évoquées plus haut.
Pouvez-vous me dire où ça va pas?

[Yanika_bzh]

Il vous manque une donnée pour votre colonne date

Bon courage

[CinePhil]

Pourquoi fais-tu un trigger AFTER DELETE et pas plutôt AFTER UPDATE ? Ou même plutôt un BEFORE UPDATE puisqu'il s'agit d'historiser les valeurs existantes donc avant qu'elles soient modifiées !

Bon c'est vrai que si tu supprimes un produit il peut être bon de l'historiser mais perso je préférerais le laisser dans la table d'origine, quitte à ajouter une date de validité du produit ou un booléen indiquant si le produit est actif ou pas. En effet, un produit est généralement lié à des commandes, des factures, d'autres produits s'il entre dans une composition ou s'il est composé... Si tu supprimes le produit, il faut alors examiner les conséquences sur les autres tables associées à la table des produits.

Quant à ta requête INSERT dans le trigger, pour donner une valeur à la date de modification, selon le type de la colonne, utilise CURRENT_DATE ou CURRENT_TIMESTAMP.

[waters]

Envoyé par Yanika_bzh

Il vous manque une donnée pour votre colonne date

Bon courage

après le select vous voulez dire? comment selectionner une donnée date qui n'existe pas dans la table?

[Yanika_bzh]

Envoyé par waters

après le select vous voulez dire? comment selectionner une donnée date qui n'existe pas dans la table?

Cinephil vous donne la réponse dans le post précédent.

[waters]

Pourquoi fais-tu un trigger AFTER DELETE et pas plutôt AFTER UPDATE ? Ou même plutôt un BEFORE UPDATE puisqu'il s'agit d'historiser les valeurs existantes donc avant qu'elles soient modifiées !

oui mais dans ma tête j'avais les triggers qu'on a fait en classe, mettre un AFTER DELETE associée à un OLD....enfin bref...
bref, les triggers c'est pas mon fort, tu l'as remarqué.

Bon c'est vrai que si tu supprimes un produit il peut être bon de l'historiser mais perso je préférerais le laisser dans la table d'origine, quitte à ajouter une date de validité du produit ou un booléen indiquant si le produit est actif ou pas. En effet, un produit est généralement lié à des commandes, des factures, d'autres produits s'il entre dans une composition ou s'il est composé... Si tu supprimes le produit, il faut alors examiner les conséquences sur les autres tables associées à la table des produits.

Quant à ta requête INSERT dans le trigger, pour donner une valeur à la date de modification, selon le type de la colonne, utilise CURRENT_DATE ou CURRENT_TIMESTAMP

on peut aussi mettre DATETIME, non?

[waters]

Envoyé par Yanika_bzh

Cinephil vous donne la réponse dans le post précédent.

exact j'ai répondu à votre message avant de lire le sien

[CinePhil]

Envoyé par waters

on peut aussi mettre DATETIME, non?

DATETIME est un type de colonne. Sauf erreur de ma part (flemme de vérifier), il n'y a pas de fonction DATETIME ni de CURRENT_DATETIME en SQL.

[Yanika_bzh]

CURRENT_TIMESTAMP ?

[CinePhil]

Envoyé par Yanika_bzh

CURRENT_TIMESTAMP ?

Oui ?
Il te pose un problème le CURRENT_TIMESTAMP ?