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.
Pourquoi la conception de schéma est importante
Un schéma de base de données bien conçu est la fondation de toute application fiable. Réussissez-le et les requêtes sont rapides, les données restent cohérentes et les fonctionnalités futures s'intègrent sans migrations douloureuses. Manquez-le et vous devrez lutter contre des anomalies de données, des performances léthargiques et des contournements sans fin pendant toute la durée de vie du projet.
Ce guide présente pas à pas processus en six étapes pour concevoir un schéma à partir de zéro — et se termine par un exemple réel complet que vous pouvez adapter pour vos propres projets.
Étape 1 — Recueillir les exigences
Avant d'ouvrir le moindre outil, discutez avec les parties prenantes. Comprenez ce que l'application ne, les données qu'elle gère et la manière dont ces données sont liées. Posez des questions telles que :
- Quelles sont les principales « choses » suivies par le système ? (Utilisateurs, commandes, produits...)
- Quelles données doivent être uniques ? (E-mails, SKU, slugs...)
- Quelles requêtes l'application exécutera-t-elle le plus souvent ?
- Existe-t-il des contraintes réglementaires ? (RGPD, HIPAA...)
- Quel est le volume de données attendu au cours des 1 à 3 prochaines années ?
Notez les réponses — elles deviennent votre spécification et guideront chaque décision future.
Étape 2 — Identifier les entités
Un entité est tout ce sur quoi le système doit stocker des données. Chaque entité correspond généralement à une table de base de données. Pour une plateforme de blog, la liste initiale des entités peut ressembler à :
- Utilisateur — auteurs et lecteurs
- Publier — articles publiés sur le blog
- Catégorie — regroupements d'articles (par ex., "React", "SQL")
- Commentaire — réponses des lecteurs aux articles
- Étiquette — étiquettes fines (plusieurs-à-plusieurs avec les articles)
Dessinez-les sous forme de boîtes sur un tableau blanc ou utilisez le Éditeur ERD pour discuter du modèle conceptuel avant de choisir les types de colonnes.
Étape 3 — Définir les attributs et les types de données
Pour chaque entité, listez les attributs dont elle a besoin et choisissez le type de données SQL le plus approprié. Soyez aussi précis que possible — préférez VARCHAR(255) sur TEXT lorsque vous connaissez la longueur maximale, et préférez TIMESTAMPTZ sur TIMESTAMP lorsque les fuseaux horaires comptent.
-- Users
id SERIAL PRIMARY KEY
username VARCHAR(50) NOT NULL UNIQUE
email VARCHAR(255) NOT NULL UNIQUE
password_hash TEXT NOT NULL -- store a password hash, never plaintext
bio TEXT
avatar_url VARCHAR(500)
created_at TIMESTAMPTZ DEFAULT now()
-- Posts
id SERIAL PRIMARY KEY
author_id INT NOT NULL -- FK → users.id
category_id INT -- FK → categories.id
title VARCHAR(200) NOT NULL
slug VARCHAR(220) NOT NULL UNIQUE
body TEXT NOT NULL
status VARCHAR(20) DEFAULT 'draft' -- draft | published | archived
published_at TIMESTAMPTZ
created_at TIMESTAMPTZ DEFAULT now()
updated_at TIMESTAMPTZ DEFAULT now()Étape 4 — Établir les relations
Déterminez comment les entités sont liées entre elles. Les trois types fondamentaux sont :
- Un-à-plusieurs (1:N) — Un utilisateur a de nombreux articles ; un article appartient à un seul utilisateur.
- Plusieurs-à-plusieurs (M:N) — Un article peut avoir de nombreuses étiquettes ; une étiquette peut appartenir à de nombreux articles. Cela nécessite un table de jonction.
- Un-à-un (1:1) — Un utilisateur a un profil. Souvent fusionné en une seule table, à moins que les données du profil ne soient volumineuses ou rarement consultées.
-- Junction table for the posts ↔ tags many-to-many relationship
CREATE TABLE post_tags (
post_id INT NOT NULL REFERENCES posts (id) ON DELETE CASCADE,
tag_id INT NOT NULL REFERENCES tags (id) ON DELETE CASCADE,
PRIMARY KEY (post_id, tag_id)
);Étape 5 — Appliquer les contraintes et les index
Les contraintes protègent la qualité des Données. Vous devriez au minimum envisager :
NOT NULLsur chaque colonne qui doit obligatoirement avoir une valeur.UNIQUEsur les identifiants naturels (e-mails, slugs, noms d'utilisateur).CHECKcontraintes pour les règles de domaine (par exemple, le statut doit être l'une des trois valeurs).DEFAULTvaleurs pour les horodatages et les colonnes de statut.- Clés étrangères avec explicitement
ON DELETEactions.
Ajoutez ensuite index sur les colonnes que vous filtrerez, trierez ou joindrez fréquemment. Les colonnes de clés étrangères sont des candidates idéales.
-- Index foreign-key columns for faster joins
CREATE INDEX idx_posts_author ON posts (author_id);
CREATE INDEX idx_posts_category ON posts (category_id);
CREATE INDEX idx_comments_post ON comments (post_id);
-- Partial index: only published posts (PostgreSQL)
CREATE INDEX idx_posts_published
ON posts (published_at DESC)
WHERE status = 'published';Étape 6 — Normaliser (puis dénormaliser si nécessaire)
Normalisation élimine les données redondantes en organisant les colonnes et les tables selon un ensemble de « formes normales ». La plupart des schémas devraient atteindre Troisième forme normale (3NF):
- 1NF — chaque colonne contient des valeurs atomiques ; pas de groupes répétés.
- 2NF — aucun attribut non premier ne dépend uniquement d'une partie d'une clé candidate.
- 3NF — chaque dépendance non triviale a un déterminant de superclé ou un attribut dépendant qui appartient à une clé candidate.
Après la normalisation, vous pouvez sélectivement dénormaliser pour les performances — par exemple, stocker un comment_count sur le posts table pour éviter une COUNT(*) à chaque chargement de page. Dénormalisez de manière délibérée et documentez-le.
Schéma complet de plateforme de blog
-- Full SQL for the blog platform schema
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(255) NOT NULL UNIQUE,
password_hash TEXT NOT NULL,
bio TEXT,
avatar_url VARCHAR(500),
created_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE,
slug VARCHAR(110) NOT NULL UNIQUE
);
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
author_id INT NOT NULL REFERENCES users (id),
category_id INT REFERENCES categories (id) ON DELETE SET NULL,
title VARCHAR(200) NOT NULL,
slug VARCHAR(220) NOT NULL UNIQUE,
body TEXT NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'draft'
CHECK (status IN ('draft','published','archived')),
published_at TIMESTAMPTZ,
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE tags (
id SERIAL PRIMARY KEY,
name VARCHAR(60) NOT NULL UNIQUE
);
CREATE TABLE post_tags (
post_id INT NOT NULL REFERENCES posts (id) ON DELETE CASCADE,
tag_id INT NOT NULL REFERENCES tags (id) ON DELETE CASCADE,
PRIMARY KEY (post_id, tag_id)
);
CREATE TABLE comments (
id SERIAL PRIMARY KEY,
post_id INT NOT NULL REFERENCES posts (id) ON DELETE CASCADE,
user_id INT NOT NULL REFERENCES users (id),
body TEXT NOT NULL,
created_at TIMESTAMPTZ DEFAULT now()
);
-- Indexes
CREATE INDEX idx_posts_author ON posts (author_id);
CREATE INDEX idx_posts_category ON posts (category_id);
CREATE INDEX idx_comments_post ON comments (post_id);
CREATE INDEX idx_comments_user ON comments (user_id);Collez le schéma complet dans le Éditeur SQL vers ERD pour inspecter ses relations. Utilisez le Éditeur ERD pour un dessin conceptuel et une conversion SQL examinée.



