Les exemples SQL utilisent PostgreSQL à moins qu'un autre dialecte ne soit nommé. Les extraits courts supposent que leurs tables référencées existent déjà. Validez le DDL dans votre base de données cible avant de l'appliquer.
Comprendre les modèles de relations SQL
Les bases de données relationnelles tirent leur puissance des connexions entre les tables. Que vous conceviez une petite application ou un entrepôt de données d'entreprise, le choix du modèle de relation correct détermine l'efficacité avec laquelle vous pouvez stocker, interroger et maintenir vos données. Les trois modèles fondamentaux —un-à-un, un-à-plusieurs, et plusieurs-à-plusieurs— couvrent de nombreuses associations courantes ; les relations récursives et de plus haut degré nécessitent une modélisation supplémentaire.
Dans ce guide, nous décomposons chaque modèle avec des exemples PostgreSQL, explorons le rôle des tables de jonction et montrons comment ces relations apparaissent sur un diagramme entité-relation (ERD). Si vous souhaitez visualiser votre propre schéma dès maintenant, ouvrez Éditeur Erd Go et collez le code pris en charge CREATE TABLE définitions pour générer un diagramme.
Relations un-à-plusieurs
A un-à-plusieurs La relation (1:N) est le modèle le plus courant dans la conception de bases de données. Cela signifie qu'une seule ligne dans le parent la table peut être référencée par de nombreuses lignes dans le enfant table, et une clé étrangère enfant non nulle pointe vers un parent. Une clé étrangère nullable autorise un enfant sans parent.
Exemple classique : Départements et Employés
Une entreprise possède de nombreux départements, et chaque département contient plusieurs employés. Cependant, chaque employé appartient à un seul département à un moment donné.
CREATE TABLE departments (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
location VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
department_id INT NOT NULL,
hire_date DATE DEFAULT CURRENT_DATE,
CONSTRAINT fk_department
FOREIGN KEY (department_id)
REFERENCES departments (id)
ON DELETE RESTRICT
ON UPDATE CASCADE
);Détails d'implémentation clés
- La clé étrangère (
department_id) se trouve toujours sur le plusieurs côté — leemployeestable dans ce cas. ON DELETE RESTRICTévite de supprimer accidentellement un département auquel des employés sont toujours affectés.ON UPDATE CASCADEgarantit que si la clé primaire d'un département change, toutes les lignes d'employés sont mises à jour automatiquement.- L'ajout d'un index sur
department_idpeut aider les jointures et les filtres de département ; confirmez le bénéfice avec des données représentatives et un plan d'exécution.
Interrogation de données un-à-plusieurs
-- All employees in the Engineering department
SELECT e.first_name, e.last_name, d.name AS department
FROM employees e
JOIN departments d ON d.id = e.department_id
WHERE d.name = 'Engineering';
-- Count employees per department
SELECT d.name, COUNT(e.id) AS headcount
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
GROUP BY d.id, d.name
ORDER BY headcount DESC;D'autres exemples courants de un-à-plusieurs incluent auteurs → articles de blog, clients → commandes, et catégories → produits.
Relations plusieurs-à-plusieurs
A plusieurs-à-plusieurs Une relation (M:N) existe lorsque les lignes sur les deux côtés peuvent être associés à plusieurs lignes de l'autre côté. Les bases de données relationnelles ne peuvent pas représenter cela directement avec une seule clé étrangère, nous introduisons donc une table de jonction (également appelé pont, jointure, ou associative table).
Exemple classique : Étudiants et Cours
Un étudiant peut s'inscrire à de nombreux cours, et chaque cours peut compter de nombreux étudiants. La table de jonction enrollments se situe entre eux et détient les clés étrangères des deux côtés.
CREATE TABLE students (
id SERIAL PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
enrolled_on DATE DEFAULT CURRENT_DATE
);
CREATE TABLE courses (
id SERIAL PRIMARY KEY,
title VARCHAR(200) NOT NULL,
credits SMALLINT NOT NULL DEFAULT 3,
department VARCHAR(100)
);
CREATE TABLE enrollments (
student_id INT NOT NULL REFERENCES students (id) ON DELETE CASCADE,
course_id INT NOT NULL REFERENCES courses (id) ON DELETE CASCADE,
enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
grade CHAR(2),
PRIMARY KEY (student_id, course_id)
);Pourquoi une table de jonction ?
- Le clé primaire composite (
student_id, course_id) autorise une ligne par paire étudiant-cours. Si des inscriptions répétées d'un trimestre à l'autre sont nécessaires, incluez un trimestre dans la clé métier ou modélisez chaque inscription séparément. - Vous pouvez attacher des attributs supplémentaires à la relation elle-même, tels que
gradeouenrolled_at, qui n'appartiennent à aucune entité en soi. ON DELETE CASCADEsur les deux clés étrangères signifie que la suppression d'un étudiant ou d'un cours nettoie automatiquement les lignes d'inscription.
Interrogation de données plusieurs-à-plusieurs
-- All courses for a specific student
SELECT c.title, c.credits, e.grade
FROM enrollments e
JOIN courses c ON c.id = e.course_id
WHERE e.student_id = 42;
-- Students enrolled in 'Database Systems'
SELECT s.full_name, s.email
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id
WHERE c.title = 'Database Systems';Des exemples réels de many-to-many incluent utilisateurs ↔ rôles, produits ↔ étiquettes, acteurs ↔ films, et commandes ↔ produits (via un order_items table de jonction qui stocke également la quantité et le prix).
Relations un-à-un
A un-à-un Une relation (1:1) signifie que chaque ligne de la table A correspond à au plus une ligne de la table B, et vice-versa. Ce modèle est moins courant, mais utile pour séparer des colonnes sensibles ou rarement consultées dans une table distincte.
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(60) UNIQUE NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL
);
CREATE TABLE user_profiles (
user_id INT PRIMARY KEY REFERENCES users (id) ON DELETE CASCADE,
bio TEXT,
avatar_url VARCHAR(500),
birthdate DATE,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);Remarquez que user_profiles.user_id est à la fois le clé primaire et le clé étrangère. Cette contrainte applique la correspondance un-à-un au niveau de la base de données — chaque utilisateur peut avoir au plus une ligne de profil.
Apparence des relations dans les diagrammes ERD
Un diagramme entité-relation utilise des lignes et des symboles entre les tables pour transmettre la cardinalité en un coup d'œil. Les deux notations les plus populaires sont Crow’s Foot et Chen notation.
| Relation | Symbole de Crow’s Foot | Comment lire |
|---|---|---|
| Un-à-un | ||—|| | Exactement un de chaque côté |
| Un-à-plusieurs | ||—< | Un à gauche, plusieurs à droite (extrémité patte de corbeau) |
| Plusieurs-à-plusieurs | >—< | Plusieurs des deux côtés ; implémenté via une table de jonction |
Ces symboles textuels sont des abréviations pour la cardinalité maximale. L'optionalité nécessite un marqueur zéro ou un supplémentaire ; une clé étrangère seule n'exige pas qu'un parent ait des enfants.
Lorsque vous modélisez une relation plusieurs-à-plusieurs dans un outil ERD, vous voyez généralement trois boîtes — deux tables d'entités et la table de jonction entre elles, chacune connectée par une ligne un-à-plusieurs.
Collez l'un des exemples SQL de cet article dans le Éditeur Erd Go pour voir le diagramme généré automatiquement, complet avec des flèches de clés étrangères et des indicateurs de cardinalité.
Choisir le bon modèle de relation
Sélectionner la mauvaise cardinalité entraîne une duplication des données, des requêtes complexes et des bugs d'intégrité. Utilisez ce cadre de décision :
- Une ligne du côté A peut-elle être liée à plusieurs lignes du côté B, mais pas l'inverse ? → Un-à-Plusieurs. Placez la clé étrangère du côté B.
- Des lignes des deux côtés peuvent-elles être liées à plusieurs lignes de l'autre côté ? → Plusieurs-à-Plusieurs. Créez une table de jonction avec une clé composite.
- La mise en correspondance est-elle strictement de 1:1 et avez-vous besoin d'une séparation des tables ? → Un-à-Un. Utilisez la même clé primaire que la clé étrangère.
En cas de doute, esquissez d'abord votre schéma. Un ERD rapide révèle les problèmes de relation bien avant de rencontrer des erreurs de contrainte en production. Essayez de construire votre diagramme dans le Éditeur Erd Go— cela prend des secondes et peut épargner des heures de débogage.



