Modéliser les données avec 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 :

Structure des données, diagramme de classes correspondant :

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

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 :

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 «  » 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.

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) :

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

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.

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 » :

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 :

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.

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) :

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 « # ») :

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

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

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).

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.