Ajoutez une relation plusieurs à plusieurs
Pour ce chapitre, on travaille sur la table foodly.
Vous avez créé votre nouvelle table "famille" ? Souvenez-vous, on aimerait ensuite ajouter le type de magasin où sont présents les aliments. Un même aliment pouvant être présent dans plusieurs lieux, nous allons créer une relation plusieurs à plusieurs. Voyons comment faire !
Analysez le besoin fonctionnel
La dernière demande de mise à jour des utilisateurs est arrivée !
Ces derniers réclament une chose en théorie simple : pouvoir visualiser dans l’application de Foodly dans quel magasin sont disponibles leurs aliments préférés. Par exemple, savoir si leur jambon de prédilection est disponible aussi bien à Carrefour que chez Leclerc.
Les développeurs se sont de nouveau regroupés : il va falloir stocker les lieux de vente dans la BDD.
Un lieu pouvant vendre plusieurs aliments, et ces mêmes aliments pouvant être vendus dans plusieurs lieux, vous allez devoir créer une relation plusieurs à plusieurs.
Quel va être notre plan d'attaque ? Et bien c'est très simple :
Nous allons ajouter une table lieu
Nous allons remplir notre table lieu
Nous allons créer une table de liaison
Nous allons remplir notre table de liaison
Nous allons tester des requêtes
Ajoutez une nouvelle table “lieu”
Mais avant toute chose donc, il vous faut créer la nouvelle table des lieux de vente (que nous allons sobrement appeler “lieu”).
Un lieu de vente, c’est :
Un nom (exemple : Carrefour City) ;
Un type (exemple : supermarché).
Je vous laisse créer la table vous-même ? Après tout, vous avez toutes les cartes en main pour le faire !
Allez-y, je vous attends. 😉
Bon, allez, au cas où, voici la correction :
CREATE TABLE lieu (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
nom VARCHAR(100) NOT NULL,
type VARCHAR(100) NOT NULL
);
OK, une fois la table créée, insérons un lieu dans cette dernière, partons sur un simple Carrefour City (oui, ça se sent que je suis citadin ? 😆).
Pareil ici, vous devriez pouvoir écrire cette commande vous-même, j’en suis sûr. 🙌
Au cas où, voici la correction :
INSERT INTO `lieu` (`nom`, `type`) VALUES ('Carrefour City', 'supermarché');
Ça y est, la nouvelle table est créée, et un objet y a été inséré. Passons maintenant aux choses sérieuses en créant la relation plusieurs à plusieurs avec les aliments.
Ajoutez la table de liaison
Si vous vous souvenez de la partie 3, vous savez que MySQL (et les bases de données SQL en général) ne sait techniquement pas stocker autre chose que des relations un à plusieurs.
Pour les relations plusieurs à plusieurs, on “triche”, via une table de liaison où on va stocker chaque relation, comme si c’était une double relation un à plusieurs.
En fait, les aliments vont avoir des relations un à plusieurs avec la table de liaison. Les lieux, eux aussi, vont disposer de telles relations avec cette table. Et c’est en reliant ces deux tables via cette table de liaison que vous allez pouvoir ressortir les relations entre lieux et aliments.
Pour ce faire, une table de liaison doit être créée. Par convention, elle doit toujours avoir cette forme :
Son nom doit regrouper les deux tables qu’elle relie, sous la forme : “table1_table2”.
Elle n’a que deux champs à stocker : “table1_id” et “table2_id”. Soit les id de chaque objet qu’elle relie.
Ces id sont donc des références aux id des autres tables.
Sa clé primaire n’est autre que l’association de ces deux id (association qui doit toujours être unique).
Dans notre cas, cela donne ceci en commande SQL :
CREATE TABLE aliment_lieu (
aliment_id INT NOT NULL,
lieu_id INT NOT NULL,
FOREIGN KEY (aliment_id) REFERENCES aliment (id) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (lieu_id) REFERENCES lieu (id) ON DELETE RESTRICT ON UPDATE CASCADE,
PRIMARY KEY (aliment_id, lieu_id)
);
Décortiquons cette commande ensemble :
On crée une nouvelle table avec le nom des deux tables qu’elle relie (ici la table “aliment”, ainsi que la table “lieu”).
On ajoute les références aux id de ces deux tables :
“aliment_id”, qui est une référence aux id de la table “aliment” ;
“lieu_id”, qui est une référence aux id de la table “lieu” ;
on signale à MySQL comment mettre à jour la BDD en cas de suppression ou de mise à jour d’un objet de “aliment_lieu”.
On explique à MySQL que l’id de cette table sera l’association entre les deux id précédents.
Il peut y avoir quelque chose de très perturbant dans cette commande :
La table créée est composée de deux colonnes, qui sont toutes deux
FOREIGN KEY
. Cela est logique puisque ces deux colonnes pointent vers des clés étrangères, l'une pour la table aliment et l'autre pour la table lieu. Ces deuxFOREIGN KEY
sont par ailleurs desPRIMARY KEY
dans leurs tables respectives.L'association de ses deux colonnes forme une
PRIMARY KEY
. Cela est logique également. En effet, il n'est pas possible d'avoir un doublon sur une paire (aliment_id, lieu_id).
Prenez le temps de bien lire, analyser et comprendre la spécificité de notre nouvelle table. C'est un peu déroutant au début mais, vous verrez, on s'habitue très vite !
C’est bien beau tout ça, mais comment ajoute-t-on une relation entre les lieux et les aliments ?
Pas de panique, ça arrive !
Admettons que le “blanc de dinde” présent dans notre BDD soit vendu chez “Carrefour City”. Il vous faudrait alors créer un objet dans “aliment_lieu”, avec l’id de “blanc de dinde” ainsi que l’id de “Carrefour City”.
Retrouvez ces id dans votre BDD et écrivez la commande SQL pour créer cette relation.
Sachant que 11 est l’id du “blanc de dinde”, 1 celui du “Carrefour City”, cela donne ça :
INSERT INTO `aliment_lieu` (`aliment_id`, `lieu_id`) VALUES ('11', '1');
Et voilà, le fait que le blanc de dinde soit vendu à Carrefour est stocké en base de données. Bravo à vous. 👏
Requêtez facilement grâce à la table de liaison
Enfin, pour retrouver cette relation, il vous faudra faire un double JOIN. Rappelez-vous, nous avions vu ça ensemble dans la partie 2.
Cela ressemble à ça :
SELECT *
FROM aliment
JOIN aliment_lieu ON aliment.id = aliment_lieu.aliment_id
JOIN lieu ON lieu.id = aliment_lieu.lieu_id
WHERE aliment.id = 11;
En sortie, MySQL vous affichera bien le “blanc de dinde” avec son lieu de vente associé : “Carrefour City” !
À vous de jouer !
On souhaite savoir quels sont les appareils sur lesquels les utilisateurs ont installé Foodly (par exemple : mac, pc, android, etc).
Un utilisateur peut utiliser Foodly sur plusieurs appareils, et un même appareil peut être commun à plusieurs utilisateurs. Il s’agit d’une relation plusieurs à plusieurs.
Un appareil est uniquement constitué de son type au format texte.
Comment feriez-vous pour créer cette table et la lier aux utilisateurs ?
Je crois en vous pour effectuer cette opération seul. 🙌
Si vous êtes bloqué, la correction est disponible ici :
En résumé
Pour modéliser une relation plusieurs à plusieurs on a besoin de créer une table d'association.
Cette table est assez spécifique. Elle contient deux
FOREIGN KEY
qui sont desPRIMARY KEY
dans leurs tables respectives.C'est deux
FOREIGN KEY
sont laPRIMARY KEY
de notre table d'association.Il faut être attentif au paramètre
ON UPDATE
qui doit être cohérent avec les paramètresON DELETE
des tables concernées.
Vous voilà arrivé à la fin de ce cours. Retrouvez-moi au chapitre suivant pour la conclusion !