Modéliser les données avec MySQL Workbench

MySQL Workbench utilise des noms par défaut pour les objets suivants : colonnes des tables, type des colonnes, tables associatives (cf. paragraphe 4.3), clés étrangères. Pour modifier ces noms : menu « Edit » puis « Preferences » :

On ouvre ensuite l'onglet « Model » de la fenêtre  Workbench Preferences  (les valeurs par défaut ci-dessous ne sont pas forcément celles qui étaient en vigueur lors de l'installation du produit) :

Le nom par défaut de la 1re colonne d'une table est constitué du nom de la table en minuscules, préfixé par « id ». Le type de cette colonne est INT. Par défaut, cette colonne est le seul élément de la clé primaire de la table. Tout ceci ne vaut que si la table n'est pas identifiée relativement une autre table (auquel cas MySQL Workbench recopie le nom de la (des) colonne(s) composant la clé primaire de cette dernière) :

Une infobulle permet de voir que MySQL Workbench propose de mettre le nom par défaut de la table en lettres majuscules, minuscules, capitales.

Pour les colonnes qui ne participent pas à la clé primaire, on voit ci-dessous que le nom par défaut est celui de la table, suffixé par « col ». Afin d'éviter les doublons, l'outil remplace en fait « col » par « col1 », « col2 », etc. Comme autre nom possible par défaut, MySQL Workbench propose le minimum syndical, à savoir seulement le nom de la table, ce qui du reste n'est pas plus mal :

Outre le nom par défaut des colonnes, on peut en définir le type par défaut, par exemple CHAR(8) au lieu de VARCHAR(45) :

Par défaut, le nom d'une étrangère est la concaténation de la constante « fk_ », du nom de la table référençante, de la constante « _ » et du nom de la table référencée, avec des possibilités d'autres noms par défaut (substitutions) :

Les colonnes d'une clé étrangère ont aussi un nom par défaut : nom de la table référençante concaténé avec le nom de la colonne correspondante de la clé primaire de la table référencée :

Si on ne souhaite pas que le nom de la table référençante fasse partie du nom par défaut des colonnes composant la clé étrangère, il suffit de remplacer « %table%_%column% » par « %column% ».

La fenêtre « Model » utilisée jusqu'ici pour définir les valeurs par défaut des noms des colonnes et leur type permet aussi de définir les actions de compensation par défaut. Une action de compensation permet de décider du comportement du SGBD au cas où :

On souhaite remplacer par une valeur k2 la valeur k1 (k1 ≠ k2) de la clé (primaire en général) d'une table T1 qui fait l'objet de contraintes référentielles de la part d'autres tables T2, ..., Tn (voire T1 elle-même en cas d'auto-référence), T1 jouant le rôle de table référencée ;

On souhaite supprimer au moins une ligne de T1 : les tables énumérées ci-dessus touchées seront en droit de donner leur accord pour en subir les conséquences, ou bien refuser énergiquement et donc faire échouer l'opération.

On retrouve ici les options possibles, RESTRICT (et/ou NO ACTION selon les SGBD) pour refuser énergiquement les opérations de mise à jour portant sur la table référencée, et CASCADE pour les accepter et les propager dans les tables référençantes. Il va de soi que SET NULL est une incongruité laissant le champ libre au bonhomme Null (Horresco referens !), lequel est hors-la loi au pays des relations.


A cette occasion, faisons un retour rapide sur l'intégrité référentielle et le métabolisme des données.

Prenons l'exemple des commandes passées par les clients. On a vu à l'occasion de la déclaration des tables CLIENT et COMMANDE que dans un 1er temps ces tables n'entretenaient aucune relation (cf. paragraphe 5.2.1) :

Une fois qu'on a connecté les deux tables, MySQL Workbench a recopié la colonne ClientId de l'en-tête de la table Client dans l'en-tête de la table COMMANDE :

