Types de Relations entre Tables dans MySQL/MariaDB

par lena · 29 juin 2022

Les bases de données relationnelles permettent de définir différents types de relations entre les tables afin de modéliser des scénarios complexes du monde réel de manière efficace. Cet article complète l’article précédent, « Introduction aux Bases de Données Relationnelles« , en explorant plus en détail les types de relations entre les tables. Comprendre ces relations est crucial pour structurer les données de manière à répondre aux exigences de cohérence, de performance, et de flexibilité des systèmes d’information modernes.

Les trois principaux types de relations sont : Un à Un, Un à Plusieurs, et Plusieurs à Plusieurs. Voyons en détail chacun de ces types, en les illustrant avec les tables Clients, Commandes, Produits, et DetailsClients.

Les diagrammes Entité-Relation (ERD) sont souvent utilisés pour visualiser les types de relations entre les tables. Ces diagrammes aident à mieux comprendre la structure des données et les liens qui existent entre les différentes entités de la base. Des outils tels que MySQL Workbench ou le Concepteur de PHPMyAdmin permettent de créer et de visualiser facilement ces relations, facilitant ainsi le processus de conception.

💻 MySQL Workbench vs Concepteur de PHPMyAdmin

« MySQL Workbench est idéal pour la conception détaillée et la documentation de bases de données grâce à ses fonctionnalités avancées, tandis que le Concepteur de PHPMyAdmin est un outil pratique intégré à l’interface web, parfait pour des visualisations et modifications rapides des relations entre tables. »

Relation Un à Un

Une relation Un à Un signifie que chaque enregistrement dans une table est lié à un seul enregistrement dans une autre table. Ce type de relation est utile lorsque l’on souhaite diviser les informations d’une entité en plusieurs tables pour des raisons de modularité ou de sécurité. Par exemple, il est souvent pertinent de stocker les informations sensibles ou rarement utilisées dans une table séparée pour optimiser les performances et la gestion des accès.

En complément de la table Clients qui contient des informations de base sur les clients, nous pourrions ajouter une table DetailsClients qui contient des informations supplémentaires, comme des préférences de contact, des remarques personnelles, ou des détails de facturation spécifiques.

CREATE TABLE DetailsClients (
    detail_id INT PRIMARY KEY AUTO_INCREMENT,
    client_id INT UNIQUE,
    preferences_contact VARCHAR(255),
    remarques TEXT,
    FOREIGN KEY (client_id) REFERENCES Clients(client_id)
) ENGINE=InnoDB;

La table DetailsClients possède une colonne client_id qui est à la fois une clé étrangère pointant vers la table Clients et une colonne avec une contrainte UNIQUE. Cette contrainte UNIQUE assure qu’il n’y aura qu’une seule ligne dans DetailsClients pour chaque client, établissant ainsi une relation Un à Un.

Cette structure permet de diviser les informations sur les clients entre des données de base et des données complémentaires, qui peuvent être accédées séparément. La clé étrangère client_id dans DetailsClients garantit que chaque enregistrement est associé à un client existant dans la table Clients.

Dans PHPMyAdmin, cette relation peut être créée visuellement en définissant la colonne client_id dans DetailsClients comme une référence à la clé primaire client_id de la table Clients et en ajoutant la contrainte UNIQUE. Cette approche garantit une relation univoque entre les clients et leurs détails supplémentaires.

Les relations Un à Un sont simples à gérer, mais si les informations des deux tables sont souvent utilisées ensemble, les jointures peuvent entraîner des coûts de performance non négligeables.

Il est conseillé de créer des relations Un à Un uniquement lorsque les données doivent être séparées pour des raisons de modularité ou de sécurité.

Relation Un à Plusieurs

La relation Un à Plusieurs est l’une des relations les plus courantes dans les bases de données relationnelles. Dans une relation Un à Plusieurs, un enregistrement dans une table est lié à plusieurs enregistrements dans une autre table. Ce type de relation est souvent utilisé pour des entités dépendantes, comme des commandes associées à des clients.

Prenons l’exemple des tables Clients et Commandes. Un client peut passer plusieurs commandes, mais chaque commande est associée à un seul client. Cela signifie qu’un enregistrement dans la table Clients peut être lié à plusieurs enregistrements dans la table Commandes.

CREATE TABLE Commandes (
    commande_id INT PRIMARY KEY AUTO_INCREMENT,
    client_id INT,
    date_commande DATE,
    montant DECIMAL(10, 2),
    FOREIGN KEY (client_id) REFERENCES Clients(client_id)
) ENGINE=InnoDB;

