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

Pour ce chapitre, on travaille sur la table foodly.

Voyons dans ce chapitre comment ajouter une relation un à plusieurs, en créant notre table "famille". Puis retrouvez dans le chapitre suivant comment ajouter une relation plusieurs à plusieurs avec la création de la table "lieu".

Ajoutez une nouvelle table “famille”

Nous avons obtenu une nouvelle demande d’amélioration de l’application de la part des utilisateurs !

Ces derniers aimeraient pouvoir trier les aliments selon leur “famille”. Par exemple, regrouper ensemble les fruits, les légumes, les viandes, etc.

Les développeurs pensent qu’il serait utile de séparer ces familles des aliments. En effet, ils pensent que les familles d’aliments pourront être utilisées de manière indépendante, pas forcément uniquement sur les fiches des aliments.

Par exemple, pour effectuer un résumé de la consommation en fruits ou légumes d’une personne sur un mois, ou la répartition au sein de ses courses.

Maintenant que vous savez joindre plusieurs tables entre elles, il est temps de construire une telle relation entre tables. Qu’en dites-vous ?

Avant de pouvoir relier les aliments à leur famille, il faut d’abord… eh bien, créer la table famille !

Pour l’instant, un objet de type famille ne devra contenir que le nom de cette famille (“fruit”, “viande”, etc.).

Vous avez déjà vu comment créer une table. Je vous fais confiance pour créer celle-ci. 😉

C’est fait ? Voici la correction :

CREATE TABLE famille 
    (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        nom VARCHAR(100) NOT NULL
    );

Le résultat ?

Query OK, 0 rows affected (0.01 sec)

Mais grâce à  SHOW TABLES; , on obtient : 

+-----------------------+
| Tables_in_foodly      |
+-----------------------+
| aliment               |
| famille               |
| langue                |
| utilisateur           |
| utilisateur_aliment   |
| utilisateurs_gmail_vw |
+-----------------------+
6 rows in set (0.00 sec)

Maintenant que cette table est créée, insérons un objet dans celle-ci. Disons, l’objet qui va représenter les légumes.

Vous vous souvenez de la commande ? La voici :

INSERT INTO famille (`nom`) VALUES ('légumes');

On obtient :

Query OK, 1 row affected (0.03 sec)

Et après SELECT * FROM famille; , on confirme que tout s'est bien passé : 

+----+----------+
| id | nom      |
+----+----------+
|  1 | légumes  |
+----+----------+
1 row in set (0.00 sec)

Et voilà, vous voici en possession d’une nouvelle table “famille” qui dispose d’un objet “légumes”.

Passons à la suite et adaptons notre BDD pour stocker la relation entre les aliments et leur famille.

Ajoutez la relation entre famille et aliment

Une des choses à déterminer lors de l’ajout d’une relation, c’est de savoir si :

• C’est une relation un à plusieurs ;

• Ou une relation plusieurs à plusieurs.

Ici, un aliment peut avoir une seule famille, mais une famille peut être présente sur plusieurs aliments. Par exemple, une poire et une pomme font partie de la famille “fruits”.

Dans le cadre d’une telle relation, c’est l’objet qui se trouve du côté “plusieurs” de la relation qui va être modifié, ici les aliments. On va devoir y stocker l’id de l’objet “un” associé, par exemple “fruits”.

Ici, les aliments vont donc devoir être mis à jour pour y stocker une référence à leur famille. Cette référence, par convention, sera l’id de la famille.

Voici les étapes que l’on va suivre :

1. Ajout du champ famille_id sur les aliments.

2. Modification de ce champ pour signaler à MySQL que c’est une référence à la table famille.

3. Modification d’un objet pour y stocker une relation.

Concernant l’ajout du champ famille_id aux aliments, vous devriez vous en sortir seul. 😉

C’est une clé primaire (un id), vous commencez à bien les connaître. Et pour ajouter un champ à un schéma, vous venez juste de le voir dans le chapitre précédent !

Au cas où, voici la commande :

ALTER TABLE aliment
ADD famille_id INT NULL;

Ici, il est important de laisser la possibilité à  famille_id  d'être  NULL. Nous verrons plus tard pourquoi.

Et cette fois, je vous laisse faire les opérations de contrôles habituels. ;)

Ensuite, ça se complique. Il faut qu’on indique à MySQL que ce champ est une référence à une autre table, en lui précisant quel champ de cette autre table on référence.

