SQL & MySQL

La vraie puissance d'une base de données relationnelle vient des relations entre les tables. Vous avez déjà vu une relation simple : products.category_id pointe vers categories.id. Mais un e-commerce a besoin de bien plus : des clients, des commandes, des lignes de commande. Ce chapitre explique en profondeur les types de relations et vous fait créer les tables clients et commandes de SunuShop.

Rappel : la clé étrangère

Une clé étrangère (FOREIGN KEY) est une colonne dans une table qui fait référence à la clé primaire d'une autre table. MySQL vérifie automatiquement que la valeur référencée existe. Si vous essayez d'insérer une commande avec customer_id = 99 et que le client 99 n'existe pas, MySQL refuse avec une erreur "foreign key constraint fails". C'est l'intégrité référentielle — la garantie que vos données sont cohérentes.

Pour que la clé étrangère fonctionne, les deux colonnes doivent avoir exactement le même type. Si categories.id est INT UNSIGNED, alors products.category_id doit être INT UNSIGNED aussi. Un INT signé et un INT UNSIGNED ne sont pas compatibles pour une FK — MySQL refusera silencieusement.

Relation un-à-plusieurs (1-N) — la plus courante

Une catégorie a PLUSIEURS produits. Un produit appartient à UNE seule catégorie. C'est une relation 1-N. La clé étrangère est toujours dans la table du côté "plusieurs" (le côté N). Ici, category_id est dans products (pas dans categories).

Autres exemples 1-N dans SunuShop :

• Un client a plusieurs commandes → customer_id est dans orders
• Une commande a plusieurs lignes → order_id est dans order_items
• Un produit a plusieurs avis → product_id est dans reviews

Relation plusieurs-à-plusieurs (N-N)

Un produit peut avoir PLUSIEURS tags, et un tag peut être sur PLUSIEURS produits. Impossible avec une simple clé étrangère — il faudrait mettre plusieurs tag_id dans products, ce qui n'est pas autorisé en SQL relationnel.

La solution : une table pivot (ou table d'association) qui contient deux clés étrangères. La table product_tag (créée au chapitre 5) a product_id ET tag_id. Chaque ligne représente une association "ce produit a ce tag". La clé primaire est composite (product_id, tag_id) pour empêcher les doublons.

Relation un-à-un (1-1) — rare

Un client a UNE seule adresse de livraison principale. On pourrait mettre l'adresse directement dans la table customers (colonnes address, city, zip), ce qui est souvent suffisant. Ou créer une table séparée customer_addresses avec une clé étrangère UNIQUE vers customers.id — la contrainte UNIQUE garantit qu'il n'y a qu'une seule adresse par client.

En pratique, le 1-1 est utilisé quand les données sont optionnelles et volumineuses (profil détaillé, préférences) ou quand on veut séparer les responsabilités (adresse de facturation vs livraison).

La normalisation — pourquoi séparer les données en tables

Vous pourriez stocker TOUT dans une seule table géante : nom du produit, nom de la catégorie, nom du client, date de commande, montant du paiement — une ligne par produit commandé. Ça fonctionnerait... mais ce serait un cauchemar.

La normalisation est le processus de structuration qui élimine la redondance et les incohérences. Elle se définit en "formes normales" :

1NF (Première Forme Normale) — chaque cellule contient UNE seule valeur, pas une liste. Mauvais : tags = "bio, promo, artisanal" (une string avec plusieurs valeurs). Bon : une table pivot product_tag avec une ligne par association. C'est ce qu'on a fait au chapitre 5.

2NF (Deuxième Forme Normale) — 1NF respectée + chaque colonne non-clé dépend de TOUTE la clé primaire, pas d'une partie. Exemple : si la table order_items avait une colonne product_name, elle dépendrait de product_id (une partie de la clé), pas de la combinaison order_id + product_id. Le nom du produit doit être dans la table products, pas dans order_items. C'est ce qu'on fait avec les clés étrangères.

3NF (Troisième Forme Normale) — 2NF respectée + aucune colonne non-clé ne dépend d'une autre colonne non-clé. Exemple : si customers avait une colonne city et une colonne city_region, city_region dépend de city (pas de l'id client). Solution : une table séparée cities avec id, name, region.

En pratique, la plupart des bases de données professionnelles sont en 3NF. Le schéma SunuShop que nous construisons respecte la 3NF : chaque information n'existe qu'à un seul endroit, et les tables sont liées par des clés étrangères.

La dénormalisation — parfois, on CASSE volontairement la normalisation pour la performance. Le champ orders.total est un exemple : il stocke la somme de order_items alors qu'on pourrait la recalculer à chaque fois. C'est redondant, mais ça évite de refaire le SUM() sur chaque affichage. La dénormalisation est un compromis assumé entre pureté du schéma et vitesse de lecture.

Retenez : en entretien d'embauche, on vous demandera "qu'est-ce que la normalisation ?" Réponse : "C'est le processus d'organisation des tables pour éliminer la redondance. La 1NF garantit des valeurs atomiques, la 2NF élimine les dépendances partielles, la 3NF élimine les dépendances transitives."

ON DELETE — que se passe-t-il quand le parent est supprimé ?

Quand vous supprimez un client qui a des commandes, MySQL doit savoir quoi faire des commandes orphelines :

• RESTRICT (défaut) — Empêcher la suppression. MySQL refuse tant que des enfants existent. C'est le plus sûr. Utilisez-le quand les enfants ont de la valeur (commandes, paiements).
• CASCADE — Supprimer en cascade. Supprimer le parent supprime automatiquement tous les enfants. Logique pour order_items (supprimer une commande = supprimer ses lignes) et reviews (supprimer un produit = supprimer ses avis).
• SET NULL — Mettre à NULL. Les enfants restent mais perdent leur référence parent. Logique si un employé quitte l'entreprise — ses tickets restent mais employee_id devient NULL.
• NO ACTION — Identique à RESTRICT en MySQL (pas en PostgreSQL où c'est différent).

Le choix dépend de la logique métier. Une règle pratique : RESTRICT pour les relations "commerciales" (client → commandes), CASCADE pour les relations "techniques" (commande → lignes de commande, produit → avis).

ON UPDATE CASCADE

Moins courant mais utile : si l'id du parent change (rare avec AUTO_INCREMENT), les enfants se mettent à jour automatiquement. En pratique, on change rarement les clés primaires.