Création de notre base de données

Maintenant que nous avons conçue notre base de données, que nous savons quelles sont les données que nous allons devoir stocker et comment ces dernières s’articulent entre elles, nous allons pouvoir créer notre base de données.

Pour rappel, voici notre MCD final :

Avant de commencer à créer nos tables, créons une base de données de tests :

CREATE DATABASE `TEST_LEGO`;

Les exemples suivants utiliseront cette même base de données :

USE `TEST_LEGO`;

La table Thème

Nous allons commencer par définir une première entité simple, les thèmes de construction. D'ailleurs, a partir de maintenant, je ne vais plus parler d'entité, mais de table.

En effet, nous ne sommes plus au niveau logique de notre conception mais désormais au niveau physique. Ne vous inquiétez pas, le nom change mais le concept reste sensiblement le même.

CREATE TABLE `LEGO__THEME` (
`id` int(11) NOT NULL auto_increment,
`nom` varchar(255) NOT NULL,
`description` text NULL,
PRIMARY KEY (`id`)
)TYPE=InnoDB;

Comme vous pouvez le voir dans ce premier script SQL il n'y a pour le moment aucun grand changement comparativement aux tables que vous avez déjà pu créer jusqu'à aujourd'hui.

Pourtant, cet exemple utilise le moteur de table InnoDB. Nous allons utiliser ce moteur de table afin que MySQL prenne bien en compte plus loin les clés étrangères et autres contraintes que nous allons définir. Je m'attarderais dans un autre tutoriel sur les différents moteurs de tables de MySQL.

La table Construction

Maintenant nous allons pouvoir entrer dans le vif du sujet. Voici notre première table incluant une clé étrangère. Nous allons donner vie dans cet exemple à notre premier type de relation dont je parlais dans un billet précédant : l'agrégation.

Comment ce traduit l'agrégation entre deux entités au sein d'une base de données ? Et bien c'est très simple, il faudra mettre en place une clé étrangère (Foreign Key en anglais). Cette clé étrangère correspondra en fait à un champ contenant la valeur de la clés primaire de l'entité agrée.

Reprenons notre exemple ; comment décrire au sein de notre base de données qu'une Construction en Lego doit appartenir à un thème ? C'est très simple, il suffit d'enregistrer ceci au sein de notre table Construction.

Pour ce faire, nous allons en plus de nos champs classiques ajouter une colonne qui contiendra pour chaque construction l'identifiant du thème auquel elle appartient. Puis nous signalerons à MySQL que ce champ correspond a un identifiant provenant de la table THEME.

CREATE TABLE `LEGO__CONSTRUCTION` (
`id` int(11) NOT NULL auto_increment,
`reference` varchar(15) NOT NULL UNIQUE,
`nom` varchar(255) NOT NULL,
`description` text NULL,
`date_creation` timestamp(10) NOT NULL,
`theme` int(11) NOT NULL,

CONSTRAINT `affilier` FOREIGN KEY (`theme`)
REFERENCES `LEGO__THEME` (`id`)
ON DELETE CASCADE ON UPDATE CASCADE,

PRIMARY KEY (`id`)
)TYPE=InnoDB;

Voilà, nous venons d'introduire un concept primordial dans la création de base de données relationnelle : les contraintes. Ici par exemple, en créant une telle base de données, nous venons de contraindre les champ Theme de la table CONSTRUCTION à toujours avoir une valeur égale à une clé primaire de la table THEME.

En gros, il sera impossible d'enregistrer une construction dont le thème n'existe pas. Dans le cas contraire, MySQL vous en refusera l'insertion.

Autre point important, nous venons en moins d'une ligne d'affecter un comportement qui peut s'avérer extrêmement utile. La suppression et la modification en cascade (ON DELETE CASCADE, ON UPDATE CASCADE). Cette instruction permet par exemple d'enchainer une série de suppression et de modification en cascade.

Par exemple, si nous souhaitons supprimer le thème "Voitures", la base de données supprimera automatiquement toutes les constructions qui ont se thème pour attribut. De même, l'instruction "ON UPADTE CASCADE" modifieras toutes les entrées de votre thème dans la table CONSTRUCTION si vous en modifié la valeur dans THEME.

Sachez que d'autres instructions du même genre sont possibles, par exemple :

ON DELETE RESTRICT,
ON UPDATE CASCADE

ON DELETE NO ACTION,
ON UPDATE RESTRICT

La table Brique

Avant de nous préoccuper de notre lien de composition, je voudrais vous donner un autre exemple de contrainte réalisable. Il s'agit de la contrainte d'unicité qui permet sur une table de rendre unique une valeur, ou un ensemble de valeurs sur toutes les occurrences.

Par exemple, on sait qu'une clé primaire sous entend une contrainte d'unicité. En effet, sur une même table vous ne pourrais jamais avoir deux valeurs identiques pour une clé primaire.

Il s'agit un peut du même principe ici. Prenons par exemple une brique. Dans notre base de données nous souhaitons éviter d'avoir plusieurs fois enregistré des briques avec des caractéristiques identiques.

En fait il serait incohérent d'enregistrer plusieurs fois une brique de même hauteur/largeur/profondeur avec un numéro d'identifiant différent. Nous allons donc lors de la création de notre table BRIQUE déclarer la contrainte Caractéristiques qui aura pour rôle d'interdire ce genre de doublons.

CREATE TABLE `LEGO__BRIQUE` (
`id` int(11) NOT NULL auto_increment,
`hauteur` integer NOT NULL,
`largeur` integer NOT NULL,
`profondeur` integer NOT NULL,

UNIQUE `caracteristiques` (`hauteur`, `largeur`, `profondeur`),

PRIMARY KEY (`id`)
)TYPE=InnoDB;

La table Composer

Désormais il ne nous reste plus qu'a créer notre lien de composition entre les tables CONSTRUCTION et BRIQUE. Comme nous l'avons vu juste au dessus, un lien d'agrégation se traduit par une clé étrangère. Pour la composition, ce sera le même principe, en un peut plus complexe. Car au lieu de n'avoir qu'une clé étrangère, nous en auront deux. Et cet ensemble de clés étrangères formera la clé primaire d'une nouvelle table.

Attention, il est primordial que la clé primaire de cette nouvelle table soit la concaténation des clés étrangères des entités en relation. Sinon cela n'a aucun intérêt. J'ai déjà pu voir très récemment des tables se voulant être des liens de composition mais dont la clé primaire est un champ complètement autonome. C'est à éviter à tout prix !

CREATE TABLE `LEGO__COMPOSER` (
`construction` int(11) NOT NULL,
`brique` int(11) NOT NULL,
`nombre` integer NOT NULL DEFAULT 1,

CONSTRAINT `construction_composer` FOREIGN KEY (`construction`)
REFERENCES `LEGO__CONSTRUCTION` (`id`)
ON DELETE RESTRICT ON UPDATE CASCADE,

CONSTRAINT `brique_composer` FOREIGN KEY (`brique`)
REFERENCES `LEGO__BRIQUE` (`id`)
ON DELETE RESTRICT ON UPDATE CASCADE,

PRIMARY KEY (`construction`, `brique`)
)TYPE=InnoDB;

Comme vous pouvez le voir, cela nous permet de porter des données sur notre relation. Souvenez vous lorsque dans un billet précédant je vous parlez de "porteuse de données". Et bien voici ici comment cela se traduit concrètement (avec le champ nombre).