Erreurs Courantes de Conception de Bases de Données Relationnelles

La plupart des erreurs de schéma relationnel se résument à une seule : faire confiance à l’application pour garantir ce que la base de données devrait assurer. Une clé étrangère que vous « gérez dans la couche service », une règle d’unicité vérifiée par un SELECT avant un INSERT, un champ de statut que votre ORM valide mais que la colonne ne contraint pas — chacun de ces cas représente une contrainte que vous avez retirée du moteur conçu pour l’appliquer, pour la confier à du code qui ne s’exécute que lorsque vous pensez à l’appeler. Le résultat : des données que le schéma n’aurait jamais autorisées — lignes orphelines, comptes en double, prix négatifs, horodatages impossibles à interpréter. Ce guide passe en revue les erreurs de conception récurrentes que commettent les développeurs d’applications sur Postgres et MySQL, explique pourquoi chacune finit par poser problème, et propose la correction — accompagnée d’un court extrait avant/après pour chaque cas. Les exemples utilisent la syntaxe moderne de Postgres 18 lorsque cela est pertinent, mais les principes s’appliquent à tout moteur relationnel.

La source de la plupart des erreurs de conception : des règles dans le code applicatif, pas dans le schéma

L’erreur la plus impactante consiste à n’appliquer les règles métier que dans le code applicatif. Une contrainte dans votre couche service ne protège qu’un seul chemin d’exécution ; une contrainte dans le schéma protège tous les chemins — votre API, un job en arrière-plan, une session psql manuelle, une migration de données bâclée, et le prochain service que quelqu’un écrira contre la même base de données. Lorsque la règle ne vit que dans l’application, le premier processus qui la contourne corrompt la table de façon permanente.

C’est le piège des ORM modernes. Des outils comme Prisma et Drizzle modélisent volontiers une relation dans le code applicatif sans jamais émettre de clé étrangère au niveau de la base de données, et un @default ou un schéma Zod donne l’impression d’une validation. Ce n’en est pas une. Un ORM qui vous permet de déclarer une relation sans clé étrangère au niveau de la base de données ne vous fait pas gagner du travail ; il reporte un bug d’intégrité des données en production, où la rejouabilité de session — et non votre suite de tests — est ce qui finit par le révéler, parce que l’échec ne se manifeste pas sous forme de stack trace. Les données corrompues apparaissent plutôt comme un avis attribué à un utilisateur qui n’existe plus, ou une commande dupliquée — un écran déroutant que l’utilisateur voit réellement.

La correction consiste à pousser la règle là où elle ne peut pas être contournée. Les contraintes de vérification de PostgreSQL, par exemple, rejettent les données invalides quel que soit le client qui les a écrites : pour exiger des prix de produits positifs, vous pouvez utiliser une contrainte CHECK (price > 0) dans la définition de la table.

-- Avant : rien n'empêche un paiement négatif ou un email NULL
CREATE TABLE payments (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id bigint,                 -- pas de FK : des orphelins en attente
  amount  numeric,                -- pas de CHECK : les négatifs sont autorisés
  email   text                    -- pas de NOT NULL, pas de UNIQUE
);

-- Après : le moteur garantit les invariants
CREATE TABLE payments (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id bigint NOT NULL REFERENCES users (id) ON DELETE RESTRICT,
  amount  numeric NOT NULL CHECK (amount > 0),
  email   text NOT NULL UNIQUE
);

Une clé étrangère n’est pas un coût en termes de performances — c’est la seule chose qui vous protège des lignes orphelines qui s’affichent comme un avis pour un utilisateur qui n’existe plus. Depuis Postgres 18, vous pouvez également ajouter des contraintes progressivement sur des tables très sollicitées : ALTER TABLE peut désormais définir l’attribut NOT VALID des contraintes NOT NULL, vous permettant d’ajouter la contrainte sans un scan complet immédiat de la table, puis de la valider ultérieurement sous un verrou moins restrictif.

Ignorer la normalisation — ou l’appliquer à l’excès

