Modéliser une base de données : du cahier des charges au schéma MySQL

Beaucoup de développeurs ouvrent phpMyAdmin et créent leurs tables au fil de l’eau, en ajoutant des colonnes quand le besoin se présente. Ça fonctionne pour un petit exercice. Mais sur un vrai projet, on se retrouve vite avec une adresse de client copiée dans chaque commande, une colonne categories qui contient « php, mysql, web », et des requêtes de plus en plus tordues pour compenser.

Modéliser une base de données avant d’écrire la moindre requête, c’est prendre une heure pour en économiser des dizaines. Dans ce guide, on suit la méthode utilisée en entreprise et enseignée dans les formations (inspirée de Merise), sur un cas concret : une plateforme de cours en ligne.

Étape 1 : analyser le cahier des charges

Tout commence par le besoin, exprimé en langage courant. Voici celui de notre projet :

Lis-le plusieurs fois en soulignant :

• les noms importants, qui deviendront des entités ou des attributs : cours, catégorie, formateur, chapitre, élève, inscription, avis, note ;
• les verbes, qui révèlent les liens : appartient à, rédigé par, découpé en, s’inscrivent, laisser ;
• les quantités et les contraintes : un seul avis par cours, note de 1 à 5, ordre précis.

Étape 2 : le dictionnaire de données

Le dictionnaire de données liste toutes les informations à stocker, avec leur type et leurs contraintes. C’est un simple tableau, mais il oblige à n’oublier aucune donnée et à se mettre d’accord sur les formats.

Étape 3 : les règles de gestion

Les règles de gestion traduisent le cahier des charges en phrases précises, sans ambiguïté. Elles serviront à fixer les cardinalités.

• RG1 : un cours appartient à une et une seule catégorie ; une catégorie contient zéro, un ou plusieurs cours.
• RG2 : un cours est rédigé par un et un seul formateur ; un formateur rédige zéro, un ou plusieurs cours.
• RG3 : un cours contient un ou plusieurs chapitres ; un chapitre appartient à un et un seul cours.
• RG4 : un utilisateur peut s’inscrire à zéro, un ou plusieurs cours ; un cours peut avoir zéro, un ou plusieurs inscrits. Chaque inscription a une date et une progression.
• RG5 : un utilisateur peut laisser au plus un avis par cours.
• RG6 : formateurs et élèves sont des utilisateurs ; un formateur peut aussi s’inscrire à un cours.

Étape 4 : le MCD (modèle conceptuel de données)

Le MCD représente les données de façon indépendante de toute technique : pas encore de tables, pas encore de SQL. Il se compose de trois éléments.

Les entités

Une entité est un objet concret ou abstrait, identifiable de façon unique. Chacune possède un identifiant (souligné dans les schémas Merise) et des attributs.

Note que le formateur et l’élève ne sont pas deux entités séparées : ce sont des utilisateurs avec un rôle différent (RG6). Si on avait créé deux tables, un formateur qui suit un cours devrait exister deux fois, avec deux mots de passe.

Les associations

Une association est un lien entre entités, généralement exprimé par un verbe :

• APPARTENIR (COURS → CATEGORIE) ;
• RÉDIGER (UTILISATEUR → COURS) ;
• CONTENIR (COURS → CHAPITRE) ;
• S’INSCRIRE (UTILISATEUR ↔ COURS), qui porte les attributs date_inscription et progression ;
• ÉVALUER (UTILISATEUR ↔ COURS), qui porte les attributs note et commentaire.

Les cardinalités

Les cardinalités indiquent, pour chaque entité, combien de fois au minimum et au maximum elle participe à une association. On les lit directement dans les règles de gestion :

Comment lire « COURS 1,1 — APPARTENIR — 0,n CATEGORIE » ? Un cours appartient au minimum à 1 et au maximum à 1 catégorie ; une catégorie contient au minimum 0 et au maximum n cours.

Étape 5 : du MCD au MLD

Le MLD (modèle logique de données) traduit le MCD en tables relationnelles. La traduction suit des règles mécaniques, qu’il suffit d’appliquer :

En appliquant ces règles :