Un lien a été établi entre les deux tables, ce qui signifie fondamentalement que chaque valeur prise par la colonne ClientId de l'en-tête de la table COMMANDE doit être une valeur prise en premier lieu par la colonne ClientId de l'en-tête de la table CLIENT : comme {ClientId} est clé de la table CLIENT (clé primaire dans l'exemple ou clé candidate dans le cas général), on conclut qu'une commande doit nécessairement faire référence à un client existant, ce qui du reste est la moindre des choses si l'on tient à ce que la base de données soit valide et que les comptables de chez Dubicobit ne viennent pas exiger, à juste titre, que les choses rentrent dans l'ordre...

Par définition la colonne ClientId appartient à une clé étrangère {ClientId} de la table COMMANDE dont les valeurs doivent d'abord être des valeurs de la clé {ClientId} de la table CLIENT. Plus précisément on est en présence d'une contrainte d'intégrité référentielle. Si la colonne ClientId de l'en-tête de la table COMMANDE prenait une valeur qui ne fût pas une valeur prise par la colonne ClientId de l'en-tête de la table CLIENT, alors la contrainte d'intégrité référentielle serait violée : à charge du SGBD de veiller au respect de la contrainte, ce qu'on lui signifie ainsi en SQL :

CREATE TABLE COMMANDE
... ClientId    INT    NOT NULL ...
    CONSTRAINT COMMANDE_CLIENT_FK FOREIGN KEY (ClientId)
        REFERENCES CLIENT (ClientId) ...

Où COMMANDE_CLIENT_FK est le nom de la contrainte à faire respecter.


Du métabolisme des données

La contrainte COMMANDE_CLIENT_FK ci-dessus est manifestement statique, elle dit seulement qu'une commande doit faire référence à exactement un client, ni plus, ni moins. Comme disait Ted Codd, au-delà de l'aspect statique, anatomique de la base de données, il faut aussi se préoccuper du métabolisme des données pour mieux comprendre la nature de celles-ci, de leurs relations, et à un niveau plus terre à terre, ne pas avoir la sensation d'avoir les pieds pris dans le béton lors des opérations de mise à jour, et cela grâce à des actions de compensation illustrées par les clauses ON UPDATE et ON DELETE :

Si, comme dans la figure ci-dessus, c'est l'option RESTRICT qui a été retenue pour la table COMMANDE :

CREATE TABLE COMMANDE
... ClientId    INT    NOT NULL ...
    CONSTRAINT COMMANDE_CLIENT_FK FOREIGN KEY (ClientId)
        REFERENCES CLIENT (ClientId) ON DELETE RESTRICT ...

Alors, si on exécute une instruction DELETE :

DELETE FROM CLIENT
    WHERE ClientId = 123 ;

Comme on l'a évoqué dans le paragraphe 1.2, de deux choses l'une, ou bien il y a au moins une commande pour le client pour lequel on vérifie la condition « ClientId = 123 », auquel cas la demande de suppression du client sera rejetée, ou bien il n'y a aucune commande pour ce client, auquel cas la demande de suppression sera satisfaite (à condition, bien sûr, qu'il n'y ait pas d'autres tables référençant la table CLIENT et pouvant provoquer, comme dans le cas de la table COMMANDE, un rejet de la demande de suppression).

En passant : avec certains SGBD (par exemple MS SQL Server) on n'utilise pas RESTRICT mais NO ACTION. Avec d'autres (par exemple DB2 for z/OS ou MySQL), on peut utiliser RESTRICT et NO ACTION.

Rappelons que l'option CASCADE n'a guère de sens que lorsque les tables atteintes par les stimuli engendrés par un DELETE correspondent à des entités-types spécialisées (cf. paragraphe 6.1) ou à des entités-types faibles (cf. paragraphe 1.3), cas en l'occurrence des lignes de commande, lesquelles sont des propriétés multivaluées des commandes : une ligne de commande n'a pas plus de raison que la date de commande de s'opposer à la suppression légitime d'une commande...

Partant de là, on retient l'option CASCADE pour la table LIGNE_COMMANDE :

CREATE TABLE LIGNE_COMMANDE
...
    CONSTRAINT COMMANDE_COMMANDE_FK FOREIGN KEY (ClientId, CommandeId)
        REFERENCES COMMANDE ON DELETE CASCADE ...

En conséquence de quoi, si aucune autre contrainte ne s'y oppose, l'exécution de l'instruction suivante provoque, dans la table LIGNE_COMMANDE, la suppression des lignes de la commande 2 du client 123 :

DELETE FROM COMMANDE
    WHERE ClientId = 123 AND CommandeId = 2 ;