La normalisation consiste à ne stocker chaque fait qu’une seule fois. La première forme normale (1NF) signifie l’absence de groupes répétitifs et de champs à valeurs multiples ; la troisième forme normale (3NF) signifie que chaque colonne non-clé dépend de la clé et de rien d’autre. Pour les schémas applicatifs, la 3NF est la valeur par défaut raisonnable — consultez la documentation Postgres sur la définition des données pour les détails techniques. Les deux violations classiques sont les valeurs séparées par des virgules dans une seule colonne et les colonnes répétitives comme payment_1 … payment_12.

Les valeurs séparées par des virgules dans une seule colonne constituent une dénormalisation que vous regretterez dès le premier WHERE … LIKE '%,42,%' ; la correction est une table enfant ou une table de jonction, et non une requête de chaîne plus astucieuse.

-- Avant : tags sous forme de chaîne délimitée, numéros de téléphone en colonnes
CREATE TABLE users (
  id     bigint PRIMARY KEY,
  tags   text,           -- 'admin,beta,vip' — non interrogeable, non contraint
  phone1 text, phone2 text, phone3 text   -- groupe répétitif, finit par manquer
);

-- Après : une table de jonction pour les tags, une table enfant pour les téléphones
CREATE TABLE user_tags (
  user_id bigint NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  tag     text   NOT NULL,
  PRIMARY KEY (user_id, tag)
);
CREATE TABLE user_phones (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id bigint NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  phone   text   NOT NULL
);

L’erreur inverse est la sur-normalisation : diviser une seule address en six tables jointes, ou modéliser une relation un-à-un en deux tables sans raison valable. Chaque jointure supplémentaire représente un coût en termes de requêtes et de compréhension. Normalisez jusqu’à ce que chaque fait n’existe qu’une seule fois, puis arrêtez-vous.

Utiliser des champs métier comme clés primaires

N’utilisez pas un champ métier comme email, username ou company_name comme clé primaire ; utilisez une clé de substitution et ajoutez une contrainte UNIQUE séparée sur la valeur naturelle, de sorte qu’un changement de nom ou d’adresse soit un UPDATE, et non une migration. Les valeurs métier changent, et lorsqu’une clé primaire change, chaque clé étrangère qui la référence doit être mise à jour en cascade — une valeur qui n’aurait jamais dû servir d’identité devient une migration à l’échelle du schéma.

Le conseil de 2007 consistant à éviter les clés de substitution est obsolète pour les développeurs d’applications. La pratique moderne est une clé primaire de substitution associée à une vraie contrainte UNIQUE sur la clé naturelle — vous obtenez une identité stable et la garantie d’unicité.

CREATE TABLE companies (
  id   uuid PRIMARY KEY DEFAULT uuidv7(),   -- identité de substitution stable
  name text NOT NULL UNIQUE                 -- clé naturelle toujours appliquée
);

UUID ou bigint : le choix tranché

Depuis Postgres 18, vous n’avez plus besoin d’extension pour les identifiants ordonnés dans le temps : Postgres 18 ajoute la fonction de génération d’UUID uuidv7(), dont la valeur est triable temporellement, ainsi qu’un alias uuidv4() pour générer explicitement des UUID de version 4. UUIDv7 — défini dans la RFC 9562 (mai 2024, qui rend obsolète la RFC 4122) — intègre un préfixe d’horodatage, de sorte que les nouvelles lignes s’ajoutent à droite de l’index au lieu de se disperser comme des UUID v4 aléatoires. Notez que pour les versions antérieures de Postgres, gen_random_uuid() (v4) fait partie du cœur depuis la version 13 ; l’extension pgcrypto n’est plus nécessaire.

Optez par défaut pour bigint lorsque les identifiants restent au sein d’une seule base de données, et pour uuidv7() lorsque vous générez des identifiants côté client ou entre plusieurs services.

Absence de stratégie d’indexation délibérée

Indexez les colonnes sur lesquelles vous filtrez et effectuez des jointures réellement — en particulier les clés étrangères, que Postgres n’indexe pas automatiquement — mais évitez d’indexer toutes les colonnes, car chaque index amplifie les écritures à chaque insertion et mise à jour. C’est la régression de performance la plus courante dans les schémas applicatifs, et il est facile de se tromper dans les deux sens.

Le piège est spécifique à Postgres : il indexe automatiquement le côté référencé (parent) d’une clé étrangère, mais pas la colonne référençante (enfant). Étant donné que l’indexation des colonnes référençantes n’est pas toujours nécessaire et qu’il existe de nombreuses façons d’indexer, la déclaration d’une contrainte de clé étrangère ne crée pas automatiquement d’index sur les colonnes référençantes. (InnoDB de MySQL crée automatiquement un tel index — ce piège est donc spécifique à Postgres.)