Pour modifier le champ famille_id, on utilise la commande :

ALTER TABLE aliment
ADD FOREIGN KEY (famille_id) REFERENCES famille (id)
ON DELETE CASCADE;

Voyons ensemble ce que cette commande signifie :

• La première partie, vous la connaissez. 😉 On signale à MySQL qu’on souhaite modifier la table “aliment”, pour y ajouter une colonne (enfin, plutôt y ajouter une contrainte, dans ce cas précis) ;

• On dit à MySQL que la colonne “famille_id” est une clé étrangère (foreign key en anglais), soit une référence à une colonne d’une autre table (ici, avec la partie FOREIGN KEY (famille_id) ) ;

• On indique ensuite ce à quoi cette clé fait référence. Soit, dans notre cas, la colonne “id” de la table “famille” (soit la commande  REFERENCES famille(id)  ) ;

• Enfin, sûrement la partie la plus obscure pour vous, on indique à MySQL le comportement à adopter en cas de suppression de l’objet “unique” (ici, la “famille”).

Laissez-moi vous expliquer. 🤔

Admettons que je vienne de créer ma famille “fruits”, et qu’elle soit reliée à mes objets “pomme” et “poire”.

Que se passe-t-il si je supprime la famille “fruits” ?

Eh bien, MySQL a besoin de le savoir. Pour cela, on lui indique via la commande ON DELETE.

Cette dernière peut prendre une option parmi celles-ci :

• RESTRICT ou NO ACTION  : MySQL va empêcher la suppression tant que “fruits” est référencé sur au moins un objet “aliment”.

• SET NULL: MySQL va autoriser la suppression de “fruits”, et remplacer “famille_id” sur “pomme” et “poire” par la valeur NULL.

• CASCADE: l’option la plus courante, mais la plus dangereuse. 😨 Ici, MySQL va supprimer “poire” et “pomme” en même temps que “fruits” (il va donc supprimer tous les objets reliés).

Voilà, MySQL sait que “famille_id” est une référence à l’id de la table famille !

Reste maintenant à mettre à jour vos aliments !

Pareil ici, vous avez toutes les compétences en main pour le faire. Sauriez-vous par exemple ajouter la relation de l’objet “haricots verts” vers la famille “légumes” ?

Voici la commande :

UPDATE `aliment` SET `famille_id` = '1' WHERE `nom` = 'haricots verts';

Pour vérifier que cela a bien fonctionné, vous pouvez sélectionner les haricots verts avec leur famille grâce à un JOIN.

SELECT *
FROM aliment
JOIN famille ON aliment.famille_id = famille.id
WHERE aliment.nom = "haricots verts";

Grâce à cette commande, vous verrez apparaître un tableau récapitulatif, où la famille de “haricots verts” est bien “légumes”.

Il ne vous reste plus qu’à ajouter les autres familles vous-même, rajouter sa famille à chaque aliment, et le tour est joué !

Pour ce faire, vous n’avez qu’à réeffectuer les commandes précédentes en les adaptant. C’est un super moyen de revoir tout ce que vous avez pu apprendre lors de ce cours !

À vous de jouer !

Faisons un petit exercice :

On souhaite rajouter les réductions disponibles sur les aliments. Une réduction pouvant être la même pour plusieurs aliments mais chaque aliment pouvant n’avoir qu’une seule réduction, il s’agit d’une relation un à plusieurs.

Les réductions sont uniquement constituées d’un champ “valeur”, qui va contenir la réduction au format texte.

Comment feriez-vous pour créer cette table et la lier aux aliments ?

Je vous laisse faire, vous en êtes capable.  💪 Une fois essayé, voici la correction :

En résumé

• Ajouter une table est relativement facile, mais la lier à d'autres demande un peu plus de travail.

• Tout d'abord, il faut créer notre nouvelle table et ajouter des objets dedans.

• Puis, il faut créer ce qui se qui sera la clé de jointure dans la table initiale.

• Il faut ensuite préciser à MySQL le lien entre la clé étrangère de la table initiale et la nouvelle table.

• Il faut faire attention à l'attribut  ON DELETE  qui précise quoi faire sur la table initiale si on supprime un objet de la table de destination.

• Enfin il faut mettre à jour les valeurs de la table initiale afin de pouvoir faire des requêtes en joignant les deux tables.

Voyons au chapitre suivant comment ajouter un relation plusieurs à plusieurs entre deux objets.