Paint de phpMyAdmin avec moi et des pouces verts yay tout est clean cool cool

Bonne pratiques pour la gestion de base de données

Attention, cet article est assez dense, et il a plus vocation à être utilisé comme un “pas-à-pas” (s’assurer qu’on a rien oublié) plutôt que comme un véritable article d’apprentissage.

Création de la base de données

  • Sur le logiciel (phpMyAdmin / Workbench) Ne pas paniquer : en cas d’erreur (nombre de tables différents, nombre de champs différents, erreur lors de la saisie, etc.), on peut toujours revenir sur la structure de la BDD à posteriori, via les onglets “Structure” et “Opérations”
  • Faire un schéma papier de la base de données à créer, avec
    • Les différentes tables (entités)
    • Leurs champs + leur type
      • suffixe avec le nom de la BDD, par exemple “nom” devient “nom_utilisateur” pour la table “utilisateur”
      • Exemples de typages
        • Nom, prénom, etc. > utiliser une chaîne de caractères avec suffisamment de caractères
        • Texte court (description de produit, commentaire, etc.)
          • VARCHAR (2000)
        • Texte long (article de blog, etc.)
          • TEXT
        • Nombre entier (Quantité, Nombre de participants, etc.)
          • INT (11)
          • La taille représente le nombre de chiffres autorisés, par exemple une taille de 6 permet d’aller jusqu’à 999 999.
        • Nombre à virgule (Prix, etc.)
          • FLOAT (11)
          • 🚨 Attention, il faut utiliser des points et non des virgules comme séparateur, ex : 9.99
        • Dates, Dates et heure, heure
          • DATE
          • DATETIME
          • TIMESTAMP
          • Les formats sont assez spécifiques, je vous recommande de suivre la documentation.
        • Booléens ( vrai / faux )
          • BOOLEAN
        • Choix personnalisés
          • ENUM
          • Permet de définir des valeurs à choisir parmi une liste
    • Ne pas oublier les clés primaires (sers à éviter/différencier les doublons, par exemple les homonymes)
      • Attributs à définir
      • Type INT, de taille 11
        • Edit 2023 : Gaffe si vraiment plus de 10 milliards d’entrées (logs automatiques par exemple), passer sur INT 20 au pire
      • Index > Primary  ou PK (Primary key) ou Primaire
      • Attributs > Unsigned (ne peut être négatif)
      • A_I > Auto-incrément : Ce champ sera géré automatiquement par le logiciel (ne pas remplir), qui attribuera un numéro unique à chaque entrée (ex: le premier sera “1”, le deuxième “2”, le troisième “3”, etc.)
    • Ne pas oublier les clés étrangères, permettant les jointures entre les tables
      • Je recommande de les placer en fin de table
    • Faire une vérification avec le cahier des charges et les maquettes (si disponibles), afin de vérifier qu’on a fait aucun oubli
    • Attention ! Ne pas utiliser de tirets “-” dans les noms des champs, sous peine de créer de nombreux bugs
  • Encodage “utf32_general_ci” (ou utf16 / 8 si non disponible (ancienne version)) afin de permettre la bonne gestion des accents & caractères spéciaux
    • Edit 2023 : Plutôt partir sur du 16 afin de maximiser la compatibilité, ou laisser le défaut ~utf8mb4
  • Bonne pratique : lors de la création de profil utilisateur, ne pas stocker l’âge (qui change chaque année..) mais stocker plutôt la date d’anniversaire. Un rapide calcul permet de retrouver l’âge 😉

Edit 2020 : Astuces

Éviter les redondances et exceptions

Il faut noter qu’il est toujours bon d’éviter les redondances : si une valeur est stockée a de multiples reprises dans différentes tables, ce n’est jamais bon.

De plus, il faut souvent privilégier le calcul de certaines valeurs plutôt que de les stocker “en dur”, par exemple :

Pour un système de réservations, plutôt que de stocker le nombre de places restantes (et incrémenter/décrémenter en fonction des réservations), il vaut mieux stocker le nombre de places maximum et calculer à la volée le nombre de personnes inscrites via une requête SQL : “Pour cette réservation, il y a actuellement 6 personnes d’inscrites. 8 personnes maximum. Il reste donc 2 places.”

Une exception notable est de prendre en compte le fait que certaines valeurs peuvent évoluer au cours du temps. Par exemple pour un système d’achat, il serait logique de ne pas stocker le prix d’un produit dans une commande, puisqu’on peut le retrouver à partir de son entrée dans la table produit.

