La carte SQL du mini‑shop

1. Carte du schéma et relations

Si on essaie de concevoir des tables « de gauche à droite » sans prendre de recul, on obtient facilement soit un monstre, soit de la bouillie. On va donc commencer par une vue d'ensemble : quels sous-systèmes existent dans le domaine mini‑shop et où sont leurs frontières. On a trois zones de sens — le catalogue, les stocks et les commandes — ce qui se reflète bien dans les tables : les référentiels vivent séparément, les quantités en stock séparément, et une commande est une entité à part avec ses propres lignes de détail.

Voici un schéma simple des relations. Ce n'est pas un « UML magique », c'est juste un guide : qui fait référence à qui via FOREIGN KEY, où on a une relation « un à plusieurs » et où c'est « un à un ».

erDiagram
    CATEGORY ||--o{ PRODUCT : contains
    PRODUCT ||--|| STOCK_ITEM : has
    CUSTOMER_ORDER ||--o{ ORDER_ITEM : includes
    PRODUCT ||--o{ ORDER_ITEM : ordered_as

Pour éviter toute confusion, adoptons une convention importante : chaque table aura une clé primaire technique id (pratique pour les références), et aussi des identifiants métier, qui sont importants et doivent être uniques. Pour la catégorie c'est code, pour le produit — sku, pour la commande — order_number. Autrement dit, id c'est le « numéro de passeport de la ligne », tandis que code/sku/order_number c'est ce que le métier voit réellement et qu'on ne peut pas dupliquer.

2. La table category

La catégorie est un bon point de départ, parce que c'est un référentiel : les données changent rarement ici, mais sont utilisées souvent. Dans une boutique, les catégories sont comme les panneaux au-dessus des rayons : « Livres », « Jeux », « Sport ». Si on laisse créer deux catégories avec le même code, il faudra ensuite démêler en héros pourquoi la moitié des produits est dans BOOKS et l'autre moitié dans un autre BOOKS.

Faisons la table category avec des champs simples : code et name sont obligatoires, code est aussi unique. On ajoute description comme champ optionnel et active comme indicateur « la catégorie est encore utilisée ».

-- Référentiel des catégories : identifiant métier code + id technique
CREATE TABLE category (
    id BIGINT PRIMARY KEY,                 -- PK technique pour les références
    code VARCHAR(50) NOT NULL UNIQUE,       -- code métier de la catégorie (unique)
    name VARCHAR(100) NOT NULL,             -- nom affiché de la catégorie
    description VARCHAR(500),               -- description optionnelle
    active BOOLEAN NOT NULL                 -- on stocke explicitement l'état actif (sans état NULL)
);

Remarque la logique des contraintes : NOT NULL sur name — c'est « une catégorie sans nom n'a pas de sens », et UNIQUE sur code — c'est « le code est un identifiant stable, il ne doit pas se répéter ». Le champ active on le met NOT NULL, parce que l'état « on ne sait pas si la catégorie est active » est une mauvaise idée. Même si on ne sait pas encore comment désactiver les catégories, avoir la capacité de stocker cet état est déjà utile.

Pour ancrer ça dans la réalité, voici un exemple simple d'insertion. On n'entraîne pas INSERT comme sujet à part (c'est une autre discussion), mais un petit exemple aide à « faire vivre » la table et à vérifier qu'elle est cohérente.

-- Exemple d'un enregistrement du référentiel
INSERT INTO category (id, code, name, description, active)
VALUES (1, 'BOOKS', 'Books', 'All kinds of books', true);

3. La table product

Le produit est l'entité centrale du catalogue, mais c'est ici que commencent les erreurs typiques comme « et si on mettait le nom de la catégorie directement dans le produit en chaîne ». Ça semble pratique jusqu'au premier cas où la catégorie a été renommée et tu découvres soudain que Books apparaît sous trois variantes : Books, Book, Livres. C'est pourquoi le produit doit référencer la catégorie par clé, pas copier ses données.

Le produit aura un sku (identifiant métier unique), un name, un status et un price. Et surtout — category_id comme référence obligatoire vers category(id).

-- Produits du catalogue : sku unique + référence à la catégorie via FK
CREATE TABLE product (
    id BIGINT PRIMARY KEY,                 -- PK technique
    sku VARCHAR(50) NOT NULL UNIQUE,        -- référence métier (unique)
    name VARCHAR(200) NOT NULL,             -- nom du produit
    status VARCHAR(30) NOT NULL,            -- statut (par ex. ACTIVE / DISABLED)
    price NUMERIC(10, 2) NOT NULL,          -- prix avec précision au centime
    category_id BIGINT NOT NULL,            -- référence obligatoire vers la catégorie
    FOREIGN KEY (category_id) REFERENCES category(id) -- la FK protège des "catégories-chaînes"
);

Il faut sentir le sens de chaque décision. On marque sku UNIQUE, parce que c'est la « référence » du produit : si tu as deux produits avec le même sku, tu ne sais plus ce que tu vends. On met price en NUMERIC(10, 2), parce que l'argent aime la précision. Si on stocke le prix en double, un jour on obtiendra un total de commande à 599.9999999997 et on commencera à soupçonner qu'un poltergeist s'est installé dans le magasin. En réalité, ce sont juste les particularités du floating point.

On met category_id en NOT NULL, parce qu'un « produit sans catégorie » signifie presque toujours soit « le modèle n'est pas complet », soit « les données sont corrompues ». Oui, dans la vraie vie il y a des produits « Sans catégorie », mais c'est en général une catégorie à part, pas un NULL.

Et voici une petite requête qui nous montre le produit avec le nom de sa catégorie. C'est exactement l'illustration que la normalisation ne gêne pas la lecture si on sait faire des JOIN.

-- Lecture des produits avec la catégorie via JOIN sur la FK
SELECT p.sku, p.name, c.name AS category_name
FROM product p
JOIN category c ON c.id = p.category_id  -- on relie le produit à sa catégorie
ORDER BY p.name;

4. La table stock_item

Quand on construit un projet d'apprentissage, on a très envie d'« économiser les tables » : mettons available_quantity directement dans product et c'est parti. Mais c'est une économie un peu comme « mettons les chaussettes et la soupe dans la même casserole — moins de vaisselle ». Le catalogue et les stocks obéissent à des règles différentes : les données du catalogue changent relativement rarement, tandis que les stocks changent souvent, parfois plusieurs fois par minute. Si on les mélange, la table des produits commence à mener la vie nerveuse d'un entrepôt.

stock_item est une table séparée qui stocke les quantités et est reliée au produit. On fixe explicitement la règle « un produit — un enregistrement de stock » via UNIQUE sur product_id.

-- Stocks par produit : un produit -> un enregistrement avec les quantités
CREATE TABLE stock_item (
    id BIGINT PRIMARY KEY,                 -- PK technique
    product_id BIGINT NOT NULL UNIQUE,     -- 1:1 avec product (UNIQUE fixe la règle)
    available_quantity INT NOT NULL,        -- disponible à la vente
    reserved_quantity INT NOT NULL,         -- réservé (pas encore débité)
    FOREIGN KEY (product_id) REFERENCES product(id) -- FK vers le produit
);

Ici, available_quantity — ce qu'on peut vendre maintenant, et reserved_quantity — ce qui est déjà « réservé » lors de la passation de commandes (ou pour des opérations en attente). Même si tu n'implémentes pas encore une mécanique de réserve complexe, le modèle avec deux champs aide à expliquer la logique normale des commandes, où on ne peut pas toujours juste « soustraire de available ».

Et oui, les deux champs en NOT NULL, parce que NULL dans des quantités, c'est presque toujours « on ne sait pas ce qui se passe ». Zéro — c'est clair, NULL — c'est de la philosophie : « la quantité est inconnue mais on vend quand même ». Évite ça.

Mini-requête pour voir les stocks avec le produit :

-- Lecture rapide des stocks pour avoir une vue d'ensemble de l'entrepôt
SELECT p.sku, p.name, s.available_quantity, s.reserved_quantity
FROM stock_item s
JOIN product p ON p.id = s.product_id     -- on relie les stocks au produit
ORDER BY p.name;

5. La commande : customer_order et order_item

Les commandes, c'est là où on voit le mieux pourquoi la normalisation est nécessaire. Une commande a une « en-tête » (numéro, email, montant) et des « lignes » (quel produit, combien d'exemplaires, à quel prix). Si on essaie de tout mettre dans une seule table, on obtient soit la répétition de l'en-tête pour chaque ligne, soit on stocke la liste des produits dans une seule colonne — et c'est la voie vers la souffrance, les larmes et des requêtes du genre « trouve les commandes où il y a le produit X » qui deviennent de l'archéologie.

Créons d'abord customer_order. L'identifiant métier clé ici est order_number, il doit être unique. customer_email est obligatoire. total_amount aussi, parce qu'une commande sans montant paraît étrange (même si le montant est nul — mieux vaut stocker 0.00 que NULL).

-- "En-tête" de commande : numéro unique + données client + total
CREATE TABLE customer_order (
    id BIGINT PRIMARY KEY,                 -- PK technique
    order_number VARCHAR(30) NOT NULL UNIQUE, -- numéro métier de la commande
    customer_email VARCHAR(255) NOT NULL,   -- contact du client
    total_amount NUMERIC(10, 2) NOT NULL    -- total de la commande (stocké explicitement)
);

Maintenant la table order_item. Elle stocke les lignes de la commande. Deux références ici : order_id vers customer_order(id) et product_id vers product(id). La quantité — quantity. Le prix de la ligne — unit_price. Et le total de la ligne — line_total.

-- "Lignes" de commande : produit, quantité, prix au moment de l'achat et total de la ligne
CREATE TABLE order_item (
    id BIGINT PRIMARY KEY,                 -- PK technique
    order_id BIGINT NOT NULL,              -- référence vers la commande
    product_id BIGINT NOT NULL,            -- référence vers le produit
    quantity INT NOT NULL,                 -- nombre d'exemplaires
    unit_price NUMERIC(10, 2) NOT NULL,    -- prix au moment de la commande (pas du catalogue)
    line_total NUMERIC(10, 2) NOT NULL,    -- total de la ligne (quantity * unit_price)
    FOREIGN KEY (order_id) REFERENCES customer_order(id), -- FK vers l'"en-tête"
    FOREIGN KEY (product_id) REFERENCES product(id)       -- FK vers le produit
);

Pourquoi stocker unit_price alors que le prix est déjà dans product.price ? Parce que le prix du produit peut changer demain, et une commande c'est un « instantané de la transaction ». Si la commande stocke seulement product_id et consulte à chaque fois le prix actuel du produit, dans une semaine une vieille commande coûtera soudain plus cher simplement parce qu'un responsable a changé le tarif. Ce n'est plus une boutique, c'est une machine à remonter le temps.

Pourquoi stocker line_total séparément alors qu'il se calcule comme quantity * unit_price ? Dans un projet d'apprentissage, on pourrait ne pas le stocker, mais le stockage séparé est pratique comme résultat de calcul figé au moment de la commande. Ça simplifie la lecture et aide à détecter les bugs : si line_total ne correspond pas au produit, c'est qu'il y a eu un problème dans la logique de passation de commande.

Et voici la requête qui lit le contenu d'une commande en une seule image : numéro de commande, produits, quantités et montants.

-- Contenu de la commande : on joint l'"en-tête", les lignes et les produits
SELECT o.order_number, p.name, oi.quantity, oi.line_total
FROM customer_order o
JOIN order_item oi ON oi.order_id = o.id  -- lignes de la commande spécifique
JOIN product p ON p.id = oi.product_id    -- on récupère le nom du produit
WHERE o.order_number = 'ORD-1001'
ORDER BY p.name;

6. Vérification du schéma via JOIN

Un bon schéma, ce n'est pas celui qui est « joli sur le papier », c'est celui qui répond aux vraies questions du métier sans acrobaties. Faisons donc une courte vérification de bon sens : imaginons quelques questions typiques et voyons si elles s'expriment facilement en requêtes SELECT. Ce n'est pas du « reporting de niveau corporate », ce sont des lectures basiques qui doivent paraître naturelles même si tu t'habitues encore à SQL.

Question 1 : afficher les produits avec la catégorie, triés, avec pagination.

-- Catalogue avec catégorie + pagination via LIMIT/OFFSET
SELECT p.id, p.sku, p.name, p.price, c.name AS category_name
FROM product p
JOIN category c ON c.id = p.category_id   -- relation produit-catégorie
ORDER BY p.id
LIMIT 20 OFFSET 0;

Question 2 : afficher les stocks par produit (pour voir rapidement ce qui s'épuise).

-- Produits avec peu de stock disponible (tri croissant)
SELECT p.sku, p.name, s.available_quantity
FROM stock_item s
JOIN product p ON p.id = s.product_id     -- des stocks on va vers le produit
ORDER BY s.available_quantity ASC
LIMIT 10;

Question 3 : afficher le contenu d'une commande par son numéro.

-- Par numéro de commande on récupère les produits et quantités dans les lignes
SELECT o.order_number, p.sku, p.name, oi.quantity
FROM customer_order o
JOIN order_item oi ON oi.order_id = o.id  -- lignes de la commande
JOIN product p ON p.id = oi.product_id    -- données du produit
WHERE o.order_number = 'ORD-1001';

Si ces requêtes paraissent naturelles, c'est qu'on est sur la bonne voie : le schéma est normalisé, les relations sont exprimées par des clés, et la lecture ne nécessite pas de stocker des données dupliquées dans chaque ligne.

Avec cette carte en tête, il devient plus facile de parler non seulement de lecture, mais aussi de la façon dont les données changent : où apparaît INSERT, ce que met à jour UPDATE, et pourquoi le côté écriture bute presque toujours rapidement sur la logique transactionnelle.

7. Erreurs typiques dans le schéma mini-shop

Erreur n°1 : stocker la catégorie directement dans product sous forme de chaînes.
category_code et category_name dans la même table semblent pratiques tant que les données sont petites. Mais la moindre faute de frappe ("Books " vs "Books") crée une nouvelle « catégorie ». Sans FOREIGN KEY, la base ne te protège pas de ces divergences. La catégorie est une entité à part, pas une chaîne « au cas où ».

Erreur n°2 : mélanger le catalogue et les stocks dans une seule table.
Ajouter stock_quantity dans product paraît plus simple que d'introduire stock_item. Mais tu mélanges ainsi deux sens différents : la description du produit et son état actuel. Du coup, la table catalogue commence à être constamment mise à jour et perd sa stabilité de référentiel.

Erreur n°3 : oublier UNIQUE sur les champs clés.
Sans UNIQUE sur sku, la base accepte tranquillement des doublons, et le problème ne se révèle que plus tard — par exemple lors d'une recherche de produit. Les contraintes ne sont pas un « supplément », elles font partie du modèle. Elles protègent les données là où le code peut se tromper.

Erreur n°4 : ne pas fixer le prix dans order_item.
Si on prend le prix depuis product.price, on lie la commande à l'état actuel du catalogue. En changeant le prix, les anciennes commandes sont « recalculées », ce qui brise le sens de la commande comme document historique. unit_price est nécessaire pour fixer le fait de l'achat dans le temps.

Erreur n°5 : stocker des listes de valeurs dans une seule colonne (en chaîne ou en JSON).
"SKU1,SKU2,SKU3" ou du JSON dans une colonne semble une voie rapide, mais ça casse les capacités de la base : filtrage, indexation, relations. Toute analyse devient une recherche de texte. Le bon modèle — une table séparée order_item, où chaque élément vit comme une ligne.