Exemple de diagramme ER : concevoir pas à pas la base de données d'une boutique en ligne

Le diagramme ER est l'endroit où l'on vérifie la conception d'une base de données avant qu'elle ne devienne des migrations. Une liste de tables dit ce qui existe. Le diagramme montre comment les tables dépendent les unes des autres : quelle ligne a besoin de quelle autre, ce qui peut manquer et où se cache une relation plusieurs-à-plusieurs.

Ce guide conçoit la base de données d'une petite boutique en ligne, des exigences en langage courant jusqu'au diagramme entité-association terminé, et explique la notation au fur et à mesure. La boutique n'est qu'un exemple ; la méthode fonctionne pour n'importe quel schéma.

Quand un diagramme ER devient utile

Une liste de tables suffit quand un schéma compte une poignée de tables et qu'une seule équipe travaille dessus. Dessinez le diagramme quand des personnes extérieures à cette équipe doivent comprendre les données, quand vous relisez une conception avant d'écrire les migrations, ou quand un schéma a tellement grandi que plus personne ne l'a en tête.

Il existe deux notations courantes. La notation de Chen, issue de l'article de Peter Chen de 1976 qui a introduit le modèle entité-association, dessine les entités en rectangles et les relations en losanges. Elle convient à la modélisation conceptuelle. La notation patte de corbeau (crow's foot) dessine les tables en boîtes et place la cardinalité aux extrémités des traits. On l'attribue généralement à Gordon Everest, en 1976, et c'est la notation qu'utilisent la plupart des outils de conception de bases de données. Ce guide utilise la patte de corbeau.

Étape 1 : les exigences en phrases simples

Écrivez d'abord les règles sous forme de phrases. Chacune devient une table, une colonne ou un trait.

1. Un client s'inscrit avec une adresse e-mail et peut enregistrer plusieurs adresses.
2. Un client passe des commandes. Chaque commande est livrée à l'une des adresses du client.
3. Une commande contient un ou plusieurs produits, chacun avec une quantité et le prix payé.
4. Chaque produit appartient à une catégorie.
5. Une commande peut avoir plusieurs tentatives de paiement, et la plupart en ont une qui a réussi.

Étape 2 : entités et clés

