Relations entre les tables d'une base MySql

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Relations entre les tables MySql

Cette formation est particulièrement importante pour toute personne désireuse d'intégrer le grand bain professionnel du développement Web. Il est question d'architecturer les tables de la base de données sur laquelle repose le fonctionnement de tout site internet d'envergure.



Beaucoup de conceptions amateures consistent à réduire le nombre de tables. Dans ces conditions, elles sont souvent constituées d'une grande quantité de champs pour réunir l'information au même endroit. Il en résulte des répétitions de données aux fâcheuses conséquences. Les ressources consommées sont importantes et les temps de réponse sont directement impactés.

Prenons l'exemple d'un client passant commande. Rien n'empêche ce client d'être fidèle. Nous n'allons pas rappeler les informations qui le concernent à chaque commande dans une unique table. Ses caractéristiques doivent être archivées une bonne fois pour toutes dans une table parente. Les commandes doivent être archivées dans une table enfant liée à cette table parente. Ainsi nous allégeons le poids des fichiers et améliorons le processus de requêtage.

Source et présentation de la problématique
Pour bien comprendre le mécanisme relationnel à établir entre les tables d'une base MySql, nous proposons de récupérer des données existantes.

• Télécharger le fichier stocks.sql en cliquant sur son lien,
• Lancer EasyPhp,
• Puis démarrer les deux serveurs (Http et Database Server),
• Cliquer alors sur le bouton Open du module Mysql Administration,

Nous accédons ainsi à l'interface d'administration de PhpMyAdmin. Le fichier Sql que nous avons réceptionné articule les tables d'une petite base de données. Nous exploiterons cette dernière pour construire une application Web de facturation avec gestion des stocks. Mais avant cela, nous devons créer la base de données permettant d'accueillir ces tables.

• Dans le volet de gauche de l'interface, cliquer sur le lien Nouvelle base de données,
• Dans la première zone de saisie au centre, saisir le nom Stocks,
• Avec la liste déroulante sur sa droite, définir l'interclassement sur utf8_general_ci,

Nous avions donné la définition de ce système d'encodage dans la formation pour créer sa première base de données MySql.

• Cliquer alors sur le bouton Créer,

Une fois le processus terminé, la base de données MySql apparaît listée dans le volet de gauche. Mais elle est vierge de tables à ce stade.

• Cliquer sur son lien dans ce volet pour la désigner,
• En haut de l'interface de PhpMyAdmin, cliquer sur l'onglet Importer,
• Au centre de l'écran, cliquer alors sur le bouton Parcourir,
• Double cliquer sur le fichier stocks.sql précédemment importé,
• Enfin, tout en bas de l'écran, cliquer sur le bouton Exécuter pour procéder à l'importation,

Nous avions démontré les subtilités des réglages à opérer dans la formation Php pour importer des données MySql. L'opération peut durer plusieurs secondes. A l'issue, un message atteste du bon déroulement des actions. Dans l'arborescence de la base Stocks, vous constatez désormais la présence de quelques tables.

• Cliquer sur le lien de la table articles pour afficher son contenu,

Comme vous le constatez, cette table fort simple, archive des produits du magasin, proposés à la vente. Ils sont tous identifiés de façon unique par le champ Article_code. Et nous allons bientôt le constater. Chaque produit est référencé par sa désignation, son prix unitaire hors taxe et sa quantité en stock.

• En haut de l'interface de PhpMyAdmin, cliquer sur l'onglet Structure,

Nous affichons ainsi la structure de la table désignée.



Nous y retrouvons les quatre champs et pour chacun d'eux, le type de données associé. Les colonnes Article_code et Article_designation sont définies comme des champs de type texte (Varchar). Le champ Article_PUHT est un réel simple (float) pour archiver les prix avec décimales. Fort naturellement, le champ Article_Qte est réglé sur le type Entier (Int). Nous pourrions d'ailleurs envisager de réduire sa capacité. Mais ce n'est pas la vocation de cette formation.

Vous notez la présence du symbole d'une clé en regard du champ Article_code. Elle représente la clé primaire. Cette clé primaire identifie chaque enregistrement comme unique. Les doublons de références ne sont pas permis. A chaque achat, ce sont ces références uniques, et seulement elles, qui doivent être rappelées dans la table archivant les commandes passées. Elles permettront d'établir le lien avec la table articles pour remonter au niveau de détail sur la désignation et le prix par exemple.

