Modelisation de base de donnees : Relations et cles primaires/etrangeres

Estimated reading time: 13 minutes

Introduction

La modelisation de base de donnees est l’art de structurer vos donnees pour qu’elles soient coherentes, performantes et evolutives. Une bonne modelisation vous evitera des heures de refactoring et des problemes de performance a long terme.

Introduction
Concepts fondamentaux
- Qu’est-ce qu’une base de donnees relationnelle ?
- Terminologie essentielle
Les cles : Fondement des relations
Types de relations
Normalisation des donnees
Diagrammes Entite-Relation (ERD)
- Notation de base
- Exemple de schema complet
Strategies de migration
Bonnes pratiques de modelisation
Conclusion

Dans cet article, vous apprendrez :

Les concepts fondamentaux de la modelisation relationnelle
Comment identifier les entites et leurs attributs
Les differents types de relations entre tables
L’utilisation des cles primaires et etrangeres
Les regles de normalisation pour eviter la redondance
Les bonnes pratiques de conception

Concepts fondamentaux

Qu’est-ce qu’une base de donnees relationnelle ?

Une base de donnees relationnelle organise les donnees en tables (aussi appelees relations). Chaque table represente un type d’entite (clients, produits, commandes) et contient des lignes (enregistrements) et des colonnes (attributs).

Table: clients
+----+------------------+--------+---------+
| id | email            | nom    | prenom  |
+----+------------------+--------+---------+
| 1  | jean@email.com   | Dupont | Jean    |
| 2  | marie@email.com  | Martin | Marie   |
| 3  | paul@email.com   | Durand | Paul    |
+----+------------------+--------+---------+

Terminologie essentielle

Terme	Definition	Exemple
Entite	Un objet ou concept du monde reel	Client, Produit, Commande
Attribut	Une caracteristique d’une entite	Nom, Prix, Date
Tuple/Enregistrement	Une ligne dans une table	Un client specifique
Schema	La structure de la base de donnees	Definition des tables et relations
Domaine	L’ensemble des valeurs valides pour un attribut	Entier positif pour un age

Les cles : Fondement des relations

Cle primaire (PRIMARY KEY)

La cle primaire identifie de maniere unique chaque enregistrement d’une table. Elle possede deux proprietes essentielles :

Unicite : Deux lignes ne peuvent pas avoir la meme valeur de cle primaire

Non-nullite : La cle primaire ne peut jamais etre NULL

-- Cle primaire simple avec auto-increment
CREATE TABLE clients (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    nom VARCHAR(100) NOT NULL,
    prenom VARCHAR(100) NOT NULL
);

-- Cle primaire explicite en fin de definition
CREATE TABLE produits (
    id INT UNSIGNED AUTOINCREMENT,
    sku VARCHAR(50) NOT NULL,
    nom VARCHAR(255) NOT NULL,
    prix DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uksku (sku)
);

-- Cle primaire composite (plusieurs colonnes)
CREATE TABLE commandeproduits (
    commandeid INT UNSIGNED NOT NULL,
    produitid INT UNSIGNED NOT NULL,
    quantite INT UNSIGNED NOT NULL DEFAULT 1,
    prixunitaire DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (commandeid, produitid)
);

Types de cles primaires

1. Cle naturelle

Utilise un attribut metier existant (email, numero de securite sociale, ISBN).

-- Cle naturelle : le numero ISBN est unique par nature
CREATE TABLE livres (
    isbn VARCHAR(13) PRIMARY KEY,
    titre VARCHAR(255) NOT NULL,
    auteur VARCHAR(255) NOT NULL,
    prix DECIMAL(8, 2) NOT NULL
);

Avantages :

Signification metier immediate

Pas besoin de colonne supplementaire

Inconvenients :

Peut changer (email modifie)

Prend plus d’espace dans les index

Jointures plus lentes

2. Cle de substitution (Surrogate Key)

Un identifiant artificiel cree par le systeme (auto-increment, UUID).