Les noms deviennent des entités : clients, adresses, commandes, lignes de commande, produits, catégories et paiements. Chacune reçoit une clé primaire. Utilisez des clés de substitution (une colonne d'identité) pour la plupart des tables, et des clés naturelles uniques là où le métier en a déjà une : l'e-mail d'un client, le SKU d'un produit.

Les lignes de commande sont l'exception. Une ligne de commande n'a pas d'identité propre ; c'est le lien entre une commande et un produit. Sa clé primaire est la paire (order_id, product_id).

Étape 3 : relations et cardinalités

Chaque clé étrangère est un trait. Les symboles à chaque extrémité indiquent combien de lignes peuvent se trouver de ce côté :

Lisez une relation depuis une table, le long du trait, jusqu'au symbole à l'autre extrémité : « un client passe zéro ou plusieurs commandes », et dans l'autre sens : « chaque commande est passée par exactement un client ».

Une clé étrangère NOT NULL signifie « exactement un » du côté référencé ; une clé étrangère qui accepte NULL signifie « zéro ou un ». Le « un ou plusieurs » de l'autre côté est une règle métier, pas quelque chose qu'une clé étrangère impose ; écrivez-la donc sur le diagramme, là où les lecteurs la verront.

Étape 4 : le plusieurs-à-plusieurs

Commandes et produits sont en relation plusieurs-à-plusieurs : une commande contient plusieurs produits, et un produit apparaît dans plusieurs commandes. Les bases relationnelles modélisent cela avec une table au milieu. Ici, cette table existe déjà pour une meilleure raison que la simple tuyauterie : order_items porte la quantité et le prix unitaire au moment du paiement, des faits qui appartiennent à la paire et à aucun des deux côtés pris seul.

Étape 5 : les décisions à consigner

Certains choix sont invisibles dans la liste des colonnes et provoquent des bugs quand personne ne les connaît :

• Instantané du prix. order_items.unit_price copie le prix du produit au moment du paiement. Changer un prix plus tard ne doit pas modifier les anciennes commandes.
• Les adresses ne sont pas modifiées sur place. Dès qu'une commande pointe vers une adresse, modifier cette ligne réécrirait la destination des commandes passées. Une modification crée une nouvelle ligne d'adresse.
• Les paiements sont des tentatives. Une ligne par tentative, avec un statut, pour que les paiements échoués et retentés restent visibles.
• L'argent est en numeric. La documentation de PostgreSQL recommande numeric pour les montants monétaires parce que ce type est exact.

Étape 6 : le DDL

Les deux tables centrales en PostgreSQL :

CREATE TABLE orders (
  id                  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id         bigint NOT NULL REFERENCES customers (id),
  shipping_address_id bigint NOT NULL REFERENCES addresses (id),
  status              text NOT NULL DEFAULT 'pending'
                      CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
  currency            char(3) NOT NULL,
  total               numeric(12,2) NOT NULL CHECK (total >= 0),
  placed_at           timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  order_id   bigint NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
  product_id bigint NOT NULL REFERENCES products (id),
  quantity   integer NOT NULL CHECK (quantity > 0),
  unit_price numeric(12,2) NOT NULL,  -- copied from products.price at checkout
  PRIMARY KEY (order_id, product_id)
);

CREATE INDEX ON orders (customer_id);
CREATE INDEX ON order_items (product_id);

PostgreSQL ne crée pas de lui-même d'index sur une colonne de clé étrangère ; les deux lignes CREATE INDEX comptent donc pour les jointures et pour les suppressions dans les tables référencées. order_items.order_id est couvert par la clé primaire, dont l'index commence par cette colonne. ON DELETE CASCADE indique qu'une ligne de commande ne peut pas survivre à sa commande.

Le diagramme

Comment le lire :

• Les clients (en haut à gauche) ont zéro ou plusieurs adresses et passent zéro ou plusieurs commandes.
• Les commandes (au centre) sont livrées à exactement une adresse, contiennent une ou plusieurs lignes de commande et ont zéro ou plusieurs paiements.
• Les lignes de commande relient chaque commande à des produits, et chaque produit appartient à exactement une catégorie.
• La note contient les décisions de l'étape 5, à côté des tables qu'elles concernent.

Comment dessiner un diagramme ER lisible

• Nommez les relations avec des verbes. « passée par », « livrée à », « paie » transforment les traits en phrases.
• Marquez les clés dans les attributs. Les marqueurs PK et FK montrent d'un coup d'œil à quelle colonne appartient un trait.
• Regroupez les tables par domaine. Clients et adresses, commandes et paiements, le catalogue. Gardez chaque groupe rapproché pour que les traits restent courts.
• Laissez le bruit de côté. Les colonnes d'audit comme created_by et updated_at, les indicateurs de suppression logique sur chaque table et les tables techniques ajoutent des lignes sans rien ajouter à la compréhension.
• Dessinez la table plusieurs-à-plusieurs. La cacher derrière un trait direct entre commandes et produits cache aussi la quantité et l'instantané du prix.
• Montrez les règles métier que le schéma ne peut pas exprimer. « Une ou plusieurs » lignes par commande et « les adresses ne sont pas modifiées » sont des règles que tout le monde doit connaître.

Le garder synchronisé avec le schéma

Un diagramme ER dessiné à la main est périmé après la migration suivante. Générez plutôt une première version à partir du schéma. L'outil gratuit SQL en diagramme ER lit des instructions CREATE TABLE ou un pg_dump complet et dessine une carte par table, avec les marqueurs PK, FK et UK, et un trait par clé étrangère ; il existe aussi des outils pour PostgreSQL, Prisma et DBML. Les extrémités patte de corbeau générées suivent les colonnes : une clé étrangère NOT NULL est obligatoire, une clé qui accepte NULL est facultative. Les règles que le SQL ne contient pas, comme « une ou plusieurs » lignes, s'ajoutent à la main.

Le construire dans Archboard

1. Connectez-vous avec Google ou GitHub et créez un diagramme.
2. Pour partir du schéma, collez les instructions CREATE TABLE dans l'outil SQL en diagramme ER et sélectionnez Ouvrir dans Archboard. Pour dessiner à la main, ajoutez une Entité du pack Entité-association pour chaque table, avec un attribut par ligne et les marqueurs PK et FK.
3. Reliez chaque clé étrangère à la table qu'elle référence et réglez les extrémités des traits sur les marqueurs patte de corbeau : exactement un côté table référencée, zéro ou plusieurs côté clé étrangère, et un ou plusieurs là où une règle métier l'exige.
4. Étiquetez chaque trait avec un verbe, comme « passée par » ou « livrée à ».
5. Ajoutez un bloc Markdown du pack Base avec les décisions de conception.

L'exemple utilise 8 nœuds de diagramme et reste dans les limites de l'offre Free. Pour voir comment un schéma s'intègre au reste d'un système, consultez l'exemple de diagramme de conception de systèmes.

FAQ

Quelle est la différence entre un diagramme ER et un diagramme de schéma de base de données ? En pratique, ils se recouvrent. Un diagramme ER peut être conceptuel et montrer entités et relations avant que les tables n'existent ; un diagramme de schéma montre les tables, colonnes et clés réelles. Le diagramme patte de corbeau de ce guide est les deux à la fois.

Chaque table doit-elle avoir une clé de substitution ? La plupart des tables y gagnent, car les clés naturelles changent. Les tables de liaison comme order_items utilisent souvent plutôt la paire de clés étrangères comme clé primaire.

Comment représenter une relation plusieurs-à-plusieurs ? Avec la table du milieu et deux traits un-à-plusieurs. Nommez la table du milieu d'après ce qu'elle représente, par exemple les lignes de commande, et non d'après les deux tables qu'elle relie.

Faut-il dessiner chaque colonne ? Dessinez les clés et les colonnes qui expliquent la conception. Laissez de côté les colonnes d'audit et techniques, sauf si le diagramme porte sur elles.

Chen ou patte de corbeau ? Chen pour les discussions conceptuelles avec des personnes qui ne raisonnent pas en tables, patte de corbeau pour tout ce qui deviendra une base de données.

Commencez gratuitement — un siège, sans carte bancaire.