• Dans le volet de gauche, cliquer sur le lien de la table clients pour afficher son contenu,

A ce stade, seules deux personnes sont référencées, sur un niveau de détail volontairement allégé.

• En haut de l'interface de PhpMyAdmin, cliquer sur l'onglet Structure,

En mode conception, nous remarquons que les clients sont également tous référencés de façon unique. La clé primaire posée sur le champ Client_num interdit les doublons. Dans la colonne Extra, vous remarquez la présence de l'attribut AUTO_INCREMENT. A chaque création de client, le numéro unique est auto-incrémenté. Nous n'aurons donc pas à le renseigner. C'est ce numéro, et seulement lui, qui doit être rappelé dans les tables liées pour connaître par exemple, toutes les commandes rattachées à un même client. Toutes les autres colonnes de cette table sont naturellement typées comme des champs de texte, avec des tailles différentes. Par exemple, le champ Client_civilite est limité à 10 caractères (varchar(10)).

• Dans le volet de gauche, cliquer sur le lien de la table commandes,

Le contenu dévoile que deux facturations ont été réalisées. Et ces commandes ont été passées par les deux personnes de la table clients, référencées par leur numéro respectif, rappelé dans la colonne Com_client. Les champs Client_num et Com_client doivent donc être reliés pour assurer la sécurité et l'intégrité des données. Nous commençons à y voir plus clair. C'est grâce à cette liaison que la table enfant commandes offre une structure et un contenu simples. Les informations complètes du client ayant passé commande n'y sont pas rappelées.

Remarque importante : Autant un client est référencé de façon unique dans la table clients par un numéro explicitement différent des autres, autant ce numéro peut être répété indéfiniment dans la table commandes qui lui est liée. Fort heureusement, un même client a le droit d'acheter plusieurs fois dans le même magasin.

• En haut de la fenêtre de PhpMyAdmin, cliquer sur l'onglet Structure,

Nous retrouvons la clé primaire sur le champ Com_num. Elle aura donc pour effet d'identifier chaque commande comme unique. Et cette identification sera sécurisée et automatisée comme l'indique l'attribut AUTO-INCREMENT dans la colonne Extra. Le champ Com_client est fort naturellement déclaré comme un entier. Son type doit être compatible avec celui du champ de la clé primaire, issu de la table parent clients. Ces deux entités doivent être conciliables pour assurer la liaison. Cette liaison permettra de repérer toutes les commandes d'un même client.

• Dans le volet de gauche, cliquer enfin sur le lien de la table detail pour afficher son contenu,

Nous obtenons ainsi le détail en cascade des commandes passées. Chaque commande est repérée par son numéro dans la colonne Detail_com. Et pour chacune, plusieurs achats ont été réalisés. C'est pourquoi les numéros sont rappelés. Ils offrent les informations sur les références et les quantités achetées pour la commande unique, référencée dans la table parent commandes. Ce champ Detail_com doit donc faire office de liaison avec celui de la clé primaire Com_num de la table commandes.

Une commande est donc bien unique dans la table parent. Mais son numéro peut être répété indéfiniment dans la table liée detail. Fort heureusement, pour une même commande, chaque client a le droit d'acheter plusieurs produits.

• En haut de l'interface de PhpMyAdmin, cliquer sur l'onglet Structure,

La vue de la table en conception nous confirme qu'elle doit être liée non seulement à la table commandes mais aussi à la table articles. Ce détail doit en effet rappeler premièrement le numéro de commande. C'est pourquoi le champ detail_com est présent. Son type est un entier, donc il est conforme au type de la clé primaire dans la table parente. Mais ce détail doit aussi rappeler les références des articles achetés. C'est pourquoi le champ detail_ref est présent. Il est typé comme un texte. Il s'agit bien du type de la clé primaire pour identifier les références dans la table parente articles.

Vous l'avez compris, avant de penser à relier des tables entre elles, plusieurs étapes sont nécessaires :

• Fractionner l'information sur plusieurs tables,
• Référencer les enregistrements par des clés primaires,
• Prévoir les champs du même type dans les tables dépendantes pour les liaisons à établir.