Dans cette structure, chaque enregistrement de la table Commandes est associé de manière unique à un client, établissant ainsi une relation Un à Plusieurs. Cela permet de savoir quel client a passé quelle commande et d’associer plusieurs commandes à un même client.

Dans PHPMyAdmin, cette relation peut être créée en ajoutant une colonne client_id dans Commandes et en la définissant comme une référence à la clé primaire client_id de Clients. Cela garantit l’intégrité référentielle, empêchant toute commande d’exister sans un client valide.

Pour optimiser la performance dans une relation Un à Plusieurs, l’utilisation d’index sur les colonnes de clés étrangères est recommandée, surtout lorsque le volume de données dans la table dépendante est important.

Pour garantir la cohérence et la performance, chaque colonne de clé étrangère dans une relation Un à Plusieurs doit être indexée.

Relation Plusieurs à Plusieurs

Une relation Plusieurs à Plusieurs survient lorsqu’un enregistrement dans une table peut être lié à plusieurs enregistrements dans une autre table, et vice versa. Pour modéliser ce type de relation, une table de jonction intermédiaire est nécessaire.

Par exemple, pour gérer les produits commandés par nos clients, nous devons introduire une table Produits qui contient les informations sur les produits disponibles à la vente. Ensuite, une table de jonction Clients_Produits_Commandes permet de lier les clients, les commandes, et les produits commandés.

CREATE TABLE Produits (
    produit_id INT PRIMARY KEY AUTO_INCREMENT,
    nom_produit VARCHAR(100) NOT NULL,
    prix DECIMAL(10, 2) NOT NULL
) ENGINE=InnoDB;

Cette table Produits contient les informations de base sur chaque produit, comme son nom et son prix.

Création de la Table de Jonction (Clients_Produits_Commandes) : Pour gérer les relations entre les clients, les produits, et les commandes, nous créons une table de jonction appelée Clients_Produits_Commandes. Cette table de jonction relie les trois tables en utilisant des clés étrangères :

CREATE TABLE Clients_Produits_Commandes (
    client_id INT,
    produit_id INT,
    commande_id INT,
    PRIMARY KEY (client_id, produit_id, commande_id),
    FOREIGN KEY (client_id) REFERENCES Clients(client_id),
    FOREIGN KEY (produit_id) REFERENCES Produits(produit_id),
    FOREIGN KEY (commande_id) REFERENCES Commandes(commande_id)
) ENGINE=InnoDB;

Cette table de jonction Clients_Produits_Commandes contient trois colonnes qui agissent comme clés étrangères (client_id, produit_id, commande_id). Cela permet de stocker les informations sur les produits commandés par chaque client dans le cadre d’une commande spécifique.

Grâce à cette structure, il est possible de gérer un scénario où un client peut commander plusieurs produits, et chaque produit peut être commandé par plusieurs clients, dans le cadre de différentes commandes. Ce type de relation est essentiel pour des systèmes de vente qui veulent conserver une trace précise des articles vendus, des clients qui les ont achetés, et des commandes auxquelles ces produits appartiennent.

Dans PHPMyAdmin, cette relation peut être visualisée en créant la table de jonction Clients_Produits_Commandes et en utilisant les outils de gestion des clés étrangères disponibles dans l’interface. Cela facilite la compréhension et la gestion de cette relation complexe.

Les relations Plusieurs à Plusieurs peuvent devenir coûteuses en termes de performance lorsqu’elles impliquent de grandes tables de jonction. L’utilisation de JOINS multiples nécessite une planification des index pour garantir l’efficacité des requêtes.

Lorsque vous modélisez une relation Plusieurs à Plusieurs, la création d’une clé primaire composite sur la table de jonction est recommandée pour éviter les doublons.

Relations impliquant plus de deux tables

Dans des systèmes complexes, il est fréquent de devoir gérer des relations impliquant plus de deux tables. Par exemple, la relation entre Clients, Commandes, et Produits nécessite de naviguer à travers plusieurs niveaux de relations pour extraire des informations complètes sur les commandes passées par chaque client et les produits achetés.

Pour ce genre de scénarios, l’utilisation de JOINS multiples est souvent nécessaire. Voici un exemple de requête SQL qui récupère des informations sur les commandes, incluant le nom du client et les détails des produits commandés.

SELECT Clients.nom, Commandes.commande_id, Produits.nom_produit, Produits.prix
FROM Clients
JOIN Commandes ON Clients.client_id = Commandes.client_id
JOIN Clients_Produits_Commandes ON Commandes.commande_id = Clients_Produits_Commandes.commande_id
JOIN Produits ON Clients_Produits_Commandes.produit_id = Produits.produit_id;