-- Cle de substitution avec auto-increment
CREATE TABLE articles (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    titre VARCHAR(255) NOT NULL,
    contenu TEXT,
    createdat TIMESTAMP DEFAULT CURRENTTIMESTAMP
);

-- Cle de substitution avec UUID
CREATE TABLE sessions (
    id CHAR(36) PRIMARY KEY,  -- UUID format: 550e8400-e29b-41d4-a716-446655440000
    userid INT UNSIGNED NOT NULL,
    data JSON,
    expiresat TIMESTAMP NOT NULL
);

-- Insertion avec UUID
INSERT INTO sessions (id, userid, data, expiresat)
VALUES (UUID(), 1, '{"cart": []}', DATEADD(NOW(), INTERVAL 24 HOUR));

Avantages :

Jamais de modification necessaire

Performance optimale (entier court)

Independant de la logique metier

Inconvenients :

Aucune signification metier

Necessite une colonne supplementaire

Recommandation

Utilisez des cles de substitution (INT AUTOINCREMENT ou UUID) comme cle primaire, et ajoutez des contraintes UNIQUE sur les cles naturelles :

CREATE TABLE utilisateurs (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    username VARCHAR(50) NOT NULL,
    -- ... autres colonnes
    UNIQUE KEY ukemail (email),
    UNIQUE KEY ukusername (username)
);

Cle etrangere (FOREIGN KEY)

Une cle etrangere etablit un lien entre deux tables en referant la cle primaire d’une autre table.

CREATE TABLE categories (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    nom VARCHAR(100) NOT NULL,
    description TEXT
);

