Database Schema Design

Problème

Les schémas sont conçus au fil de l'eau, sans indexing prévu, sans stratégie de migration. Résultat : performances qui s'effondrent avec le volume, migrations qui cassent la prod.

Solution

Principes de design de schéma + indexing stratégique + migrations réversibles.

1. Règles de normalization

• 3NF par défaut — une donnée = une place
• Dénormaliser uniquement sur colonnes calculées fréquemment lues (counters, caches)
• Pas de JSON pour des données relationnelles — utiliser des tables
• JSON acceptable pour : metadata flexible, config, payloads d'API externes

2. Indexing — les 5 règles

-- 1. Index sur toutes les FK
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 2. Index composites dans l'ordre de requête
CREATE INDEX idx_orders_status_created ON orders(status, created_at);

-- 3. Index partiel pour les queries fréquentes
CREATE INDEX idx_users_active ON users(last_login) WHERE deleted_at IS NULL;

-- 4. Index unique pour contraintes métier
CREATE UNIQUE INDEX idx_users_email ON users(email) WHERE deleted_at IS NULL;

-- 5. Pas d'index sur colonnes à faible cardinalité (booléens, statuts avec 2 valeurs)

3. Types — choisir le bon

4. Migrations réversibles

// Prisma migration — toujours up + down
// Drizzle — forward + rollback

// Règles :
// 1. Jamais de migration destructive en prod (DROP COLUMN, DROP TABLE)
// 2. Ajouter colonne → déployer → backfill → supprimer ancienne (migration suivie)
// 3. Renommer colonne → ajouter nouvelle → dual-write → migrer lectures → supprimer ancienne
// 4. Tester migration sur copy de prod avant

// Exemple : ajout de colonne non-null
// Step 1: ADD COLUMN nullable
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
// Step 2: Backfill
UPDATE users SET phone = '' WHERE phone IS NULL;
// Step 3: SET NOT NULL (migration suivie)
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;

5. Connection pooling

// Postgres — pgbouncer en production
// App — pool de connections, pas une connection par requête

// Prisma (pool intégré)
const prisma = new PrismaClient({
  datasources: {
    db: { url: process.env.DATABASE_URL + '?connection_limit=10' }
  }
});

// Drizzle + postgres-js
import postgres from 'postgres';
const sql = postgres(process.env.DATABASE_URL, {
  max: 10,        // max connections
  idle_timeout: 20,
  connect_timeout: 10
});

6. Soft delete pattern

-- Pas de DELETE physique, utiliser deleted_at
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;

-- Queries filtrent automatiquement
-- Via view :
CREATE VIEW users_active AS
SELECT * FROM users WHERE deleted_at IS NULL;

-- Via ORM scope (Prisma middleware ou Drizzle)

7. Audit trail

CREATE TABLE audit_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  table_name VARCHAR(50) NOT NULL,
  record_id UUID NOT NULL,
  action VARCHAR(10) NOT NULL, -- INSERT, UPDATE, DELETE
  changed_by UUID REFERENCES users(id),
  changed_at TIMESTAMPTZ DEFAULT NOW(),
  old_values JSONB,
  new_values JSONB
);

-- Trigger automatique ou application-level logging

Anti-patterns

• SELECT * en production
• Pas d'index sur les FK
• FLOAT pour des montants
• Migration destructive sans plan de rollback
• Une seule connection partagée (bottleneck)
• Pas de soft delete sur des données réglementées

Références

• [[KNOW-PAT-220]] — Secure SDLC Checklist
• [[KNOW-PAT-221]] — Privacy by Design (retention, deletion)
• [[KNOW-REF-010]] — Twelve-Factor App (backup, concurrency)