Toutefois, étant donné que le paiement se fait à un instant T, mais que le prix du produit peut évoluer dans le temps, je recommande de stocker le prix (et le total) dans la commande.

“Même si le prix du produit est maintenant de 20€, le client qui l’a acheté il y a 6 mois l’a payé 18€, car c’était son prix à l’époque.”

Cela permet de conserver un historique fiable, et immuable.

Niveaux, privilégier les nombres

Une autre astuce est de stocker les niveaux dans des nombres plutôt que des description.

Par exemple, pour un système de vente de randonnées de différents niveaux, plutôt que de stocker les niveaux “facile”, “moyen”, “expert” et “pro”, je recommande de stocker les intitulés dans un champ et des valeurs dans un autre ; ici respectivement “1” pour facile, “2” pour moyen”, “3” pour “expert” et “4” pour “pro”.

De cette manière, si un client expert souhaite faire une randonnée, plutôt que de faire une requête “Affiche les randonnées ‘facile’, ‘moyenne’ et ‘pro’”, on peut lui afficher “Toutes les randonnées dont le niveau est inférieur ou égal a 3″.

La base est ainsi plus flexible en cas d’évolution : si un jour un niveau “0 / débutant” est rajouté, pas besoin de modifier les autres  requêtes 🙂

Astuce supplémentaire : afin d’ajouter encore plus de flexibilité et de granularité, je recommanderai même de nommer les différents niveaux “100”, “200”, “300”, “400”.

Ainsi, si l’on doit rajouter un niveau intermédiaire, par exemple “avancé” entre moyen et expert, pas besoin de décaler expert en 4 et pro en 5 (ou de changer le type pour float et de mettre 2,5).

Avancé peut prendre directement la valeur 250. Les autres valeurs ne bougent pas, et les requêtes sont respectées.

Edit 2023 : Toujours d’actualité, mais ne pas hésiter à stocker une description directement en BDD, et ne pas afficher le libellé uniquement dans le front. Si les équipes front & back sont séparées / ne communiquent pas / peu c’est vecteur à erreur. A minima rajouter un commentaire SQL sur la colonne.

Ajout de contenu par défaut

Ajouter du contenu par défaut est une étape importante lors de la création de la base de données, il permet d’effectuer des tests afin de s’assurer que le contenu (types de champs, valeurs, nombres d’entrées dans la bdd) est bien correct à la fois dans notre BDD, mais également dans les résultats de nos requêtes.

  • Aussi parfois appelé “Dummy content”
  • Pour chaque table, ajouter 2-3 entrées (lignes) de contenu fictif
    • Pour cela (dans phpmyadmin)
    • Cliquer sur la table souhaitée
    • En haut, onglet “Insérer”
    • Laisser la clé primaire vide (sera remplie automatiquement)
    • Remplir les champs “Valeurs” avec du contenu réaliste, cela permettra de s’assurer que chaque champ correspond bien.

Edit 2020 : Vous pouvez également utiliser des générateurs de fausses données réalistes en ligne (je reco Mockaroo mais il doit exister des alternatives, par exemple pour des données FR).

^ Note perso : Pensez également a toujours récupérer un jeu de données au format JSON, cela permet de faire des tests côté front uniquement (des fois que le back soit mort).

Filets de sécurité / Exports & Imports

Il est important de pouvoir travailler l’esprit serein, notamment sur des bases de données clients conséquentes (par exemple contenant des milliers d’articles de blog, ou des milliers d’inscriptions d’utilisateurs payants).
Pour cela, nous pouvons effectuer des sauvegardes de BDD, et également restaurer ces sauvegardes lorsque c’est nécessaire.

Export

L’export de base de données va consister en la création d’un fichier de type texte, contenant l’ensemble des valeurs de la base de données. La plupart du temps cet export est au format SQL, mais on peut également le trouver au format JSON. Il se peut que plus tard vous ayez des exports de données ciblées à faire depuis vos bases de données, elles seront alors envoyées (à vos commerciaux :}) au format JSON, afin que ces derniers puissent les importer dans Excel et en faire des jolis tableaux / graphiques. Bref.

