Cas pratique : le schéma d'une boutique en ligne

CREATE TABLE clients (
    id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email    text        NOT NULL UNIQUE,
    nom      text        NOT NULL,
    cree_le  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE produits (
    id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku    text           NOT NULL UNIQUE,
    nom    text           NOT NULL,
    prix   numeric(12, 2) NOT NULL CHECK (prix >= 0),
    stock  integer        NOT NULL DEFAULT 0 CHECK (stock >= 0)
);

CREATE TABLE commandes (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    client_id  bigint      NOT NULL REFERENCES clients (id),
    statut     text        NOT NULL DEFAULT 'en_attente'
               CHECK (statut IN ('en_attente', 'payee', 'expediee', 'annulee')),
    cree_le    timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ON commandes (client_id, cree_le DESC);

CREATE TABLE lignes_commande (
    commande_id    bigint  NOT NULL REFERENCES commandes (id) ON DELETE CASCADE,
    produit_id     bigint  NOT NULL REFERENCES produits (id),
    quantite       integer NOT NULL CHECK (quantite > 0),
    prix_unitaire  numeric(12, 2) NOT NULL,        -- copié : le prix du catalogue peut changer
    PRIMARY KEY (commande_id, produit_id)
);
CREATE INDEX ON lignes_commande (produit_id);

UPDATE produits
SET    stock = stock - $1
WHERE  id = $2 AND stock >= $1
RETURNING stock;            -- 0 ligne : stock insuffisant, on refuse la commande