CREATE TABLE produits (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    categorieid INT UNSIGNED,  -- Cle etrangere
    nom VARCHAR(255) NOT NULL,
    prix DECIMAL(10, 2) NOT NULL,

    -- Declaration de la contrainte de cle etrangere
    FOREIGN KEY (categorieid)
        REFERENCES categories(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

Actions referentielles

Les actions referentielles definissent le comportement lors de la modification ou suppression d’un enregistrement parent :

-- ON DELETE : Que faire quand l'enregistrement parent est supprime ?
-- ON UPDATE : Que faire quand la cle primaire parent est modifiee ?

-- Option 1: CASCADE - Propager l'action
FOREIGN KEY (categorieid)
    REFERENCES categories(id)
    ON DELETE CASCADE      -- Supprime les produits si la categorie est supprimee
    ON UPDATE CASCADE      -- Met a jour categorieid si l'id categorie change

-- Option 2: SET NULL - Mettre la cle etrangere a NULL
FOREIGN KEY (categorieid)
    REFERENCES categories(id)
    ON DELETE SET NULL     -- Met categorieid a NULL si la categorie est supprimee
    ON UPDATE SET NULL

-- Option 3: RESTRICT / NO ACTION - Bloquer l'operation (defaut)
FOREIGN KEY (clientid)
    REFERENCES clients(id)
    ON DELETE RESTRICT     -- Empeche la suppression si des commandes existent
    ON UPDATE RESTRICT

-- Option 4: SET DEFAULT - Mettre une valeur par defaut (pas supporte partout)
FOREIGN KEY (statutid)
    REFERENCES statuts(id)
    ON DELETE SET DEFAULT

Exemple complet avec contraintes

-- Schema e-commerce complet

CREATE TABLE clients (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    nom VARCHAR(100) NOT NULL,
    prenom VARCHAR(100) NOT NULL,
    createdat TIMESTAMP DEFAULT CURRENTTIMESTAMP,
    UNIQUE KEY ukemail (email)
) ENGINE=InnoDB;

CREATE TABLE adresses (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    clientid INT UNSIGNED NOT NULL,
    type ENUM('facturation', 'livraison') DEFAULT 'livraison',
    rue VARCHAR(255) NOT NULL,
    ville VARCHAR(100) NOT NULL,
    codepostal VARCHAR(10) NOT NULL,
    pays VARCHAR(50) DEFAULT 'France',
    estprincipale BOOLEAN DEFAULT FALSE,

    FOREIGN KEY (clientid)
        REFERENCES clients(id)
        ON DELETE CASCADE,

    INDEX idxclient (clientid),
    INDEX idxtype (type)
) ENGINE=InnoDB;

CREATE TABLE categories (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    parentid INT UNSIGNED NULL,
    nom VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL,
    niveau INT UNSIGNED DEFAULT 0,

    FOREIGN KEY (parentid)
        REFERENCES categories(id)
        ON DELETE SET NULL,

    UNIQUE KEY ukslug (slug),
    INDEX idxparent (parentid)
) ENGINE=InnoDB;

CREATE TABLE produits (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    categorieid INT UNSIGNED,
    sku VARCHAR(50) NOT NULL,
    nom VARCHAR(255) NOT NULL,
    description TEXT,
    prix DECIMAL(10, 2) NOT NULL CHECK (prix >= 0),
    stock INT UNSIGNED DEFAULT 0,
    actif BOOLEAN DEFAULT TRUE,
    createdat TIMESTAMP DEFAULT CURRENTTIMESTAMP,
    updatedat TIMESTAMP DEFAULT CURRENTTIMESTAMP ON UPDATE CURRENTTIMESTAMP,

    FOREIGN KEY (categorieid)
        REFERENCES categories(id)
        ON DELETE SET NULL,

    UNIQUE KEY uksku (sku),
    INDEX idxcategorie (categorieid),
    INDEX idxactif (actif),
    INDEX idxprix (prix)
) ENGINE=InnoDB;

CREATE TABLE commandes (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    clientid INT UNSIGNED NOT NULL,
    adressefacturationid INT UNSIGNED NOT NULL,
    adresselivraisonid INT UNSIGNED NOT NULL,
    numero VARCHAR(20) NOT NULL,
    total DECIMAL(10, 2) NOT NULL DEFAULT 0,
    statut ENUM('brouillon', 'validee', 'payee', 'expediee', 'livree', 'annulee')
        DEFAULT 'brouillon',
    createdat TIMESTAMP DEFAULT CURRENTTIMESTAMP,
    updatedat TIMESTAMP DEFAULT CURRENTTIMESTAMP ON UPDATE CURRENTTIMESTAMP,

    FOREIGN KEY (clientid)
        REFERENCES clients(id)
        ON DELETE RESTRICT,
    FOREIGN KEY (adressefacturationid)
        REFERENCES adresses(id)
        ON DELETE RESTRICT,
    FOREIGN KEY (adresselivraisonid)
        REFERENCES adresses(id)
        ON DELETE RESTRICT,

    UNIQUE KEY uknumero (numero),
    INDEX idxclient (clientid),
    INDEX idxstatut (statut),
    INDEX idxdate (createdat)
) ENGINE=InnoDB;

CREATE TABLE commandelignes (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    commandeid INT UNSIGNED NOT NULL,
    produitid INT UNSIGNED NOT NULL,
    quantite INT UNSIGNED NOT NULL DEFAULT 1 CHECK (quantite > 0),
    prixunitaire DECIMAL(10, 2) NOT NULL,
    -- Snapshot des infos produit au moment de la commande
    produitnom VARCHAR(255) NOT NULL,
    produitsku VARCHAR(50) NOT NULL,

    FOREIGN KEY (commandeid)
        REFERENCES commandes(id)
        ON DELETE CASCADE,
    FOREIGN KEY (produitid)
        REFERENCES produits(id)
        ON DELETE RESTRICT,

    UNIQUE KEY ukcommandeproduit (commandeid, produitid),
    INDEX idxproduit (produitid)
) ENGINE=InnoDB;

Types de relations

Relation Un-a-Un (1:1)

Chaque enregistrement d’une table est associe a au plus un enregistrement d’une autre table.

Exemple : Un utilisateur a un profil detaille

CREATE TABLE utilisateurs (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    passwordhash VARCHAR(255) NOT NULL,
    actif BOOLEAN DEFAULT TRUE,
    createdat TIMESTAMP DEFAULT CURRENTTIMESTAMP
);

CREATE TABLE profils (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    utilisateurid INT UNSIGNED NOT NULL UNIQUE,  -- UNIQUE garantit le 1:1
    bio TEXT,
    siteweb VARCHAR(255),
    avatarurl VARCHAR(255),
    datenaissance DATE,

    FOREIGN KEY (utilisateurid)
        REFERENCES utilisateurs(id)
        ON DELETE CASCADE
);

-- Alternative : cle primaire = cle etrangere
CREATE TABLE profilsv2 (
    utilisateurid INT UNSIGNED PRIMARY KEY,  -- PK et FK en meme temps
    bio TEXT,
    siteweb VARCHAR(255),
    avatarurl VARCHAR(255),

    FOREIGN KEY (utilisateurid)
        REFERENCES utilisateurs(id)
        ON DELETE CASCADE
);

Quand utiliser une relation 1:1 ?

Separer les donnees frequemment accedees des donnees rarement utilisees

Separer les donnees sensibles (informations personnelles)

Heriter d’une table de base (specialisation)

Relation Un-a-Plusieurs (1:N)

Un enregistrement d’une table peut etre associe a plusieurs enregistrements d’une autre table.

Exemple : Un client a plusieurs commandes

CREATE TABLE clients (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    nom VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE commandes (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    clientid INT UNSIGNED NOT NULL,  -- Un client -> Plusieurs commandes
    numero VARCHAR(20) NOT NULL UNIQUE,
    total DECIMAL(10, 2) DEFAULT 0,
    createdat TIMESTAMP DEFAULT CURRENTTIMESTAMP,

    FOREIGN KEY (clientid) REFERENCES clients(id) ON DELETE RESTRICT,
    INDEX idxclient (clientid)
);

clients (1)                    commandes (N)
+----+--------+               +----+-----------+--------+
| id | nom    |               | id | clientid | numero |
+----+--------+               +----+-----------+--------+
| 1  | Dupont |  <-----+      | 1  | 1         | C001   |
| 2  | Martin |        +----> | 2  | 1         | C002   |
+----+--------+        +----> | 3  | 1         | C003   |
                              | 4  | 2         | C004   |
                              +----+-----------+--------+

Relation Plusieurs-a-Plusieurs (N:N)

Plusieurs enregistrements d’une table peuvent etre associes a plusieurs enregistrements d’une autre table. Cette relation necessite une table de liaison (table pivot, table d’association).

Exemple : Des etudiants suivent des cours, un cours a plusieurs etudiants

-- Tables principales
CREATE TABLE etudiants (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    nom VARCHAR(100) NOT NULL,
    prenom VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE cours (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    code VARCHAR(20) NOT NULL UNIQUE,
    nom VARCHAR(255) NOT NULL,
    credits INT UNSIGNED NOT NULL DEFAULT 3
);

-- Table de liaison
CREATE TABLE inscriptions (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    etudiantid INT UNSIGNED NOT NULL,
    coursid INT UNSIGNED NOT NULL,
    dateinscription DATE DEFAULT (CURRENTDATE),
    note DECIMAL(4, 2),
    statut ENUM('inscrit', 'encours', 'complete', 'abandonne') DEFAULT 'inscrit',

    FOREIGN KEY (etudiantid) REFERENCES etudiants(id) ON DELETE CASCADE,
    FOREIGN KEY (coursid) REFERENCES cours(id) ON DELETE CASCADE,

    -- Empecher les doublons
    UNIQUE KEY uketudiantcours (etudiantid, coursid),

    INDEX idxcours (coursid)
);

etudiants (N)           inscriptions              cours (N)
+----+---------+        +----+-----+-----+        +----+------+
| id | nom     |        | id | eid| cid|        | id | code |
+----+---------+        +----+-----+-----+        +----+------+
| 1  | Dupont  | <----> | 1  | 1   | 1   | <----> | 1  | MAT1 |
| 2  | Martin  | <----> | 2  | 1   | 2   | <----> | 2  | PHY1 |
| 3  | Bernard | <----> | 3  | 2   | 1   |        | 3  | INF1 |
+----+---------+        | 4  | 2   | 3   |        +----+------+
                        | 5  | 3   | 2   |
                        +----+-----+-----+

Autre exemple : Produits et tags

CREATE TABLE produits (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    nom VARCHAR(255) NOT NULL,
    prix DECIMAL(10, 2) NOT NULL
);

CREATE TABLE tags (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    nom VARCHAR(50) NOT NULL UNIQUE,
    couleur VARCHAR(7) DEFAULT '#000000'
);

-- Table de liaison simple (sans donnees supplementaires)
CREATE TABLE produittags (
    produitid INT UNSIGNED NOT NULL,
    tagid INT UNSIGNED NOT NULL,

    PRIMARY KEY (produitid, tagid),  -- Cle primaire composite

    FOREIGN KEY (produitid) REFERENCES produits(id) ON DELETE CASCADE,
    FOREIGN KEY (tagid) REFERENCES tags(id) ON DELETE CASCADE
);

-- Insertion
INSERT INTO produittags (produitid, tagid) VALUES
(1, 1), (1, 2), (1, 5),  -- Produit 1 a 3 tags
(2, 1), (2, 3);          -- Produit 2 a 2 tags

Relation reflexive (auto-reference)

Une table fait reference a elle-meme pour modeliser des hierarchies.

-- Hierarchie d'employes (manager/subordonne)
CREATE TABLE employes (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    managerid INT UNSIGNED NULL,  -- NULL pour le PDG/dirigeant
    nom VARCHAR(100) NOT NULL,
    prenom VARCHAR(100) NOT NULL,
    poste VARCHAR(100) NOT NULL,

    FOREIGN KEY (managerid)
        REFERENCES employes(id)
        ON DELETE SET NULL,

    INDEX idxmanager (managerid)
);

-- Insertion de la hierarchie
INSERT INTO employes (id, managerid, nom, prenom, poste) VALUES
(1, NULL, 'Dupont', 'Marie', 'PDG'),
(2, 1, 'Martin', 'Jean', 'Directeur Technique'),
(3, 1, 'Bernard', 'Paul', 'Directeur Commercial'),
(4, 2, 'Petit', 'Sophie', 'Developpeur Senior'),
(5, 2, 'Moreau', 'Lucas', 'Developpeur'),
(6, 3, 'Durand', 'Emma', 'Commercial');

-- Requete : Trouver tous les subordonnes d'un manager
SELECT  FROM employes WHERE managerid = 2;

-- Requete : Trouver le manager d'un employe
SELECT m.
FROM employes e
JOIN employes m ON e.managerid = m.id
WHERE e.id = 5;

-- Hierarchie de categories (categories/sous-categories)
CREATE TABLE categories (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    parentid INT UNSIGNED NULL,
    nom VARCHAR(100) NOT NULL,
    niveau INT UNSIGNED DEFAULT 0,
    chemin VARCHAR(500),  -- Stocke le chemin complet : "1/3/7"

    FOREIGN KEY (parentid)
        REFERENCES categories(id)
        ON DELETE CASCADE,

    INDEX idxparent (parentid),
    INDEX idxniveau (niveau)
);

Normalisation des donnees

La normalisation est le processus de structuration des donnees pour eliminer la redondance et les anomalies.

Premiere forme normale (1NF)

Regle : Chaque colonne contient une seule valeur atomique (pas de listes, pas de valeurs multiples).

-- MAUVAIS (pas en 1NF) : telephones multiples dans une colonne
CREATE TABLE clientsmauvais (
    id INT PRIMARY KEY,
    nom VARCHAR(100),
    telephones VARCHAR(255)  -- "0601020304, 0698765432"
);

-- BON (1NF) : Une table separee pour les telephones
CREATE TABLE clients (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    nom VARCHAR(100) NOT NULL
);

CREATE TABLE telephones (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    clientid INT UNSIGNED NOT NULL,
    numero VARCHAR(20) NOT NULL,
    type ENUM('mobile', 'fixe', 'travail') DEFAULT 'mobile',

    FOREIGN KEY (clientid) REFERENCES clients(id) ON DELETE CASCADE
);

Deuxieme forme normale (2NF)

Regle : En 1NF + tous les attributs non-cles dependent entierement de la cle primaire (pas de dependance partielle).

-- MAUVAIS (pas en 2NF) : nomproduit depend seulement de produitid
CREATE TABLE commandelignesmauvais (
    commandeid INT,
    produitid INT,
    nomproduit VARCHAR(255),  -- Depend seulement de produitid
    quantite INT,
    PRIMARY KEY (commandeid, produitid)
);

-- BON (2NF) : Separation des tables
CREATE TABLE produits (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    nom VARCHAR(255) NOT NULL,
    prix DECIMAL(10, 2) NOT NULL
);

CREATE TABLE commandelignes (
    commandeid INT UNSIGNED NOT NULL,
    produitid INT UNSIGNED NOT NULL,
    quantite INT UNSIGNED NOT NULL,
    prixunitaire DECIMAL(10, 2) NOT NULL,  -- Snapshot au moment de la commande

    PRIMARY KEY (commandeid, produitid),
    FOREIGN KEY (produitid) REFERENCES produits(id)
);

Troisieme forme normale (3NF)

Regle : En 2NF + pas de dependance transitive (un attribut non-cle ne depend pas d’un autre attribut non-cle).

-- MAUVAIS (pas en 3NF) : nomville depend de codepostal, pas de id
CREATE TABLE clientsmauvais (
    id INT PRIMARY KEY,
    nom VARCHAR(100),
    codepostal VARCHAR(10),
    nomville VARCHAR(100)  -- Depend de codepostal (transitif)
);

-- BON (3NF) : Separation des villes
CREATE TABLE villes (
    codepostal VARCHAR(10) PRIMARY KEY,
    nom VARCHAR(100) NOT NULL,
    departement VARCHAR(3)
);

CREATE TABLE clients (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    nom VARCHAR(100) NOT NULL,
    codepostal VARCHAR(10),

    FOREIGN KEY (codepostal) REFERENCES villes(codepostal)
);

Resume des formes normales

Forme	Regle principale	Probleme resolu
1NF	Valeurs atomiques	Donnees dans des listes
2NF	Dependance complete de la cle	Redondance partielle
3NF	Pas de dependance transitive	Redondance transitive
BCNF	Chaque determinant est une cle	Anomalies restantes

Denormalisation strategique

Parfois, on denormalise volontairement pour les performances :

-- Table denormalisee pour les rapports (lecture rapide)
CREATE TABLE rapportventesmensuel (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    anneemois CHAR(7) NOT NULL,  -- "2024-01"
    produitid INT UNSIGNED NOT NULL,
    produitnom VARCHAR(255) NOT NULL,         -- Denormalise
    categorienom VARCHAR(100),                -- Denormalise
    quantitevendue INT UNSIGNED DEFAULT 0,
    chiffreaffaires DECIMAL(12, 2) DEFAULT 0,

    UNIQUE KEY ukmoisproduit (anneemois, produitid),
    INDEX idxmois (anneemois),
    INDEX idxproduit (produitid)
);

-- Mise a jour periodique via procedure
DELIMITER //
CREATE PROCEDURE actualiserrapportventes(IN pmois CHAR(7))
BEGIN
    DELETE FROM rapportventesmensuel WHERE anneemois = pmois;

    INSERT INTO rapportventesmensuel
        (anneemois, produitid, produitnom, categorienom,
         quantitevendue, chiffreaffaires)
    SELECT
        pmois,
        p.id,
        p.nom,
        c.nom,
        SUM(cl.quantite),
        SUM(cl.quantite  cl.prixunitaire)
    FROM commandes cmd
    JOIN commandelignes cl ON cmd.id = cl.commandeid
    JOIN produits p ON cl.produitid = p.id
    LEFT JOIN categories c ON p.categorieid = c.id
    WHERE DATEFORMAT(cmd.createdat, '%Y-%m') = pmois
    AND cmd.statut NOT IN ('annulee', 'brouillon')
    GROUP BY p.id, p.nom, c.nom;
END //
DELIMITER ;

Diagrammes Entite-Relation (ERD)

Notation de base

Notation Crow's Foot (patte de corbeau) :

    ||-----|<    : Un a plusieurs (1:N)
    ||-----||    : Un a un (1:1)
    >|-----|<    : Plusieurs a plusieurs (N:N)
    ||--o--|<    : Un a zero ou plusieurs (1:0..N)
    ||--o--||    : Un a zero ou un (1:0..1)

Exemple :
    [CLIENTS] ||------o|< [COMMANDES] ||---------|< [LIGNESCOMMANDE] >|---------|| [PRODUITS]

    Un client peut avoir zero ou plusieurs commandes
    Une commande a au moins une ligne
    Un produit peut apparaitre dans plusieurs lignes

Exemple de schema complet

                                    +------------------+
                                    |    CATEGORIES    |
                                    +------------------+
                                    | PK id            |
                                    | FK parentid     |---+
                                    |    nom           |   |
                                    |    slug          |   |
                                    +--------+---------+   |
                                             |             |
                                             | 1:N         |
                                             |             |
+------------------+                +--------+---------+   |
|     CLIENTS      |                |     PRODUITS     |<--+
+------------------+                +------------------+
| PK id            |                | PK id            |
|    email         |                | FK categorieid  |
|    nom           |                |    sku           |
|    prenom        |                |    nom           |
+--------+---------+                |    prix          |
         |                          |    stock         |
         | 1:N                      +--------+---------+
         |                                   |
+--------+---------+                         |
|    COMMANDES     |                         |
+------------------+                         |
| PK id            |                         |
| FK clientid     |                         |
|    numero        |                         |
|    total         |                         |
|    statut        |                         |
+--------+---------+                         |
         |                                   |
         | 1:N                               | N:N
         |                                   |
+--------+-----------------------------------+--------+
|              COMMANDELIGNES                        |
+-----------------------------------------------------+
| PK id                                               |
| FK commandeid                                      |
| FK produitid                                       |
|    quantite                                         |
|    prixunitaire                                    |
+-----------------------------------------------------+

Strategies de migration

Ajouter une colonne

-- Migration: Ajout d'un champ "datederniereconnexion"
ALTER TABLE utilisateurs
ADD COLUMN datederniereconnexion TIMESTAMP NULL AFTER email;

-- Avec valeur par defaut
ALTER TABLE produits
ADD COLUMN poidskg DECIMAL(8, 3) DEFAULT 0.000 AFTER prix;

Ajouter une nouvelle relation

-- Migration: Ajouter un systeme de favoris (N:N)

-- Etape 1: Creer la table de liaison
CREATE TABLE favoris (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    utilisateurid INT UNSIGNED NOT NULL,
    produitid INT UNSIGNED NOT NULL,
    createdat TIMESTAMP DEFAULT CURRENTTIMESTAMP,

    FOREIGN KEY (utilisateurid)
        REFERENCES utilisateurs(id)
        ON DELETE CASCADE,
    FOREIGN KEY (produitid)
        REFERENCES produits(id)
        ON DELETE CASCADE,

    UNIQUE KEY ukuserproduit (utilisateurid, produitid),
    INDEX idxproduit (produitid)
);

-- Etape 2: Migration des donnees si necessaire
-- (exemple: migrer depuis un champ JSON existant)
INSERT INTO favoris (utilisateurid, produitid)
SELECT
    u.id,
    JSONEXTRACT(u.preferences, '$.favoris[]')
FROM utilisateurs u
WHERE JSONLENGTH(u.preferences, '$.favoris') > 0;

Modifier une relation

-- Migration: Passer de 1:N a N:N
-- Exemple: Un produit appartenait a une categorie, maintenant plusieurs

-- Etape 1: Creer la table de liaison
CREATE TABLE produitcategories (
    produitid INT UNSIGNED NOT NULL,
    categorieid INT UNSIGNED NOT NULL,
    estprincipale BOOLEAN DEFAULT FALSE,

    PRIMARY KEY (produitid, categorieid),
    FOREIGN KEY (produitid) REFERENCES produits(id) ON DELETE CASCADE,
    FOREIGN KEY (categorieid) REFERENCES categories(id) ON DELETE CASCADE
);

-- Etape 2: Migrer les donnees existantes
INSERT INTO produitcategories (produitid, categorieid, estprincipale)
SELECT id, categorieid, TRUE
FROM produits
WHERE categorieid IS NOT NULL;

-- Etape 3: Supprimer l'ancienne colonne (apres verification)
ALTER TABLE produits DROP FOREIGN KEY produitsibfk1;
ALTER TABLE produits DROP COLUMN categorieid;

Bonnes pratiques de modelisation

1. Conventions de nommage

-- Tables : nom pluriel, snakecase
CREATE TABLE commandelignes ...
CREATE TABLE utilisateurroles ...

-- Colonnes : snakecase, prefixe pour les FK
clientid, categorieid, createdat, updatedat

-- Index : prefixe descriptif
INDEX idxclientemail (email)
UNIQUE KEY ukproduitsku (sku)
FOREIGN KEY fkcommandeclient

-- Eviter les mots reserves
-- MAUVAIS : order, user, group, key
-- BON : orders, users, groups, keyvalue

2. Toujours inclure des champs d’audit

CREATE TABLE entite (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    -- ... colonnes metier ...
    createdat TIMESTAMP DEFAULT CURRENTTIMESTAMP,
    updatedat TIMESTAMP DEFAULT CURRENTTIMESTAMP ON UPDATE CURRENTTIMESTAMP,
    createdby INT UNSIGNED,
    updatedby INT UNSIGNED
);

3. Utiliser les contraintes CHECK (MySQL 8.0+)

CREATE TABLE produits (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    prix DECIMAL(10, 2) NOT NULL,
    stock INT NOT NULL,

    CONSTRAINT chkprixpositif CHECK (prix >= 0),
    CONSTRAINT chkstockpositif CHECK (stock >= 0)
);

4. Documenter le schema

-- Ajouter des commentaires sur les tables et colonnes
CREATE TABLE commandes (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY
        COMMENT 'Identifiant unique de la commande',
    client_id INT UNSIGNED NOT NULL
        COMMENT 'Reference vers le client',
    statut ENUM('brouillon', 'validee', 'expediee', 'livree')
        COMMENT 'Etat actuel de la commande'
) COMMENT='Table des commandes clients';

-- Voir les commentaires
SHOW CREATE TABLE commandes;
SHOW FULL COLUMNS FROM commandes;

Conclusion

La modelisation de base de donnees est une competence fondamentale qui determine la qualite et l’evolutivite de vos applications. Voici les points essentiels a retenir :

Cles a maitriser

Cle primaire : Identifiant unique, preferez les cles de substitution (auto-increment)

Cle etrangere : Lien entre tables avec actions referentielles appropriees

Index : Sur toutes les colonnes utilisees dans les WHERE et JOIN

Relations fondamentales

1:1 : Separation logique ou technique des donnees

1:N : La plus courante, cle etrangere cote « N »

N:N : Toujours via une table de liaison

Normalisation

Appliquez au moins la 3NF pour les donnees transactionnelles

Denormalisez strategiquement pour les donnees de lecture/reporting

Prochaines etapes

Dans les articles suivants, vous apprendrez :

Les jointures SQL pour interroger des tables liees

L’optimisation avec les index

Les transactions et la gestion de la concurrence

Les patterns avances de modelisation

Une bonne modelisation au depart vous fera gagner des semaines de travail plus tard. Prenez le temps de bien concevoir votre schema avant d’ecrire la premiere ligne de code applicatif.