Accueil › SQL & bases de données › Modéliser le temps et l'historique
Modéliser le temps et l'historique
Modéliser un domaineintermediaire
Un UPDATE détruit l'état précédent. Quand cet état a de la valeur — prix, adresse, statut, affectation — il faut le modéliser explicitement.
Périodes de validité : une ligne par état, bornée dans le temps.
CREATE TABLE prix_produit (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
produit_id integer NOT NULL REFERENCES produit (id),
prix numeric(10, 2) NOT NULL CHECK (prix >= 0),
valide_du timestamptz NOT NULL,
valide_au timestamptz -- NULL = toujours en vigueur
);
La lecture du prix courant devient une condition sur la période :
SELECT prix FROM prix_produit
WHERE produit_id = :id
AND valide_du <= now()
AND (valide_au IS NULL OR valide_au > now());
Utilisez la convention semi-ouverte [début, fin) : la fin d'une période est exactement le début de la suivante. Elle évite les trous d'une seconde et les chevauchements.
Table d'audit : qui a changé quoi, quand.
CREATE TABLE commande_audit (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
commande_id integer NOT NULL,
operation text NOT NULL, -- INSERT / UPDATE / DELETE
ancien jsonb,
nouveau jsonb,
auteur text NOT NULL DEFAULT current_user,
survenu_le timestamptz NOT NULL DEFAULT now()
);
Alimentée par un trigger, elle capture aussi les écritures qui ne passent pas par l'application — scripts, corrections manuelles — c'est-à-dire exactement celles qu'on cherche après un incident.
Les termes à connaître
- Historisation
Conserver les états successifs d'une donnée plutôt que de les écraser. Sans elle, un
UPDATEdétruit définitivement l'information précédente.- Période de validité
Deux colonnes
valide_du/valide_auqui bornent la période pendant laquelle une ligne est vraie. La ligne courante a souventvalide_au IS NULLou une date lointaine.- Intervalle semi-ouvert
Convention
[début, fin): la borne de début est incluse, celle de fin exclue. Elle évite les chevauchements d'une seconde et les trous entre deux périodes consécutives.- Table d'audit
Une table séparée qui enregistre chaque modification (
qui,quand,quoi, ancienne et nouvelle valeur). Souvent alimentée par un trigger, pour capturer aussi les écritures hors application.- tstzrange
Type PostgreSQL représentant un intervalle de temps. Combiné à une contrainte
EXCLUDE, il empêche deux périodes de se chevaucher — garantie impossible avec un simpleCHECK.- Contrainte EXCLUDE
Généralisation de
UNIQUE: interdit deux lignes dont les valeurs se chevauchent selon un opérateur.EXCLUDE USING gist (produit_id WITH =, periode WITH &&)empêche deux prix simultanés pour un produit.
Cette page présente la partie essentielle de la leçon. Les exemples commentés, les pièges courants et les trois questions de quiz sont dans l'application.