Ici, tout ce travail est déjà réalisé. Nous n'avons plus qu'à l'exploiter.

Relier des tables avec mises à jour en cascade
L'autre intérêt de ces relations est de pouvoir provoquer des réactions en chaîne. Si nous supprimons un client de la table clients, toutes ses commandes doivent être effacées automatiquement dans la table commandes. De fait, tous les détails de toutes ses commandes doivent elles-mêmes être éliminées de la table detail. On parle alors de suppression en cascade (DELETE ON CASCADE). Ce processus est sécuritaire. Il permet de ne pas conserver des enregistrements orphelins. De fait, le code serveur peut s'autoriser certaines largesses. La requête n'est entreprise que sur la table parente. Les actions sont répercutées automatiquement sur les tables liées.

Mais ne vous y trompez pas, l'intérêt majeur consiste à faire régner la règle de l'intégrité référentielle. La chronologie doit être respectée. Imaginons que nous souhaitions renseigner la commande du client numéro 3. Cette règle nous en empêchera formellement. Pour l'instant, seuls les clients numéros 1 et 2 sont référencés. Le client numéro 3 doit d'abord être créé dans la table clients pour que sa commande puisse être honorée dans la table commandes. Dès lors, son contenu pourra être renseigné dans la table detail et non l'inverse. Et seules les références issues de la table articles pourront y être ajoutées.

• Dans le volet de gauche, sélectionner la première table dépendante, soit commandes,
• En haut de l'interface de PhpMyAdmin, cliquer sur l'onglet Structure,
• Puis, juste au-dessus de la table, cliquer sur le bouton Vue relationnelle,

Nous accédons à l'interface de gestion des relations pour la table commandes.

• Dans la première liste déroulante Colonne, désigner le champ Com_client,

Ce champ rappelle le numéro de la clé primaire issue de la table clients. Dans les relations, on parle de clé étrangère, ou encore de clé externe.

• Dans la liste déroulante Table, choisir la table clients,

Comme vous le constatez, le champ Client_num de la clé primaire, s'inscrit automatiquement dans la seconde liste déroulante colonne. La relation s'opère naturellement du fait de l'unicité définie sur ce champ et de la correspondance entre les types de données.



La règle de l'intégrité référentielle est établie entre ces deux tables. Mais nous n'en avons pas terminé. Il faut déclencher les réactions en chaîne.

• Dans la liste déroulante ON DELETE, choisir CASCADE,
• Dans la liste déroulante ON UPDATE, choisir CASCADE,

Nous définissons ainsi le comportement des tables par le jeu des relations. Si une suppression de client est opérée dans la table clients, toutes ses traces doivent être effacées dans la table commandes. La mise à jour (ON UPDATE) est facultative dans notre cas. Aucun élément lié dans la table enfant n'est répété. Donc aucune actualisation n'est susceptible d'intervenir. Nous actionnons néanmoins le mécanisme dans ce processus de découverte.

• En bas de l'interface, cliquer sur le bouton Enregistrer pour valider cette première liaison,

Il est fondamental d'assimiler que ces relations peuvent être construites si et seulement si le moteur de base de données est défini sur InnoDb. C'est lui qui permet de gérer et manipuler ces liaisons, ce que ne peut réaliser le moteur de base de données MyIsam.

• Dans le volet de gauche, cliquer sur le lien de la base de données stocks,

Nous affichons ainsi la vue schématique résumant toutes les tables la composant. Et comme l'indique la colonne Type, pour chacune d'entre elles, le moteur de base de données a soigneusement été défini sur InnoDb. Il s'agit d'un réglage important à ne pas omettre en création de table en vue de bâtir des relations.

Poursuivons ! La table detail dépend des commandes passées et des articles référencées. Deux relations doivent donc être créées.

• Dans le volet de gauche, cliquer sur le lien de la table detail,
• En haut de l'interface de PhpMyAdmin, cliquer sur l'onglet Structure,
• Au-dessus de la table, cliquer sur le bouton Vue relationnelle,
• Dans la première liste colonne, choisir le champ Detail_com,
• Dans la liste Table, choisir la table commandes,

Là encore, le lien se construit naturellement. La clé primaire Com_num de la table parente est instantanément identifiée et imposée.