-- payments.user_id est une FK mais non indexée : chaque jointure et chaque
-- DELETE sur users effectue un parcours séquentiel de payments
CREATE INDEX ON payments (user_id);

Indexez les colonnes de clés étrangères et de filtrage ; évitez les index sur les colonnes à faible sélectivité et sur les tables que vous interrogez rarement par ce champ.

Une table qui remplit trop de rôles

Une table unique forcée à modéliser de nombreux domaines — la table générique entité-attribut-valeur (EAV), ou une table notifications regroupant alertes, événements d’audit et journaux système — sacrifie la clarté du schéma pour une fausse flexibilité. Vous perdez la sécurité des types, vous ne pouvez pas appliquer de contraintes significatives, et chaque lecture devient un fouillis de filtres et d’auto-jointures. Utilisez des tables séparées par domaine afin que chacune dispose de ses propres colonnes, types et clés étrangères.

-- Avant : un fourre-tout typé en text, sans vraies contraintes
CREATE TABLE entity_attributes (
  entity_id bigint, attr_name text, attr_value text
);

-- Après : de vraies tables avec de vraies colonnes et contraintes
CREATE TABLE user_alerts  (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
                           user_id bigint NOT NULL REFERENCES users(id),
                           level text NOT NULL, body text);
CREATE TABLE audit_events (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
                           actor_id bigint REFERENCES users(id),
                           action text NOT NULL,
                           occurred_at timestamptz NOT NULL DEFAULT now());

Si vous avez réellement besoin d’attributs flexibles, optez pour une colonne jsonb typée sur la vraie table — et non pour un EAV typé en chaîne de caractères qui annule toutes les contraintes que le moteur peut offrir.

Nommage incohérent

Choisissez une convention de nommage et appliquez-la partout. Pour Postgres et la plupart des ORM, cela signifie :

• snake_case (les identifiants non quotés sont convertis en minuscules) ;
• noms de tables au pluriel ou au singulier, choisis une fois pour toutes ;
• created_at / updated_at pour les horodatages ;
• aucune métadonnée dans les noms (tbl_users, col_varchar_address) ;
• pas d’espaces ni de guillemets ; et
• aucun mot réservé comme user, order ou group comme identifiant nu.

Le nommage est ce qu’il y a de moins coûteux à bien faire au moment de la création de la table, et ce qu’il y a de plus coûteux à modifier une fois que des données et des requêtes en dépendent.

Traiter le schéma comme définitif

Un schéma est du code vivant, pas un artefact ponctuel. Gérez chaque modification via des migrations versionnées, révisées et réversibles — l’outil de migration de votre ORM (Prisma Migrate, Drizzle Kit) ou un outil autonome — afin que l’historique du schéma soit reproductible entre les environnements.

L’historique dicte également la façon dont vous supprimez. Préférez une suppression logique (un champ deleted_at timestamptz) à un DELETE physique lorsque d’autres lignes référencent l’enregistrement, car une suppression physique orpheline les enfants ou, avec ON DELETE CASCADE, efface silencieusement un historique que l’on vous demandera peut-être de produire plus tard.

ALTER TABLE orders ADD COLUMN deleted_at timestamptz;
-- "Commandes actives" devient un filtre, pas une opération destructrice
CREATE VIEW active_orders AS SELECT * FROM orders WHERE deleted_at IS NULL;

Stocker les dates et heures sans fuseau horaire

Stockez les horodatages en timestamptz, et non en timestamp — une colonne sans fuseau horaire enregistre silencieusement ce que le serveur considérait comme « maintenant », et cette ambiguïté devient irrécupérable dès que vos serveurs s’étendent sur plusieurs régions. timestamptz stocke un point absolu dans le temps (normalisé en UTC) et le restitue dans le fuseau horaire de la session ; timestamp stocke une heure horloge murale sans ancrage. Consultez la documentation Postgres sur les types date/heure. Pour les plannings et les réservations, Postgres 18 a également ajouté des contraintes temporelles : il prend en charge les contraintes PRIMARY KEY, UNIQUE et de clé étrangère sans chevauchement, spécifiées avec WITHOUT OVERLAPS et PERIOD. (Les clés étrangères temporelles ne prennent pas en charge les actions de cascade ON DELETE/ON UPDATE, donc ne comptez pas sur les cascades dans ce contexte.)