Dans cette requête :

La table Clients est jointe à la table Commandes grâce à la clé étrangère client_id.
Ensuite, la table Commandes est jointe à la table de jonction Clients_Produits_Commandes à l’aide de la clé commande_id.
Enfin, la table Produits est jointe pour obtenir les détails des produits commandés.

Ce type de requête permet d’obtenir une vue complète des clients, des commandes qu’ils ont passées, et des produits associés. Toutefois, il est essentiel de bien concevoir les index et d’optimiser la requête pour maintenir une bonne performance, surtout lorsque le volume de données est important.

Actions de Clés Étrangères : `ON DELETE` et `ON UPDATE`

Pour garantir la cohérence et l’intégrité des données, il est indispensable de bien gérer les clés étrangères et de définir clairement les relations entre les tables. Les relations doivent toujours être conçues de manière à préserver l’intégrité référentielle, c’est-à-dire en assurant que les modifications dans une table ne laissent pas d’enregistrements orphelins dans les tables dépendantes. Cela implique de réfléchir soigneusement aux options telles que ON DELETE CASCADE, ON DELETE SET NULL, ou RESTRICT, et de choisir celle qui correspond le mieux aux besoins fonctionnels et aux exigences de cohérence de l’application.

Lors de la création de relations entre les tables avec des clés étrangères, il est important de spécifier ce qui doit se produire lorsque des modifications (suppression ou mise à jour) sont faites dans la table référencée. Ces actions sont spécifiées par des clauses telles que ON DELETE et ON UPDATE. Voici les différentes options :

CASCADE

Lorsque l’enregistrement référencé est supprimé ou mis à jour, toutes les lignes qui dépendent de cet enregistrement dans les autres tables sont également supprimées ou mises à jour. Par exemple :

FOREIGN KEY (client_id) REFERENCES Clients(client_id) ON DELETE CASCADE ON UPDATE CASCADE

Ici, si un client est supprimé, toutes ses commandes seront également supprimées.

SET NULL

Lorsque l’enregistrement référencé est supprimé, la clé étrangère de la table dépendante est définie sur NULL. Cela est utile si l’on souhaite garder l’enregistrement dépendant, mais sans la relation.

FOREIGN KEY (client_id) REFERENCES Clients(client_id) ON DELETE SET NULL

Cela signifie que si un client est supprimé, le client_id dans la table des commandes sera mis à NULL.

RESTRICT

Empêche la suppression ou la mise à jour d’un enregistrement référencé tant qu’il existe des enregistrements dépendants. Cela permet de protéger la cohérence des données. Par exemple, on ne peut pas supprimer un client s’il a encore des commandes enregistrées :

FOREIGN KEY (client_id) REFERENCES Clients(client_id) ON DELETE RESTRICT

NO ACTION

Similaire à RESTRICT, mais la vérification des contraintes est différée, c’est-à-dire que la suppression ou la mise à jour est annulée uniquement si elle enfreint une contrainte, après la tentative.

FOREIGN KEY (client_id) REFERENCES Clients(client_id) ON DELETE NO ACTION ON UPDATE NO ACTION

Avec cette définition, si une action de suppression ou de mise à jour est tentée sur un enregistrement référencé dans la table Clients, et qu’il existe des dépendances dans la table Commandes, l’action sera annulée, empêchant ainsi toute modification qui pourrait briser l’intégrité référentielle. L’option NO ACTION fonctionne de manière similaire à RESTRICT, en ce sens qu’elle ne permet pas de supprimer ou de mettre à jour tant qu’il y a des enregistrements dépendants, sauf que la vérification peut être différée dans certaines circonstances.

Ces actions permettent de mieux contrôler l’intégrité des données lorsque des modifications sont apportées. Le choix de l’action appropriée dépend de la logique de l’application et des exigences de cohérence des données.

Conclusion des Types de Relations

Ces trois types de relations (Un à Un, Un à Plusieurs, et Plusieurs à Plusieurs) permettent de modéliser des scénarios réels de manière structurée et efficace. Ils sont à la base de la conception de bases de données relationnelles et garantissent une bonne organisation des données, leur intégrité, et leur interconnexion. En utilisant des clés primaires et étrangères, MySQL et MariaDB permettent d’assurer que les données restent cohérentes, même lorsqu’elles sont distribuées sur plusieurs tables.

Avec un outil comme PHPMyAdmin, il est facile de visualiser ces relations grâce à ses fonctionnalités graphiques. En utilisant les diagrammes et les outils de gestion des relations, les développeurs peuvent facilement établir les connexions nécessaires entre les différentes entités de leur base de données.