Modéliser les données avec MySQL Workbench

Image non disponible


précédentsommairesuivant

4. Définir les associations entre les tables (plusieurs à plusieurs)

4.1. En amont de MySQL Workbench


Supposons qu'au niveau conceptuel on ait les règles de gestion des données suivantes :

        RG314 : Un article peut être stocké dans un ou plusieurs dépôts.
        RG315 : Un dépôt est un lieu de stockage pour au moins un article. 
        RG316 : La quantité d'articles par dépôt doit être connue. 
        N.B. Par « article », on sous-entend plutôt « type d'article ». 

Structure des données, version MCD merisien :

Image non disponible
Figure 4.1 - Association N-M (Merise)



Structure des données, diagramme de classes correspondant :

Image non disponible
Figure 4.2 - Association N-M (UML)

4.2. Avec MySQL Workbench


Au niveau MLD, avant qu'on établisse une association entre DEPOT et ARTICLE, la situation est la suivante :

Image non disponible
Figure 4.3 - Association identifiante, avant



Pour établir l'association, on clique sur l'icône « n:m Identifying Relationship », puis successivement sur les objets DEPOT et ARTICLE ; au résultat :

Image non disponible
Figure 4.4 - Association identifiante, après

La notation utilisée pour les cardinalités portées par les liens connectant les tables est dite « patte de corbeau » (Crow's foot). Le symbole « Image non disponible » est synonyme de « N » et le symbole « | » synonyme de « 1 » : un dépôt a au moins un article et un article se trouve dans au moins un dépôt.

4.3. Cardinalité minimale 0


L'outil a créé une table d'association DEPOT_has_ARTICLE (nommage par défaut, mais on peut changer la règle, cf. paragraphe 10.4.7), dotée d'une clé primaire {DepotId, ArticleId}. On peut renommer cette table en STOCKER ou (STOCK, employer un verbe ou un substantif ou autre chose, après tout peu importe, à chacun ses goûts et ses habitudes). En tout cas, on ajustera les cardinalités minimales pour respecter les règles de gestion et rendre conforme le diagramme à la règle selon laquelle un article n'est pas toujours forcément stocké dans un dépôt : pour cela on double-clique sur la patte connectant STOCK et ARTICLE, puis on décoche la case « Mandatory » associée à STOCK (Referencing Table) :

Image non disponible
Figure 4.5 - Cardinalité minimale 0

4.4. Clés étrangères


On observera que l'outil a défini automatiquement les clés étrangères nécessaires :

Image non disponible
Figure 4.6 - Création des clés étrangères par MySQL Workbench



Au sujet des clés étrangères, de l'intégrité référentielle et du métabolisme des données (option ON DELETE), se reporter au paragraphe 10.4.6.

4.5. Afficher le nom des associations


Le nom des associations devrait être affiché automatiquement. Si ça n'est pas le cas, vérifier si la case « Hide Captions » est bien décochée (cf. paragraphe 10.11). Au besoin, on peut pallier avec l'outil « Texte » :

Image non disponible
Figure 4.7 - Affichage du nom des associations

4.6. Notation UML


La notation utilisée jusqu'ici pour les cardinalités est celle d'IE (Information Engineering), dite encore « patte d'oie » ou « patte de corbeau » (Crow's foot). On peut toutefois préférer la notation UML :

Image non disponible
Figure 4.8 - Notation UML



Les Merisiens ne trouveront pas de notation dans le style de Merise, puisqu'on se situe ici au niveau logique (MLD) où les ovales des MCD sont sans objet.

4.7. Notation « à la ACCESS »


Pour les habitués de Microsoft ACCESS, leur notation habituelle est disponible sous l'appellation « Connect to columns », se reporter à The MySQL Workbench Community Blog, Section « About », paragraphe « Notation Styles ». (Voir aussi la représentation d'une hiérarchie, cf. figures 7.9 et 7.10) :

Image non disponible
Figure 4.9 - Notation « à la ACCESS »

4.8. Ordre des colonnes dans les clés


En passant, même si la performance des requêtes n'est pas ici l'objet (cf. paragraphe 10.8), on peut vérifier que l'ordre des colonnes dans les clés est bien celui qui convient à ce sujet (au besoin on change cet ordre en agissant sur la colonne « # ») :

Image non disponible
Figure 4.10 - Ordre des colonnes composant les clés

4.9. Ajout de colonnes


Reste à compléter l'en-tête de la table STOCK, auquel manque la colonne StockQuantite, ce que l'on fait manuellement :

Image non disponible
Figure 4.11 - Ajout de colonne



Le Merisien aura noté que la colonne StockQuantite correspond à une propriété appartenant à une association dite « porteuse de propriétés ».

4.10. Index redondants



Une remarque concernant MySQL et le niveau physique :

Quand on définit la clé primaire d'une table, dans la foulée MySQL Workbench crée automatiquement un index « primaire » (PRIMARY) branché sur cette table et dont la clé est composée des colonnes de la clé primaire. Ainsi, la clé primaire de la table STOCK étant la paire {DepotId, ArticleId}, le couple <DepotId, ArticleId> constitue la clé de l'index primaire de la table STOCK : jusque-là pas de problème. Mais, comme MySQL exige dans un 1er temps que chaque clé étrangère soit dotée de son propre index (toutefois dans un 2e temps « This index might be silently dropped... »), à son tour MySQL Workbench crée automatiquement un index STOCK_DEPOT_FK pour la clé étrangère STOCK_DEPOT_FK représentée par le singleton {DepotId}.

Cas des autres SGBD, par exemple DB2 ou MS SQL :

Le raisonnement tenu par MySQL est trop simple (voir la documentation officielle de la version 5.7, paragraphe 13.1.14.2 «  Using FOREIGN KEY Constraints)  », puisque pour des SGBD comme DB2 ou MS SQL Server, en l'absence de l'index STOCK_DEPOT_FK, l'index primaire pallie. L'index STOCK_DEPOT_FK n'apporte rien, c'est un poids mort, voire nuisible (si l'on réfléchit par exemple à l' effet cluster), et au nom du rasoir d'Ockham il est à supprimer (à faire en dehors de MySQL Workbench s'il s'y oppose !) Quand les index fleurissent, qu'ils soient utiles ou non, c'est toujours au détriment de la performance des opérations de mise à jour qui dans ces conditions peuvent être particulièrement pénalisées (cf. l'expérience méridionale).

Image non disponible
Figure 4.12 - Index redondant, inutile (et pénalisant en mise à jour)


Bonne nouvelle toutefois : à partir de la version 6.0.9, MySQL Workbench autorise la suppression de ce type d'index, merci !



A noter que si le couple <ArticleId, DepotId> constituait la clé de l'index primaire (autre possibilité, choix du DBA), alors ça serait à l'index STOCK_ARTICLE_FK de disparaître.


précédentsommairesuivant

  

Copyright © 2014 - François de Sainte Marie. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.