Guide du débutant pour l’écriture de schémas de base de données mySQL

Lors du développement d'un projet logiciel, l'un des aspects les plus importants, fondamentaux et intrinsèques est un schéma de base de données correctement structuré. C'est l'équivalent de la construction d'une maison dont vous avez besoin pour vous assurer que les fondations sont correctement posées, sinon les chances de construire une maison de qualité sont considérablement réduites.

Étonnamment plus facile qu'on ne le pense, apprenons les différentes facettes utilisées pour écrire un schéma de base de données bien architecturé.

Syntaxe de CREATE TABLE

Pour commencer, ouvrez votre éditeur de texte préféré. La création d'un schéma de base de données ne nécessite rien de plus qu'un fichier texte brut. Une base de données se compose de plusieurs tables, chacune composée de colonnes, et la syntaxe CREATE TABLE est utilisée pour créer une seule table. Voici un exemple de base:

 
CREATE TABLE users (
 id INT NOT NULL,
 is_active TINY INT NOT NULL,
 full_name VAR CHAR(100) NOT NULL,
 email VARCHAR(100) NOT NULL
 );

Comme vous pouvez le voir, cela créera une table de base de données nommée users qui se compose de quatre colonnes. Cela devrait être une instruction SQL assez simple commençant par CREATE TABLE , suivi du nom des tables de la base de données, puis entre parenthèses les colonnes de la table séparées par une virgule.

Utiliser les types de colonnes corrects

Comme indiqué ci-dessus, les colonnes qui composeront le tableau sont séparées par des virgules. Chaque définition de colonne comprend les trois mêmes parties:

 COL_NAME    TYPE    [OPTIONS]

Le nom de la colonne, suivi du type de colonne, puis de tout paramètre facultatif. Nous aborderons les paramètres facultatifs plus tard, mais en nous concentrant sur le type de colonne, ci-dessous répertorie les types de colonnes les plus couramment utilisés disponibles:

À toutes fins utiles, les types de colonnes ci-dessus sont tout ce dont vous avez besoin pour écrire des schémas de base de données mySQL bien construits.

Définir les options de colonne

Lors de la définition des colonnes, vous pouvez également spécifier diverses options. Voici un autre exemple de l'instruction CREATE TABLE :

 
CREATE TABLE users (
 id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
 username VARCHAR(100) NOT NULL UNIQUE,
 status ENUM('active','inactive') NOT NULL DEFAULT 'active',
 balance DECIMAL(8,2) NOT NULL DEFAULT 0,
 date_of_birth DATETIME,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
 );

Ce qui précède peut sembler un peu intimidant, mais ne vous inquiétez pas, c'est assez simple. En panne, voici ce qui se passe dans la déclaration ci-dessus:

• Vous devez toujours utiliser NOT NULL sur toutes les colonnes possibles pour améliorer la vitesse et les performances de la table. Cela spécifie simplement que la colonne ne peut pas être laissée vide / nulle lorsqu'une ligne est insérée.
• Essayez toujours de garder la taille de la colonne aussi petite que possible, car cela contribue à améliorer la vitesse et les performances.
• La colonne id est un entier, est également la clé primaire de la table, ce qui signifie qu'elle est unique et incrémentée de un à chaque fois qu'un enregistrement est inséré. Cela doit généralement être utilisé sur toutes les tables que vous créez afin que vous puissiez facilement référencer une seule ligne dans la table.
• La colonne d' état est un ENUM et doit avoir la valeur «actif» ou «inactif». Si aucune valeur n'est spécifiée, une nouvelle ligne commencera avec le statut «actif».
• La colonne de solde commence à 0 pour chaque nouvelle ligne et est un montant qui est formaté deux deux décimales.
• La colonne date_of_birth est simplement une DATE mais autorise également une valeur nulle car la date de naissance peut ne pas être connue lors de la création.
• Enfin, la colonne created_at est un TIMESTAMP et utilise par défaut l'heure actuelle à laquelle la ligne a été insérée.

Ce qui précède est un exemple de table de base de données bien structurée, et devrait être utilisé comme exemple à l'avenir.

Lier des tables avec des contraintes de clé étrangère

L'un des plus grands avantages de l'utilisation de bases de données relationnelles telles que mySQL est son excellent support pour les contraintes de clés étrangères et la cascade. C'est lorsque vous liez deux tables ensemble par une colonne, formant une relation parent-enfant, de sorte que lorsque la ligne parent est supprimée, les lignes enfants nécessaires sont également supprimées automatiquement.

Voici un exemple:

 
CREATE TABLE users (
 id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
 username VARCHAR(100) NOT NULL UNIQUE,
 full_name VARCHAR(100) NOT NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
 ) engine=InnoDB;
 CREATE TABLE orders (
 id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
 userid INT NOT NULL,
 amount DECIMAL(8,2) NOT NULL,
 product_name VARCHAR(200) NOT NULL,
 FOREIGN KEY (userid) REFERENCES users (id) ON DELETE CASCADE
 ) engine=InnoDB;

Vous remarquerez la clause FOREIGN KEY en dernière ligne. Cette ligne indique simplement que cette table contient des lignes enfants qui sont liées par la colonne userid à leur ligne parent, qui est la colonne id de la table users . Cela signifie qu'à chaque fois qu'une ligne est supprimée de la table des utilisateurs , mySQL supprimera automatiquement toutes les lignes correspondantes de la table des commandes pour garantir l'intégrité structurelle de votre base de données.

Notez également le moteur = InnoDB à la fin de l'instruction ci-dessus. Bien qu'InnoDB soit maintenant le type de table mySQL par défaut, ce n'était pas toujours le cas, donc cela devrait être ajouté juste pour rester sûr, car la cascade ne fonctionne qu'avec les tables InnoDB.

Concevez en toute confiance

Vous êtes maintenant sur la bonne voie pour concevoir des schémas de base de données mySQL solides et bien structurés. En utilisant les connaissances ci-dessus, vous pouvez maintenant écrire des schémas bien organisés qui fournissent à la fois des performances et une intégrité structurelle.

Une fois votre schéma en place, assurez-vous de savoir comment l'utiliser avec ces commandes SQL essentielles .