Modélisation des relations entre les tables pour la normalisation

Aujourd’hui, on va plonger dans un sujet super important : la modélisation des relations entre les tables. Parce que la normalisation, ce n’est pas juste des données atomiques et l’élimination des redondances, c’est aussi la création de bonnes relations entre les tables.

Si une base de données, c’est un système organisé pour stocker des infos, alors les relations entre les tables, c’est les ponts logiques qui montrent comment les données interagissent entre elles. Imagine une bibliothèque où les infos sur les livres sont stockées à part des infos sur les auteurs, mais chaque livre “connaît” son auteur via une relation spéciale. Ou pense à un site e-commerce : les données sur les produits existent indépendamment des infos sur les clients, mais quand quelqu’un passe une commande, le système relie un client précis à des produits précis via la table des commandes.

Dans une clinique médicale, les patients sont liés à leurs dossiers médicaux, les médecins à leur planning de rendez-vous, et les médicaments aux prescriptions. Ces relations aident le système à comprendre quelles infos sont liées à quoi, sans dupliquer les données pour rien.

Les types de relations de base fonctionnent comme dans la vraie vie : un passeport appartient à une seule personne (un-à-un), un prof peut donner plusieurs cours (un-à-plusieurs), et les étudiants peuvent s’inscrire à plusieurs matières, chaque matière étant suivie par plusieurs étudiants (plusieurs-à-plusieurs).

Un-à-un (1:1)

C’est une relation où un enregistrement dans la table “A” correspond à un seul enregistrement dans la table “B”. Par exemple, on a les tables “Employés” et “Données de passeport”. Un employé peut avoir un seul passeport, et chaque passeport appartient à un seul employé.

Exemple :

Employés

Données de passeport

Ici, la relation se fait via la foreign key employé_id, qui pointe vers l’id dans la table “Employés”.

Un-à-plusieurs (1:N)

C’est le type de relation le plus courant. Ici, chaque enregistrement de la table “A” peut être lié à plusieurs enregistrements dans la table “B”, mais chaque enregistrement de la table “B” est lié à un seul enregistrement de la table “A”. Par exemple, on a les tables “Professeurs” et “Cours”. Un prof peut donner plusieurs cours.

Exemple :

Professeurs

Cours

La relation est créée via la foreign key professeur_id dans la table “Cours”.

Plusieurs-à-plusieurs (M:N)

Quand t’as plein de tout, c’est fun mais c’est compliqué. Ici, chaque enregistrement dans la table “A” peut être lié à plusieurs enregistrements de la table “B”, et inversement. Par exemple, les étudiants peuvent s’inscrire à plusieurs cours, et chaque cours peut avoir plusieurs étudiants.

Exemple :

Étudiants

Cours

Pour faire le lien, il nous faut une table intermédiaire qui va stocker les correspondances entre étudiants et cours :

Inscriptions

Modélisation des relations avec des foreign keys

Une foreign key, c’est une colonne (ou un groupe de colonnes) qui pointe vers la colonne de primary key dans une autre table. C’est la base pour construire des relations entre les tables.

Exemple de foreign key :

CREATE TABLE Cours (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(255)
);

CREATE TABLE Inscriptions (
    id SERIAL PRIMARY KEY,
    étudiant_id INT,
    cours_id INT,
    FOREIGN KEY (cours_id) REFERENCES Cours(id)
);

Comment éviter les erreurs quand tu conçois des foreign keys ? D’abord, il faut vérifier que les types de données entre les colonnes de la foreign key et de la primary key sont les mêmes — sinon la base va juste refuser de créer la relation. Et il faut aussi réfléchir à ce qui doit se passer quand tu supprimes des enregistrements. Par exemple, si tu supprimes une ligne de la table parent, qu’est-ce qu’on fait des lignes enfants ? Une option populaire : utiliser ON DELETE CASCADE, comme ça les données liées sont supprimées automatiquement avec l’enregistrement principal. Ça aide à garder tout propre et à éviter les “liens orphelins”.

Implémentation de la relation “plusieurs-à-plusieurs”

Prenons un exemple : on a des étudiants et des cours. Un étudiant peut être inscrit à plusieurs cours, et un cours peut avoir plusieurs étudiants inscrits. Pour faire la relation M:N, on va créer trois tables : Étudiants, Cours et Inscriptions.

CREATE TABLE Étudiants (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(255)
);

CREATE TABLE Cours (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(255)
);

CREATE TABLE Inscriptions (
    id SERIAL PRIMARY KEY,
    étudiant_id INT,
    cours_id INT,
    FOREIGN KEY (étudiant_id) REFERENCES Étudiants(id),
    FOREIGN KEY (cours_id) REFERENCES Cours(id)
);

Maintenant, on peut ajouter des lignes dans la table Inscriptions pour lier les étudiants et les cours.

Exercice pratique

Crée la structure d’une base de données pour un système de gestion de cours. Tu dois avoir les tables Étudiants, Cours et Inscriptions. Implémente toutes les relations entre les tables. Ensuite, ajoute des exemples de données sur les étudiants, les cours et leurs inscriptions. Regarde comment on fait ça.

1. On crée les tables :

CREATE TABLE Étudiants (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(255)
);

CREATE TABLE Cours (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(255)
);

CREATE TABLE Inscriptions (
    id SERIAL PRIMARY KEY,
    étudiant_id INT,
    cours_id INT,
    FOREIGN KEY (étudiant_id) REFERENCES Étudiants(id),
    FOREIGN KEY (cours_id) REFERENCES Cours(id)
);

2. On insère des données :

INSERT INTO Étudiants (nom) VALUES ('Otto Lin'), ('Maria Chi');
INSERT INTO Cours (nom) VALUES ('Bases de SQL'), ('Administration BD');
INSERT INTO Inscriptions (étudiant_id, cours_id) VALUES (1, 1), (1, 2), (2, 1);

3. On vérifie les données :

SELECT
    Étudiants.nom AS étudiant, 
    Cours.nom AS cours
FROM Inscriptions
JOIN Étudiants ON Inscriptions.étudiant_id = Étudiants.id
JOIN Cours ON Inscriptions.cours_id = Cours.id;

Résultat :

Les difficultés et particularités de la modélisation des relations

Quand tu modélises les relations entre les tables, tu peux tomber sur des galères comme :

• Des erreurs à la suppression de données (genre t’as des lignes dans la table enfant qui dépendent d’une ligne dans la table parent).
• La perf des requêtes quand t’as beaucoup de données. Les relations M:N sont particulièrement “gourmandes”, parce qu’elles demandent des jointures en plus.

Pour régler ces soucis, tu peux :

• Mettre des index sur les foreign keys
• Penser la structure de la base à l’avance.
• Faire un équilibre entre normalisation et performance.

On a vu la modélisation des relations entre les tables à un niveau super basique et on l’a appliqué en créant la structure d’une base pour un système de gestion de cours. J’aurais bien aimé prendre un gros exemple, mais franchement j’ai pas trouvé comment faire. Un gros exemple, c’est vite compliqué et chiant. Et ça sert pas à grand-chose. Je vais essayer d’y revenir vers la fin du cours.