• APPARTENIR (1,1 côté COURS) → cours reçoit la clé étrangère categorie_id ;
• RÉDIGER (1,1 côté COURS) → cours reçoit la clé étrangère formateur_id ;
• CONTENIR (1,1 côté CHAPITRE) → chapitres reçoit la clé étrangère cours_id ;
• S’INSCRIRE (0,n — 0,n) → nouvelle table inscriptions(utilisateur_id, cours_id, date_inscription, progression) ;
• ÉVALUER (0,n — 0,n) → nouvelle table avis(utilisateur_id, cours_id, note, commentaire).

Le MLD complet, en notation textuelle (clé primaire en gras, clé étrangère précédée de #) :

• utilisateurs (id, email, nom, mot_de_passe, role, cree_le)
• categories (id, nom)
• cours (id, titre, slug, prix, publie_le, #categorie_id, #formateur_id)
• chapitres (id, titre, position, contenu, #cours_id)
• inscriptions (#utilisateur_id, #cours_id, inscrit_le, progression)
• avis (#utilisateur_id, #cours_id, note, commentaire, cree_le)

Étape 6 : vérifier la normalisation

La normalisation est un ensemble de règles qui éliminent les redondances et les incohérences. En pratique, on vise la troisième forme normale (3FN).

Vérifions notre modèle :

• 1FN : aucune liste dans une colonne. Les chapitres sont dans leur propre table, une ligne par chapitre. ✅
• 2FN : dans inscriptions et avis, les attributs (date, progression, note) dépendent bien du couple utilisateur + cours. ✅
• 3FN : le nom de la catégorie n’est stocké que dans categories, le nom du formateur que dans utilisateurs. ✅

Étape 7 : le script SQL MySQL

On passe au modèle physique : le script SQL adapté à MySQL 8, avec les types précis, les contraintes et les index.

CREATE DATABASE codeacademie CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE codeacademie;

CREATE TABLE utilisateurs (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email         VARCHAR(255) NOT NULL,
    nom           VARCHAR(100) NOT NULL,
    mot_de_passe  VARCHAR(255) NOT NULL,              -- empreinte password_hash(), jamais en clair
    role          ENUM('eleve', 'formateur', 'admin') NOT NULL DEFAULT 'eleve',
    cree_le       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_utilisateurs_email UNIQUE (email)
) ENGINE = InnoDB;

CREATE TABLE categories (
    id   SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nom  VARCHAR(50) NOT NULL,
    CONSTRAINT uq_categories_nom UNIQUE (nom)
) ENGINE = InnoDB;

CREATE TABLE cours (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    titre         VARCHAR(150) NOT NULL,
    slug          VARCHAR(160) NOT NULL,
    prix          DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    publie_le     DATETIME NULL,                       -- NULL = brouillon
    categorie_id  SMALLINT UNSIGNED NOT NULL,
    formateur_id  INT UNSIGNED NOT NULL,
    CONSTRAINT uq_cours_slug UNIQUE (slug),
    CONSTRAINT ck_cours_prix CHECK (prix >= 0),
    CONSTRAINT fk_cours_categorie FOREIGN KEY (categorie_id)
        REFERENCES categories (id) ON DELETE RESTRICT,
    CONSTRAINT fk_cours_formateur FOREIGN KEY (formateur_id)
        REFERENCES utilisateurs (id) ON DELETE RESTRICT
) ENGINE = InnoDB;

CREATE TABLE chapitres (
    id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cours_id  INT UNSIGNED NOT NULL,
    titre     VARCHAR(150) NOT NULL,
    position  SMALLINT UNSIGNED NOT NULL,
    contenu   MEDIUMTEXT NOT NULL,
    CONSTRAINT uq_chapitres_position UNIQUE (cours_id, position),
    CONSTRAINT ck_chapitres_position CHECK (position >= 1),
    CONSTRAINT fk_chapitres_cours FOREIGN KEY (cours_id)
        REFERENCES cours (id) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE inscriptions (
    utilisateur_id  INT UNSIGNED NOT NULL,
    cours_id        INT UNSIGNED NOT NULL,
    inscrit_le      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    progression     TINYINT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (utilisateur_id, cours_id),
    CONSTRAINT ck_inscriptions_progression CHECK (progression BETWEEN 0 AND 100),
    CONSTRAINT fk_inscriptions_utilisateur FOREIGN KEY (utilisateur_id)
        REFERENCES utilisateurs (id) ON DELETE CASCADE,
    CONSTRAINT fk_inscriptions_cours FOREIGN KEY (cours_id)
        REFERENCES cours (id) ON DELETE CASCADE,
    INDEX idx_inscriptions_cours (cours_id)
) ENGINE = InnoDB;

CREATE TABLE avis (
    utilisateur_id  INT UNSIGNED NOT NULL,
    cours_id        INT UNSIGNED NOT NULL,
    note            TINYINT UNSIGNED NOT NULL,
    commentaire     TEXT NULL,
    cree_le         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (utilisateur_id, cours_id),
    CONSTRAINT ck_avis_note CHECK (note BETWEEN 1 AND 5),
    CONSTRAINT fk_avis_utilisateur FOREIGN KEY (utilisateur_id)
        REFERENCES utilisateurs (id) ON DELETE CASCADE,
    CONSTRAINT fk_avis_cours FOREIGN KEY (cours_id)
        REFERENCES cours (id) ON DELETE CASCADE,
    INDEX idx_avis_cours (cours_id)
) ENGINE = InnoDB;

CREATE DATABASE codeacademie CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE codeacademie;

CREATE TABLE utilisateurs (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email         VARCHAR(255) NOT NULL,
    nom           VARCHAR(100) NOT NULL,
    mot_de_passe  VARCHAR(255) NOT NULL,              -- empreinte password_hash(), jamais en clair
    role          ENUM('eleve', 'formateur', 'admin') NOT NULL DEFAULT 'eleve',
    cree_le       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_utilisateurs_email UNIQUE (email)
) ENGINE = InnoDB;

CREATE TABLE categories (
    id   SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nom  VARCHAR(50) NOT NULL,
    CONSTRAINT uq_categories_nom UNIQUE (nom)
) ENGINE = InnoDB;

CREATE TABLE cours (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    titre         VARCHAR(150) NOT NULL,
    slug          VARCHAR(160) NOT NULL,
    prix          DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    publie_le     DATETIME NULL,                       -- NULL = brouillon
    categorie_id  SMALLINT UNSIGNED NOT NULL,
    formateur_id  INT UNSIGNED NOT NULL,
    CONSTRAINT uq_cours_slug UNIQUE (slug),
    CONSTRAINT ck_cours_prix CHECK (prix >= 0),
    CONSTRAINT fk_cours_categorie FOREIGN KEY (categorie_id)
        REFERENCES categories (id) ON DELETE RESTRICT,
    CONSTRAINT fk_cours_formateur FOREIGN KEY (formateur_id)
        REFERENCES utilisateurs (id) ON DELETE RESTRICT
) ENGINE = InnoDB;

CREATE TABLE chapitres (
    id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cours_id  INT UNSIGNED NOT NULL,
    titre     VARCHAR(150) NOT NULL,
    position  SMALLINT UNSIGNED NOT NULL,
    contenu   MEDIUMTEXT NOT NULL,
    CONSTRAINT uq_chapitres_position UNIQUE (cours_id, position),
    CONSTRAINT ck_chapitres_position CHECK (position >= 1),
    CONSTRAINT fk_chapitres_cours FOREIGN KEY (cours_id)
        REFERENCES cours (id) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE inscriptions (
    utilisateur_id  INT UNSIGNED NOT NULL,
    cours_id        INT UNSIGNED NOT NULL,
    inscrit_le      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    progression     TINYINT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (utilisateur_id, cours_id),
    CONSTRAINT ck_inscriptions_progression CHECK (progression BETWEEN 0 AND 100),
    CONSTRAINT fk_inscriptions_utilisateur FOREIGN KEY (utilisateur_id)
        REFERENCES utilisateurs (id) ON DELETE CASCADE,
    CONSTRAINT fk_inscriptions_cours FOREIGN KEY (cours_id)
        REFERENCES cours (id) ON DELETE CASCADE,
    INDEX idx_inscriptions_cours (cours_id)
) ENGINE = InnoDB;

CREATE TABLE avis (
    utilisateur_id  INT UNSIGNED NOT NULL,
    cours_id        INT UNSIGNED NOT NULL,
    note            TINYINT UNSIGNED NOT NULL,
    commentaire     TEXT NULL,
    cree_le         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (utilisateur_id, cours_id),
    CONSTRAINT ck_avis_note CHECK (note BETWEEN 1 AND 5),
    CONSTRAINT fk_avis_utilisateur FOREIGN KEY (utilisateur_id)
        REFERENCES utilisateurs (id) ON DELETE CASCADE,
    CONSTRAINT fk_avis_cours FOREIGN KEY (cours_id)
        REFERENCES cours (id) ON DELETE CASCADE,
    INDEX idx_avis_cours (cours_id)
) ENGINE = InnoDB;

Les choix expliqués

Tester le modèle avec de vraies requêtes

Un bon modèle se reconnaît à la simplicité des requêtes qu’il permet. Vérifions qu’on répond facilement aux besoins du cahier des charges :

-- Les cours d'une catégorie, avec le nom du formateur, la note moyenne et le nombre d'inscrits
SELECT c.titre,
       u.nom                              AS formateur,
       ROUND(AVG(a.note), 1)              AS note_moyenne,
       COUNT(DISTINCT i.utilisateur_id)   AS nb_inscrits
FROM cours AS c
JOIN utilisateurs AS u      ON u.id = c.formateur_id
JOIN categories   AS cat    ON cat.id = c.categorie_id
LEFT JOIN avis         AS a ON a.cours_id = c.id
LEFT JOIN inscriptions AS i ON i.cours_id = c.id
WHERE cat.nom = 'PHP' AND c.publie_le IS NOT NULL
GROUP BY c.id, c.titre, u.nom
ORDER BY note_moyenne DESC;

-- Le sommaire d'un cours, dans l'ordre
SELECT position, titre
FROM chapitres
WHERE cours_id = 12
ORDER BY position;

-- Les cours d'une catégorie, avec le nom du formateur, la note moyenne et le nombre d'inscrits
SELECT c.titre,
       u.nom                              AS formateur,
       ROUND(AVG(a.note), 1)              AS note_moyenne,
       COUNT(DISTINCT i.utilisateur_id)   AS nb_inscrits
FROM cours AS c
JOIN utilisateurs AS u      ON u.id = c.formateur_id
JOIN categories   AS cat    ON cat.id = c.categorie_id
LEFT JOIN avis         AS a ON a.cours_id = c.id
LEFT JOIN inscriptions AS i ON i.cours_id = c.id
WHERE cat.nom = 'PHP' AND c.publie_le IS NOT NULL
GROUP BY c.id, c.titre, u.nom
ORDER BY note_moyenne DESC;

-- Le sommaire d'un cours, dans l'ordre
SELECT position, titre
FROM chapitres
WHERE cours_id = 12
ORDER BY position;

Ces requêtes utilisent des jointures et des regroupements : si ce n’est pas encore clair pour toi, nos guides sur les jointures SQL (INNER, LEFT, RIGHT JOIN) et sur GROUP BY et HAVING détaillent chaque clause.

Les erreurs de modélisation les plus fréquentes

(¹) Exception importante : sur une facture, on copie volontairement l’adresse et le prix au moment de l’achat. C’est une donnée historique : si le client déménage ou si le prix change, la facture passée ne doit pas être modifiée.

Les outils pour modéliser une base de données

Une feuille de papier suffit pour commencer, mais quelques outils gratuits rendent le travail plus confortable :

Pour gérer ensuite ta base au quotidien, notre sélection des meilleurs outils de gestion de bases de données t’aidera à choisir.

FAQ

Conclusion

Modéliser une base de données, c’est suivre une démarche : comprendre le besoin, lister les données, écrire des règles de gestion claires, construire le MCD avec ses cardinalités, le traduire mécaniquement en MLD, vérifier la troisième forme normale, puis écrire un script SQL qui fait respecter les règles par la base elle-même grâce aux clés étrangères, aux contraintes UNIQUE et CHECK.

Cette heure de réflexion avant de coder te fera gagner un temps considérable pendant toute la vie du projet. Entraîne-toi sur le projet de notre tutoriel SQL : la base de données d’une bibliothèque, en refaisant d’abord le MCD avant de regarder la solution. Pour sécuriser les modifications qui touchent plusieurs tables, lis aussi notre guide sur les transactions SQL. La documentation MySQL sur les clés étrangères complète ce guide.