Modéliser une base de données MySQL

Lorsque la base de données MySQL d’une application est très simple, c’est à dire composée d’une seule table par exemple, il est naturel de passer directement à la création de cette base, à l’aide de l’outil phpMyAdmin. En revanche, dès que la base de données devient plus compliquée, il devient indispensable de passer par une phase de modélisation au cours de laquelle on va réfléchir sur les données (champs) à utiliser et sur leur articulation entre elles.

La conception d’une base de données passe par les 6 étapes suivantes indiquées selon leur ordre chronologique de réalisation. Les 4 premières étapes consituent la phase de modélisation :
1) Rédaction des règles de gestion
2) Création du dictionnaire des données
3) Identification des dépendances fonctionnelles
4) Création du Modèle Conceptuel des Données (MCD)
5) Création du Modèle Logique des Données (MLD)
6) Création du Modèle Physique des Données (MPD)

Les règles de gestion définissent la façon dont les valeurs de chaque donnée doivent être insérées, mises à jour, conservées et supprimées de la base de données
Par exemple :
un membre d’un club de judo peut passer différentes ceintures à différentes dates
un membre d’un club de judo peut selon sa ceinture participer seulement à certains stages
...

Le dictionnaire des données d’une application est un document qui liste l’ensemble des données qui seront utilisées par cette application. Pour chaque données on indique : l’intitulé de la donnée, une explication sur ce qu’elle représente, son type et sa taille.
Par exemple
intitulé, Type, Taille, Explication
nom_membre, texte, 30, nom du membre inscrit au club
prenom_membre, texte, 30, prenom du membre inscrit au club
...

Les dépendances fonctionnelles indiquent les liens qui existent entre les données. Soient par exemple deux données D1 et D2. Si une valeur de D1 est en relation avec une seule valeur de D2, on dit qu’il existe la dépendance fonctionnelle D1→D2 entre les données D1 et D2. Par exemple si une commande ne peut être effectuée que par un seul client on a la dépendance fonctionnelle reference_commande -> client Il est à noter que la réciproque n’est pas vraie car un client peut effectuer plusieurs commandes.

Le Modèle Conceptuel des données (MCD) est un schéma dans lequel ont représente de façon synthétique l’ensemble des données utilisées par une application ainsi que les liens entre elles. Dans ce schéma, on regroupe les données en un certain nombre d’ensembles appelés entités. Chaque entité comprend un identifiant (dont les valeurs prises sont uniques) et un certain nombre de propriétés (champs); Les relations entre ces ensembles de données sont appelées associations. Chaque association s’exprime par un verbe et peut elle-même contenir des propriétés. Les cardinalités affichée de chaque coté de l'association indiquent le nombre d’occurences que l’on rencontrer pour chacune de ces entités. Les cardinalités les plus fréquemment rencontrées sont 0,1 ou 1,1 ou 0,N ou 1,N

mcd-entite-association

1) Cardinalité "1 -> N"

Dans le cas de figure "1 vers N" on a la cardinalité 0,N (ou 1,N) d’un coté et 0,1 (ou 1,1) de l’autre coté. L’exemple suivant indique qu’une cotisation donnée a été payée par un et un seul judoka tandis qu’un judoka peut payer 0 à N cotisations (selon le nombre d’années d’inscription au club).

Le passage du MCD au MLD se fait de la façon suivante :

  • Les deux entités deviennent des tables ;
  • Les identifiants, dans ces des tables, deviennent des clés primaires ;
  • L’association devient une clé étrangère (id_judoka) ajoutée dans la table cotisation.

Le passage du MLD au MPD se fait simplement en précisant les types des champs.

mcd-mld-mpd

Le passage du MPD au code MySQL se fait selon la syntaxte suivante

CREATE TABLE judoka (
id_judoka int NOT NULL,
Nom text,
PRIMARY KEY (id_judoka)
)
ENGINE = InnoDB ;

CREATE TABLE Cotisation (
id_cotisation int NOT NULL,
Date datetime,
id_judoka int,
PRIMARY KEY (id_cotisation),
FOREIGN KEY (id_judoka) REFERENCES judoka(id_judoka)
)
ENGINE = InnoDB ;

On peut utiliser phpMyAdmin pour créer la première table

02a

puis la seconde

02b

Remarque : clés primaires et clés étrangères

Dans une table MySQL, on commence souvent avec les lettres id_ les noms des propriétés qui sont définies comme clés primaires (PRIMARY KEY) ou étrangères (FOREIGN KEY). Une colonne déclarée comme clé primaire doit obligatoirement contenir des valeurs différentes les unes des autres (exemple id_judoka dans la table judoka et id_cotisation dans la table cotisation). Chaque ligne de cette table peut donc être identifiée de façon certaine par sa valeur de clé primaire.

Une colonne déclarée comme clé étrangère (exemple id_judoka dans la table cotisation) doit obligatoirement contenir des valeurs présentes dans la colonne correspondante, déclarée comme clé primaire d'une autre table (exemple id_judoka dans la table judoka) . Cette contrainte permet d'assurer la cohérence des données entre les tables. Pour des raisons de cohérence des données, il n'est pas possible d'insérer dans la table cotisation une ligne qui aurait un id_judoka non présent dans la table judoka. De même, il n’est pas possible de supprimer dans la table judoka une ligne qui contiendrait un id_judoka utilisé dans la table cotisation Il est à noter que les clés étrangères peuvent être créées avec le moteur InnoDB mais pas avec le moteur MyISAM.

Exemple : Soient une table judoka, contenant la liste des judokas d'un club sportif, et une table categorie contenant les catégories d'ages de ces judokas (poussin, minime, benjamin, cadet, senior, veteran par exemple). On a un MCD avec une relation categorie (N) → (1) judoka.
Un judoka correspond à une catégorie mais une catégorie peut correspondre à plusieurs judoka On en déduit le modèle logique suivant :
table categorie: id_categorie (clé primaire), reglement
table judoka : id_judoka (clé primaire), id_categorie (clé étrangère), nom_judoka

2) Cardinalité "1 -> 1"

Dans le cas de figure "1 vers 1" on a la cardinalité 0,1 (ou 1,1) d’un coté et 0,1 (ou 1,1) de l’autre coté. L’exemple suivant indique qu’un judoka possède 0 ou 1 carte de sécurité sociale et qu’une carte de sécurité sociale donnée appartient à un et un seul judoka.

03

Le passage du MCD au MLD se fait de la façon suivante :

  • Les deux entités deviennent des tables ;
  • Les identifiants deviennent des clés primaires ;
  • L’association devient une clé étrangère (id_judoka) ajoutée dans la table carte_secu ou une clé étrangère (id_securite_sociale) ajoutée dans la table judoka.

Le passage du MLD au MPD se fait en précisant les types des champs.

3) Cardinalité "N -> N"

Dans le cas de figure "N ver N", on a la cardinalité 0,N (ou 1,N) d’un coté et 0,N (ou 1,N) de l’autre coté L’exemple suivant indique qu’un judoka donné participe à nombre de stages pouvant varier de 1 à N et qu’un stage donné a une participation d’un nombre de judokas pouvant varier de 1 à N

04

Le passage du MCD au MLD se fait de la façon suivante :

  • Les deux entités deviennent des tables ;
  • Les identifiants id_judoka et id_stage des tables judoka et stage deviennent des clés primaires ;
  • L’association devient une table (participation) dotée de deux clés étrangères (id_judoka et id_stage).

Le passage du MLD au MPD se fait simplement en précisant les types des champs.

Mentions légales © 2015