• Dans la liste ON DELETE, choisir CASCADE,
• Dans la liste ON UPDATE, choisir CASCADE,
• Sous ces listes, cliquer sur le lien Ajouter une contrainte,
• Dans la première liste colonne, choisir le champ Detail_ref,
• Dans la liste table, définir la table articles,

De fait et comme précédemment, le lien s'opère naturellement avec la clé primaire Article_code du même type, dans la table parente.

• Dans la liste ON DELETE, choisir CASCADE,
• Dans la liste ON UPDATE, choisir CASCADE,
• Enfin, cliquer sur le bouton Enregistrer pour valider ces relations,

Toutes les liaisons sont désormais établies.

Fonctionnement des relations
Il est temps de constater l'impact des relations que nous avons établies entre les tables de la base de données MySql.

• Dans le volet de gauche, cliquer sur la base stocks pour la désigner intégralement,
• En haut de l'interface de PhpMyAdmin, cliquer sur l'onglet Plus,
• Dans la liste, choisir Concepteur,

Nous affichons la représentation schématique des tables avec leurs champs, les clés primaires, les clés étrangères et les relations.

Ces liaisons sont explicitement mises en valeur, entre la clé primaire de la table parente et la clé externe de la table enfant. Cette vue simplifie aussi la compréhension de la hiérarchie établie. La table commandes dépend de la table clients. La table detail dépend des tables commandes et articles.

Ces relations peuvent aussi être créées directement depuis cette vue. Il suffit de cliquer sur le bouton Nouvelle relation, dans la barre d'outils verticale, sur la gauche du concepteur. Il faut ensuite cliquer sur la clé primaire d'une table et sur la clé étrangère de la table dépendante. Une boîte de dialogue se déclenche alors pour proposer les mises à jour en cascade.

• Dans le volet de gauche, cliquer sur la table commandes pour afficher son contenu,
• En haut de l'interface de PhpMyAdmin, cliquer sur l'onglet Insérer,

Nous basculons dans une vue offrant d'ajouter manuellement de nouveaux enregistrements dans la table commandes.

• Déployer la liste déroulante du champ Com_client,

Nous confirmons donc la sécurité imposée par la règle de l'intégrité référentielle. Nous n'avons pas conçu de liste déroulante. Mais par le jeu des relations, PhpMyAdmin n'autorise pas d'affecter des clients n'existant pas dans la table clients. Seuls les clients 1 et 2, actuellement référencés peuvent passer commande.

• Dans le volet de gauche, cliquer sur la table clients pour afficher son contenu,
• En haut de l'interface de PhpMyAdmin, cliquer sur l'onglet Insérer,
• Dans le champ Client_civilite, taper le texte : Monsieur,
• Dans le champ Client_nom, saisir le texte : Doeuf,
• Dans le champ Client_prenom, taper le texte : John,

Fort naturellement, nous ne renseignons pas le champ Client_num. Il s'agit de la clé primaire auto-incrémentée.

• Cliquer alors sur le bouton Exécuter pour procéder à l'insertion,

Un message apparaît. Il confirme qu'une nouvelle ligne a été ajoutée dans la table clients. La syntaxe de la requête SQL enclenchée par PhpMyAdmin est donnée à titre d'information :

INSERT INTO 'clients' ('Client_num', 'Client_civilite', 'Client_nom', 'Client_prenom') VALUES (NULL, 'Monsieur', 'Doeuf', 'John');

• En haut de l'interface de PhpMyAdmin, cliquer sur l'onglet Parcourir,

Nous affichons ainsi le contenu de la table clients. Nous constatons la présence du nouvel enregistrement que nous venons de créer. Son numéro Client_num lui a bien été attribué automatiquement.

• Dans le volet de gauche, cliquer sur le lien de la table commandes,
• En haut de l'interface de PhpMyAdmin, cliquer sur l'onglet Insérer,
• Déployer la liste déroulante Com_client,

Cette fois, il est possible de désigner un nouveau client. En effet, dans la chronologie et dans la hiérarchie, nous l'avons créé d'abord.

• Choisir le client Numéro3,
• Dans le champ Com_date, saisir par exemple : 12/03/2019,
• Puis, dans le champ Com_montant, taper la valeur : 246.1,

Attention, le système de décimales anglo-saxon est matérialisé par un point (.) et non par une virgule comme en France.

