Implémentez vos bases de données relationnelles avec SQL

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 :

1. Nous allons ajouter une table lieu

2. Nous allons remplir notre table lieu

3. Nous allons créer une table de liaison

4. Nous allons remplir notre table de liaison

5. 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 :

1. 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 deux FOREIGN KEY sont par ailleurs des PRIMARY KEY dans leurs tables respectives.

2. 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 !

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.

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 des PRIMARY KEY dans leurs tables respectives.

• C'est deux FOREIGN KEY sont la PRIMARY KEY de notre table d'association.

• Il faut être attentif au paramètre ON UPDATE qui doit être cohérent avec les paramètres ON DELETE des tables concernées.

 Vous voilà arrivé à la fin de ce cours. Retrouvez-moi au chapitre suivant pour la conclusion !