Rappelons (cf. paragraphe 4) qu'une table associative résulte d'une association de plusieurs à plusieurs entre deux tables (ou plusieurs, cf. paragraphe 5.3), par exemple DEPOT et ARTICLE : un type d'article donné peut être stocké dans plusieurs dépôts et dans un dépôt donné peuvent être stockés plusieurs types d'articles.

Situation avant connexion des tables :

Du fait du paramétrage ci-dessous, par défaut, la table associative connectant DEPOT (considérée ci-dessous comme source) et ARTICLE (considérée comme cible) sera nommée DEPOT_has_ARTICLE :

Mais si, par exemple, on remplace « %stable%_has_%dtable » par « %stable%_%dtable », le nom par défaut de la table associative sera DEPOT_ARTICLE.

Un défi ergonomique est de faire tenir les objets d'un diagramme sur une seule page au format A4 ou A3. A force d'être chargé, un diagramme devient illisible, avec des ficelles qui se croisent dans tous les sens, ça tient de l'art new-yorkais, et le concepteur tente en vain d'y mettre de l'ordre. Comme disait un des champions de Merise, Arnold Rochfeld, quand on voit un diagramme comportant plus de sept objets, on peut préparer l'aspirine...

Qui plus est, si le grand projet de la société Volfoni Frères (concurrente de Dubicobit) donne lieu à un diagramme comportant quelque chose comme mille types d'entités (ou schémas de tables dans le cas de MySQL Workbench), l'administration des données prendra la précaution d'urbaniser tout cet ensemble en grands domaines (référentiels), sous-domaines et sous-sous-domaines cohérents. Ainsi, ce projet aura été décomposé en référentiels : Personnes, Contrats, Cotisations, Catalogue produits, Prospection de masse, Éditique, Habilitations, etc., peut-être une vingtaine, voire plus. Au niveau le plus fin, la représentation graphique doit tenir — de façon claire et lisible — sur l'équivalent d'un ensemble de pages au format (disons) A3, chacune d'elles constituant une vue suffisante pour qu'un développeur puisse s'y retrouver.

Dans le même sens, supposons que l'on effectue la rétro-conception (cf. paragraphe 9) d'un modèle de taille raisonnable et que le résultat (pour le moins glauque !) de l'opération soit le suivant :

De cette bouillie indigeste de 180 schémas de tables, faire quelque chose de lisible tenant sur un seul diagramme est évidemment impossible : il n'y a plus qu'à urbaniser, faire figurer dans un même diagramme les tables dont les liens sémantiques sont évidents, sans pour autant supprimer le diagramme bouillie, valide par hypothèse, mais qui n'aura plus guère l'occasion de sortir de la cave.

Du strict point de vue technique, MySQL Workbench permet d'urbaniser sans difficulté particulière. A titre indicatif, on peut procéder ainsi : en plus du diagramme bouillie, créer un diagramme par sous-ensemble (domaine, sous-domaine, etc.) dont on veut une vue intelligible et cohérente, et sur lequel on pourra travailler confortablement.

Exemple : Supposons qu'on veuille créer un diagramme « Commandes » dédié aux commandes, à partir du diagramme « Général », lequel contient l'ensemble des tables, dont celles à faire figurer dans le nouveau diagramme (notamment les tables TIERS er CLIENT visibles ci-dessous) :

Opérations pour construire le diagramme « Commandes » :

—    Créer un diagramme à l'aide de la commande ad-hoc (cf. paragraphe 10.6.3).

—    Lui donner un nom (cf. paragraphe 10.6.4).

—    Le peupler avec les tables voulues (cf. paragraphe 10.6.5).

—    En retirer (en les masquant) les objets (tables, liens) qu'on aura fait figurer inutilement (cf. paragraphe 10.6.6).

Pour créer le diagramme « Commandes » (vide), utiliser CTRL-T ou la commande « Model > Add diagram » ou encore cliquer sur l'icône de la barre de menus. Par défaut, le nouveau diagramme porte le nom de « EER Diagram » :

Le diagramme à renommer porte actuellement le nom de « EER Diagram » :

Passer par l'onglet « Model » et choisir « Diagram properties and Size... » :

Apparaît la fenêtre « Diagram properties » : on pourra alors renommer « EER Diagram » comme on l'entend.