• Cliquer sur le bouton Exécuter pour créer la nouvelle commande,
• Puis, cliquer sur l'onglet Parcourir pour visualiser le contenu de la table,

La nouvelle commande a parfaitement été affectée au nouveau client par le jeu des relations.

• Dans le volet de gauche, cliquer sur la table detail pour afficher son contenu,
• En haut de l'interface de PhpMyAdmin, cliquer sur l'onglet Insérer,
• Déployer la liste déroulante du champ Detail_com,
• Puis, choisir la commande numéro 3,

Par un concours de circonstances, le numéro de la commande coïncide avec le numéro du client. Mais cela vient seulement du fait que les deux précédents clients n'ont pas passé d'autres commandes dans l'intervalle de temps.

• Dans la liste déroulante Detail_ref, choisir la référence b009,

Là encore, par le jeu des relations, la règle de l'intégrité référentielle se déclenche. Il n'est pas possible d'inscrire une référence dont l'article n'a pas encore été créé dans la table parente articles.

• Dans le champ Detail_qte, saisir : 2,

PhpMyAdmin propose d'ajouter un deuxième enregistrement à la suite, dans la partie inférieure de la vue.

• Dans le champ Detail_com, choisr la commande numéro 3,
• Dans le champ Detail_ref, choisir la référence b010,
• Dans le champ Detail_qte, taper 1,

La commande du nouveau client est donc constituée de deux lignes.

• Cliquer sur le bouton Exécuter pour procéder à l'insertion des enregistrements,
• Puis, dans le volet de gauche, cliquer sur la table detail pour afficher son contenu,

Nous y retrouvons effectivement le détail de la nouvelle commande pour le nouveau client.

Voyons désormais comment se comportent les tables dans la hiérarchisation. Partons du principe que le client numéro 2 n'existe plus. Nous devons donc le supprimer.

• Dans le volet de gauche, cliquer sur la table clients pour afficher son contenu,
• Cocher la case en regard du client numéro 2,
• Puis, cliquer sur le lien Supprimer situé sur la ligne de l'enregistrement,

Un message de confirmation apparaît. Il ne s'agit pas d'une action anodine en effet. De plus, elle est irréversible.

• Cliquer sur le bouton Ok pour confirmer la suppression,

Nous sommes de retour sur l'affichage de la table clients. Et l'enregistrement que nous avons désigné a effectivement disparu. Ne soyez pas tenté de cliquer sur le lien général Supprimer en dessous de la table. Vous ne supprimeriez que l'enregistrement sans ses dépendances dans les tables liées.

• Dans le volet de gauche, cliquer sur la table commandes pour afficher son contenu,

Comme vous le remarquez, la suppression en cascade a opéré. La commande du client supprimé a elle aussi été éliminée.

• Dans le volet de gauche, cliquer maintenant sur la table detail,

Le constat est le même. Tous les détails pour la commande du client supprimé ont disparu. Voilà donc l'un des intérêts fondamentaux de ces relations entre tables de base de données, avec mises à jour en cascade.

Créer le compte utilisateur
Pour préparer les travaux à suivre sur l'approvisionnement des stocks et la facturation, nous avons besoin de créer un compte. Ce compte doit disposer des droits. Ainsi, le code serveur Php pourra y faire référence afin de se connecter à la base de données des stocks.

• Dans le volet de gauche, cliquer sur la base de données stocks pour la désigner intégralement,
• En haut de l'interface de PhpMyAdmin, cliquer sur l'onglet Privilèges,
• Au centre de l'écran, cliquer sur le lien Ajouter un compte d'utilisateur,
• Dans la zone Nom d'utilisateur, saisir gStock,
• Pour le nom d'hôte, renseigner l'adresse locale, ici : 127.0.0.1,
• Pour le mot de passe, taper : gS123k12,
• Puis, le confirmer dans la zone Saisir à nouveau,
• Dans la section Privilèges globaux, cocher la case Tout cocher,
• Enfin, tout en bas de l'interface, cliquer sur le bouton Exécuter,

Le compte est désormais créé. Vous pouvez le visualiser en cliquant sur l'onglet Comptes d'utilisateurs. Maintenant que notre base de données relationnelle est parfaitement structurée, nous pourrons l'exploiter dans les formations à venir, notamment pour proposer une interface Web de facturation avec gestion des stocks.