Modélisation de la relation MANY-TO-MANY avec une table intermédiaire

Une relation "many-to-many", c'est quand un enregistrement dans une table peut être lié à plusieurs enregistrements dans une autre table, et inversement. Par exemple : - Un étudiant (de la table students) peut être inscrit à plusieurs cours (de la table courses). - Un cours peut être suivi par plusieurs étudiants.

Le souci, c'est que stocker ce genre de relation directement, c'est galère. C'est là qu'intervient la table intermédiaire, qui va stocker ces liens.

Exemple dans la vraie vie

Imagine que tu crées une table d'étudiants et une table de cours. Si tu essaies d'ajouter dans chaque table une colonne pour stocker toutes les données liées, c'est le bazar :

• Dans la table students, il faudrait stocker la liste de tous les cours auxquels l'étudiant est inscrit. Mais comment stocker une liste ? Avec des virgules ? Dans un array ? C'est galère à requêter.
• Dans la table courses, il faudrait stocker la liste des étudiants, et là c'est carrément la migraine.

Donc la bonne solution, c'est de créer une troisième table qui va stocker les liens entre étudiants et cours.

La table intermédiaire : notre sauveur !

La table intermédiaire (parfois appelée table de liaison) règle tous les soucis. Elle contient deux références externes :

• Une clé étrangère vers la table students.
• Une clé étrangère vers la table courses.

Chaque ligne dans cette table crée un lien entre un étudiant précis et un cours précis.

Création des tables pour une relation "many-to-many"

On passe à la pratique ! Voilà comment on peut créer les tables pour gérer les liens entre étudiants et cours :

Étape 1 : Création de la table students

Voici notre table pour les étudiants. On y stocke les identifiants uniques des étudiants et leurs prénoms.

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

• student_id — c'est l'identifiant unique de l'étudiant (auto-incrémenté, merci SERIAL!).
• name — le prénom de l'étudiant.

Étape 2 : Création de la table courses

Maintenant on crée la table pour les cours. On y stocke les identifiants uniques des cours et leurs titres.

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

• course_id — identifiant unique du cours.
• title — nom du cours.

Étape 3 : Création de la table intermédiaire enrollments

Maintenant on crée notre fameuse table de liaison. Elle contient deux colonnes, chacune étant une clé étrangère pointant vers les tables correspondantes.

CREATE TABLE enrollments (
    student_id INT REFERENCES students(student_id),
    course_id INT REFERENCES courses(course_id),
    PRIMARY KEY (student_id, course_id)
);

Décryptons la structure :

• student_id — clé étrangère pointant vers student_id dans la table students.
• course_id — clé étrangère pointant vers course_id dans la table courses.
• PRIMARY KEY (student_id, course_id) — la clé primaire est composée des deux clés étrangères. Ça garantit que chaque lien est unique.

Insertion de données

On va ajouter un peu de données pour voir comment ça marche.

Étape 1 : Ajout des étudiants

INSERT INTO students (name) VALUES
('Alice'),
('Bob'),
('Charlie');

Résultat :

Étape 2 : Ajout des cours

INSERT INTO courses (title) VALUES
('Mathematics'),
('History'),
('Biology');

Résultat :

Étape 3 : Ajout des lignes dans enrollments

Maintenant on inscrit les étudiants aux cours. Par exemple :

• Alice est inscrite à Mathematics et History.
• Bob est inscrit seulement à Biology.
• Charlie est inscrit à tous les cours.

INSERT INTO enrollments (student_id, course_id) VALUES
(1, 1), -- Alice sur Mathematics
(1, 2), -- Alice sur History
(2, 3), -- Bob sur Biology
(3, 1), -- Charlie sur Mathematics
(3, 2), -- Charlie sur History
(3, 3); -- Charlie sur Biology

Résultat :

Requêtes pour la relation "many-to-many"

Maintenant qu'on a des données, on va en profiter !

Comment trouver tous les cours auxquels un étudiant est inscrit ?

Par exemple, pour savoir à quels cours est inscrite Alice (ID = 1), fais cette requête :

SELECT c.title
FROM courses c
JOIN enrollments e ON c.course_id = e.course_id
WHERE e.student_id = 1;

Résultat :

Comment trouver tous les étudiants inscrits à un cours précis ?

Disons qu'on veut savoir qui est inscrit au cours Mathematics (ID = 1) :

SELECT s.name
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
WHERE e.course_id = 1;

Résultat :

Comment trouver les étudiants et leurs cours ?

Pour avoir la vue complète de qui est inscrit à quoi, fais cette requête :

SELECT s.name AS student, c.title AS course
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
JOIN courses c ON e.course_id = c.course_id;

Résultat :

La table enrollments rend notre schéma super flexible — on peut ajouter ou supprimer des liens entre étudiants et cours sans toucher aux tables principales. Grâce aux requêtes JOIN, c'est facile de trouver les infos qu'on veut, genre qui est inscrit à quel cours. Et les clés étrangères surveillent automatiquement qu'il n'y ait pas d'erreurs — par exemple, personne ne peut inscrire un étudiant à un cours qui n'existe pas.

Erreurs classiques dans les relations "many-to-many"

Absence de contrainte d'unicité : Si tu ne mets pas de PRIMARY KEY, tu peux ajouter plusieurs fois le même lien par accident.

Violation de l'intégrité des données : Essayer d'insérer une ligne avec un student_id ou un course_id qui n'existe pas va planter.

Mauvais ordre de suppression des données : Si tu supprimes d'abord un cours de courses, les lignes dans enrollments vont rester "orphelines". Pour éviter ça, utilise ON DELETE CASCADE dans la définition des clés étrangères.