Database Design Advisor

Database Design Advisor

Workflow

1. Recueillir les exigences

Questions à poser systématiquement :

• Quelles sont les entités métier principales ? (ex. : Commande, Client, Produit)
• Quels sont les volumes estimés ? (lignes/table, croissance annuelle)
• Profil de charge : read-heavy, write-heavy, ou mixte ?
• Contraintes de latence ? (OLTP < 10 ms vs OLAP analytique)
• Multitenancy ? Soft delete ? Audit trail ?

2. Construire le modèle conceptuel (ERD)

Notation Crow's Foot recommandée. Identifier :

• Entités fortes vs entités faibles
• Cardinalités : 1:1, 1:N, N:M
• Attributs multivalués → table séparée obligatoire
• Agréger les associations N:M en entité d'association avec ses propres attributs

Client (1) ──────< (N) Commande (N) >──────< (N) Produit
                            |
                     LigneCommande (entité d'association)
                       quantite, prix_unitaire

3. Normalisation — critères de décision

Règle pratique : viser 3NF par défaut ; BCNF si les anomalies persistent avec des clés candidates multiples.

4. DDL — squelette opérationnel

-- Convention : snake_case, PK surrogate, timestamps audit
CREATE TABLE client (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    code_client VARCHAR(20)  NOT NULL UNIQUE,
    nom         VARCHAR(100) NOT NULL,
    email       VARCHAR(255) NOT NULL UNIQUE,
    actif       BOOLEAN      NOT NULL DEFAULT TRUE,
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ  NOT NULL DEFAULT now()
);

CREATE TABLE commande (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    client_id   BIGINT       NOT NULL REFERENCES client(id),
    statut      VARCHAR(20)  NOT NULL CHECK (statut IN ('BROUILLON','VALIDEE','LIVREE','ANNULEE')),
    total_ht    NUMERIC(14,4) NOT NULL CHECK (total_ht >= 0),
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT now()
);
CREATE INDEX idx_commande_client ON commande(client_id);
CREATE INDEX idx_commande_statut ON commande(statut) WHERE statut NOT IN ('LIVREE','ANNULEE');

5. Patterns courants — quand les appliquer

Héritage (entité Personne → Employe / Client) :

Historisation SCD :

• Type 1 : écrasement → pas de trace (accepté pour données de référence)
• Type 2 : nouvelle ligne + date_debut/date_fin + is_current → audit complet
• Type 6 : combinaison 1+2+3 → complexe, réserver aux Data Warehouses

Soft delete :

-- Ajouter à chaque table concernée
deleted_at TIMESTAMPTZ,
deleted_by BIGINT REFERENCES utilisateur(id)
-- Index partiel pour exclure les supprimés des requêtes courantes
CREATE INDEX idx_client_actif ON client(code_client) WHERE deleted_at IS NULL;

Multi-tenant :

-- Approche shared schema (recommandée pour < 1000 tenants)
tenant_id BIGINT NOT NULL REFERENCES tenant(id)
-- Index composite : (tenant_id, clé_métier)
CREATE INDEX idx_commande_tenant ON commande(tenant_id, id);
-- RLS PostgreSQL
ALTER TABLE commande ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON commande
    USING (tenant_id = current_setting('app.tenant_id')::BIGINT);

6. Indexation — règles de décision

Créer un index si :
  - Colonne dans WHERE fréquent ET cardinalité > 10 valeurs distinctes
  - Clé étrangère non couverte (jointure sans index = seq scan)
  - ORDER BY / GROUP BY sur grande table

Éviter si :
  - Table < 10 000 lignes (seq scan souvent plus rapide)
  - Colonne avec < 5 valeurs distinctes (booléen, statut à 2 états)
  - Table à insert massif (chaque index ralentit les writes)

Index partiel pour tables à soft delete ou statuts actifs :
  CREATE INDEX idx_cmd_en_cours ON commande(client_id)
      WHERE statut IN ('BROUILLON','VALIDEE');

7. Dénormalisation contrôlée

Toujours documenter via un commentaire ou ADR :

-- DÉNORM: total_commande stocké en cache dans client.total_commandes_ytd
-- Raison: requête tableau de bord T+500ms → T+5ms
-- Cohérence: trigger AFTER INSERT/UPDATE/DELETE sur commande
-- Revu le: 2026-01-15 — acceptable jusqu'à 5M commandes
ALTER TABLE client ADD COLUMN total_commandes_ytd NUMERIC(16,4) NOT NULL DEFAULT 0;

8. Validation finale — checklist

• Chaque table a une PK surrogate (BIGINT ou UUID selon besoin de distribution)
• Toutes les FK ont un index couvrant
• Pas de colonnes valeur1, valeur2, valeur3 → tableau ou table séparée
• Pas de stockage de listes CSV dans une colonne TEXT
• Contraintes CHECK sur les colonnes avec domaine fini (statuts, types)
• Colonnes created_at / updated_at présentes sur les tables métier
• DDL testé sur un jeu de données représentatif (INSERT + requêtes critiques)

Anti-patterns / Pièges

Bonnes pratiques 2026

• UUID v7 / ULID : préférer aux UUID v4 pour les PKs distribuées — ordonnés dans le temps, compatibles B-tree.
• JSONB avec schéma partiel : stocker les attributs variables en JSONB + index GIN, mais extraire en colonne les champs interrogés fréquemment.
• Generated columns : calculer des colonnes dérivées côté SGBD plutôt qu'en applicatif.
• Temporal tables (SQL:2011, supporté PostgreSQL 16+, SQL Server 2016+) : privilégier aux patterns SCD 2 manuels pour l'historisation native.
• Schema versioning : versionner les migrations avec Flyway ou Liquibase dès le premier jour — jamais de DDL appliqué manuellement en prod.