Retour à tous les guides
Conception de bases de données··5 min de lecture

Concevoir un schéma de base de données de zéro : étape par étape

Concevez un schéma de base de données relationnelle à partir de zéro. Un guide étape par étape, des exigences aux tables, relations et contraintes avec SQL.

Les exigences deviennent des entités, des relations et un schéma SQL avec des clés customer et order
Dans ce guide
  1. Pourquoi la conception de schéma est importante
  2. Étape 1 — Recueillir les exigences
  3. Étape 2 — Identifier les entités
  4. Étape 3 — Définir les attributs et les types de données
  5. Étape 4 — Établir les relations
  6. Étape 5 — Appliquer les contraintes et les index
  7. Étape 6 — Normaliser (puis dénormaliser si nécessaire)
  8. Références et lecture complémentaire

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 NULL sur chaque colonne qui doit obligatoirement avoir une valeur.
  • UNIQUE sur les identifiants naturels (e-mails, slugs, noms d'utilisateur).
  • CHECK contraintes pour les règles de domaine (par exemple, le statut doit être l'une des trois valeurs).
  • DEFAULT valeurs pour les horodatages et les colonnes de statut.
  • Clés étrangères avec explicitement ON DELETE actions.

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.

Références et lecture complémentaire

Plus d'articles