Une autre méthode pour renommer un diagramme : cliquer sur l'onglet « MySQL Model », l'outil affiche alors la fenêtre « Model Overview » et un nom de diagramme : « EER Diagram » :

Pour changer le nom du diagramme, on clique délicatement sur le symbole du diagramme, ce qui suffit normalement pour sélectionner le nom et le changer, ou bien on utilise la touche F2 :

On peut alors renommer « EER Diagram », par exemple en « Général » :

En double-cliquant sur « Général », on ouvre le diagramme correspondant.

Les noms des tables à faire figurer dans le diagramme « Commandes » apparaissent dans le catalogue (Catalog Tree) :

Le diagramme étant vide de tout objet, pour y faire figurer par exemple les tables COMMANDE et LIGNE_COMMANDE, on procède par glisser-déposer de leur nom à partir du catalogue, un par un ou ensemble :

Au résultat (en notant bien que MySQL Workbench établit automatiquement les liens entre les tables) :

Il n'y a plus qu'à compléter le diagramme avec les autres tables parties prenantes dans les commandes :

L'objet de l'opération est de ne plus afficher un objet (table ou lien) dans un diagramme, sans pour autant le supprimer (ne serait-ce que parce que d'autres diagrammes s'en servent !) Par exemple pour masquer la table COMMANDE_STATUT :

Sélectionner la table, puis faire un clic droit pour avoir accès à la commande « Delete » (ou passer par la commande « Menu > Edit > Delete ») :

Il y a ouverture de la fenêtre « Delete Object ». Choisir » Keep » pour effectivement masquer la table sans la supprimer :

Au résultat, la table COMMANDE_STATUT n'est plus affichée (mais elle n'est pour autant supprimée, ouf !) :

Pour réafficher la table : effectuer un glisser-déposer de son nom à partir du catalogue des objets (Catalog tree) :

Histoire d'en rajouter une couche pour faire joli... Qu'il s'agisse du diagramme général (cf. paragraphe 10.6.2) ou d'un sous-diagramme ne contenant qu'un sous-ensemble de tables, on peut faire du coloriage et donner une sensation d'urbanisation. Partons d'un sous-diagramme où ne sont affichées que les commandes et leur livraison par les camions :

On commence par cliquer sur l'icône ad-hoc :

On entoure les objets à incorporer au layer (qui a été nommé « New Layer » suite au clic sur l'icône) :

Puis en double-cliquant, on renomme le layer comme ci-dessous (et au besoin on change le coloriage) :

Au résultat :

Constitution d'un layer supplémentaire :

N.B. Quand on clique sur l'onglet associé à un layer, on peut le bouger, tout ce qu'il contient suit.

L'écran d'accueil de la version 5.2 de MySQL Workbench est moins sinistre que celui de la version 6.0...

Il s'agit de créer un nouveau modèle. On sélectionne l'option « Create New EER Model » ¹.

En réponse, MySQL Workbench propose la fenêtre suivante, permettant de créer un MLD (modèle logique de données) sous la forme d'un diagramme :

Cette fenêtre est identique à celle qui est proposée par MySQL Workbench version 6 : à partir de là, on procède de la même façon (cf. paragraphe 2.2.2).

________________________________________________________________

¹ Pour la petite histoire, EER est l'abréviation de « Enhanced Entity-Relationship », c'est-à-dire à peu de choses près que MySQL Workbench se veut une extension du modèle Entité-Relation (E/R). En réalité, l'outil nous permet plutôt de créer des modèles logiques de données (MLD) au sens Merise du terme, car il nous propose par exemple le concept de clé étrangère, qui n'a pas lieu d'être au niveau E/R où il ferait double emploi (violant donc le principe d'essentialité) avec le concept de relation (relationship, association) tel que défini en 1976 par Peter Chen, père du modèle (The Entity-Relationship Model-Toward a Unified View of Data, dans ACM Transactions on Database Systems, Vol. 1, No. 1. March 1976, Pages 9-36.) En revanche, au niveau logique, le concept de clé étrangère est essentiel. Notons encore que, pour faire partie de la famille des modèles EER « enhanced », MySQL Workbench devrait en plus offrir des fonctionnalités telles que la généralisation/spécialisation (cf. paragraphe 6).