Absence de documentation ou de tests du schéma

Un schéma non documenté et non testé est une erreur qui se cumule silencieusement. Les valeurs magiques en sont le symptôme classique : un status_code de 1 à 3 qui se brise le jour où quelqu’un ajoute 4, sans aucune trace de ce que signifie chaque nombre. Les remèdes sont concrets :

• documentez l’intention directement dans la base de données avec COMMENT ON COLUMN ;
• conservez un diagramme ER dans le dépôt ; et
• testez les migrations comme vous testez le code — appliquez, annulez, et vérifiez les cas limites et les entrées invalides.

COMMENT ON COLUMN orders.status IS
  'enum: pending|paid|shipped|cancelled — voir app/orders/status.ts';

Une contrainte CHECK (status IN (...)) associée à un commentaire transforme un entier fragile en une colonne auto-descriptive et auto-contrainte.

Conclusion

Chaque erreur présentée ici se résout de la même façon : faites de la base de données — et non de l’application, ni de l’ORM — l’autorité en matière de validité des données. Ajoutez la clé étrangère, le NOT NULL, le CHECK et la contrainte UNIQUE avant d’écrire la requête qui les suppose. Commencez par votre table la plus sollicitée en écriture : listez les invariants que vous appliquez actuellement dans le code, et déplacez chacun d’eux dans le schéma, là où il ne peut pas être contourné.

FAQ

Quand devrais-je utiliser une clé primaire UUID plutôt qu'une colonne bigint identity ?

Utilisez un bigint identity lorsque les identifiants sont générés au sein d'une seule base de données et n'ont jamais besoin d'être créés par des clients, car il occupe 8 octets, s'indexe séquentiellement et représente l'option la plus rapide. Utilisez un UUID, spécifiquement uuidv7() dans Postgres 18, lorsque vous générez des identifiants côté client ou entre plusieurs services et que vous avez besoin d'une unicité globale. UUIDv7 conserve une localité d'index quasi-séquentielle grâce à son préfixe d'horodatage intégré, contrairement à l'UUIDv4 aléatoire.

La déclaration d'une clé étrangère dans mon ORM crée-t-elle également une contrainte au niveau de la base de données ?

Pas nécessairement. Des outils comme Prisma et Drizzle peuvent modéliser une relation dans le code applicatif sans émettre de contrainte FOREIGN KEY au niveau de la base de données, de sorte que la relation n'existe que dans la compréhension de l'ORM et non dans le moteur. Sans la contrainte au niveau de la base de données, un job en arrière-plan, une requête manuelle ou un autre service peut créer des lignes orphelines que l'ORM n'avait pas anticipées. Vérifiez que votre migration génère bien une clause REFERENCES plutôt que de faire confiance à la seule déclaration de relation.

Quelle est la différence entre timestamp et timestamptz dans PostgreSQL ?

timestamptz stocke un point absolu dans le temps normalisé en UTC et le restitue dans le fuseau horaire de la session, tandis que timestamp stocke une heure horloge murale sans ancrage de fuseau horaire. Un timestamp simple enregistre silencieusement ce que le serveur considérait comme l'heure locale, et cette ambiguïté devient irrécupérable dès que les serveurs s'étendent sur plusieurs régions. Utilisez timestamptz pour les dates et heures applicatives afin qu'une valeur fasse toujours référence à un instant unique et non ambigu, quel que soit l'endroit où elle est lue.

Ai-je encore besoin de l'extension pgcrypto ou uuid-ossp pour générer des UUID dans Postgres ?

Non. La fonction gen_random_uuid(), qui produit un UUID de version 4, fait partie du cœur de PostgreSQL depuis la version 13, donc aucune extension n'est requise. La documentation de pgcrypto pour PG18 marque désormais sa propre gen_random_uuid() comme obsolète car elle appelle la fonction native. Depuis Postgres 18, vous disposez également de uuidv7() intégré pour les identifiants ordonnés dans le temps et d'un alias uuidv4(), le tout sans installer d'extension.