Afin de réaliser une sauvegarde depuis phpMyAdmin il faut

  1. Cliquer sur la base à sauvegarder
  2. En haut, Onglet “Exporter”
    1. Format permet de choisir le format (défaut SQL)
    2. Choisir export “personnalisé
      1. S’assurer que l’ensemble des tables sont sélectionnées
      2. S’assurer que l’encodage est bien utf-8
      3. Compression > en cas de soucis de performances (fichiers trop volumineux pour le serveur local, ou les contraintes PHP) faire un export en .zip
      4. Options de format
        1. Maximiser la compatibilité > MYSQL40
        • au cas ou votre export devrait avoir lieu sur un vieux serveur / une vieille version de sql
      5. Options de créations d’objets
        1. Cocher CREATE DATABASE
        2. Cocher DROP TABLE
        • Crée la base de données si besoin, supprime les tables existantes (afin de s’assurer que les données entrées sont clean). Moins fastidieux, cf. docs si besoin
  3. Bouton “Exécuter” en bas
  4. Nommer son fichier correctement (ex : 170719_sql_dump_Restaurant.zip ) et le ranger dans un dossier approprié (ex : /dumps/sql )

Import

En cas de site dépendant de données (ex: WordPress, e-commerce, etc.) cela permet de faire une installation classique, d’insérer des données par défaut et de s’assurer que tout fonctionne bien / de faire une démo client.
Egalement très utile si vous avez besoin de tester le site (ajout d’utilisateurs, de produits, d’articles, etc.) que vous devez revenir à une BDD saine avant la mise en ligne ; ou en cas de problème (crash serveur, piratage avec destruction de données, etc.).

Depuis phpMyAdmin

  1. En haut, onglet Importer
    1. Spécifier compatibilité MYSQL40, si vous l’avez spécifié lors de votre export
    2. 🚨 SURTOUT SURTOUT SURTOUT décocher la case “Permettre l’interruption de l’import…” afin de vous assurer que l’import ne soit pas fait à moitié (qu’il manque des entrées dans la bdd, ou d’autres problèmes techniques plus graves) liés aux performances de phpmyadmin.
    3. Bouton “Exécuter” en bas

En cas de trop grosse base de données, vous pouvez également exporter les tables une par une (une par fichier).

D’un point de vue général, si vous souffrez des limitations techniques de phpMyAdmin / de votre serveur local, passez par un logiciel tiers (ex: Workbench). Cela sera bien plus performant et sûr.

Création des requêtes (PHP) et tests

Trop de blahblah, voir l’article dédié 🙂

🚨 Attention à ne pas confondre le guillement un peut spécial de Mysql ` (Alt Gr + 7) avec les guillemets classiques ‘.

Gestion des utilisateurs SQL

Pour des raisons de sécurité, je recommande de créer à minima un utilisateur par base de données.

Sur des sites plus avancés, on peut même avoir plusieurs utilisateurs pour une seule base de données, chacun avec des droits différents.

Dans un article dédié 🙂

Edit 2021 :

Attention au nombre de caractères minimum pour les noms/prénoms. Je recommandais 2, mais maintenant c’est 1. Edit 2023 : lel je vient de le remettre en haut #trauma x’D

Si vous rencontrez des problèmes pour créer vos liens/contraintes entre vos différentes tables (“la base” > concepteur > liaison) :

  • Je n’ai pas le bouton “concepteur” > Assurer d’être sur la base, et non sur une table. Votre écran/navigateur n’est pas assez large, survolez le bouton “Plus”.
  • La clé étrangère doit avoir l’index “INDEX”
  • Vérifier que les types, les longueurs, ainsi que les attributs (~unsigned) des clés primaires/étrangères à relier soient identiques.
  • Si les tables à lier comportent déjà des données, vérifiez que les données concordent. Par exemple, si je souhaite lier la table articles à la table catégories en rajoutant la clé étrangère id_categories dans la table articles, les valeurs des clés étrangères doivent correspondre.
  • Il n’y a pas d’erreur mais le lien n’apparait pas sur le concepteur : actualiser la page. Note : sur certaines versions de phpmyadmin, les liens n’apparaissent jamais ¯\_(ツ)_/¯.
  • Si vraiment cela ne passe pas, s’assurer que les tables (et non la base) utilisent le moteur de stockage “InnoDB”, et pas un autre. “La table” > Menu “Opérations” > “Moteur de stockage”

^Merci à la promo La passerelle Freya :3  sauf Alexandre LAUL


Commentaires

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *