Tutoriaux divers et variés

3WA / Bonne pratiques pour la gestion de la base de données

Par le 19 juillet 2017 dans Divers, Support, Support 3WA | 3 commentaires

Article sur les bonnes pratiques relatives à tout ce qui touche aux bases 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, prenom, etc. > utiliser une chaîne de caractères avec suffisamment de caractères

          • VARCHAR (200)
        • 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
      • 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
  • 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.

 

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 éxister 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 « Executer » 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é :)

 

Tags: , , , , , , , , , , , ,

    3 commentaires

  1. Je cite :
    « suffixe avec le nom de la bdd, par exemple « nom » devient « nom_utilisateur » pour la table « utilisateur » »

    Dans cet exemple, le nom du champs est suffixé avec le nom de la table et non de la bdd.

    Chaque table doit être, en terme de bonne pratique, préfixé avec une chaine se rapportant à la bdd, et chaque champs de table avec un préfixe (ou suffixe mais perso je préfère préfixer, question de lisibilité) se rapportant à la table :
    BDD : configuration
    TABLE : cf_user
    CHAMPS : user_name

    Alten

    13 avril 2018

    • Chacun a sa méthode ^^.

      Personnellement je préfère préfixer car l’information est directement plus accessible, et cela facilte également la lecture en cas de clé étrangère (les deux tables sont situées à la fin de la chaîne, la première étant la table en cours, la deuxième la table contenant le champ de liaison).

      Clairement ma méthode n’est pas reconnue universellement, mais j’aime sa simplicité de mise en place et d’utilisation : utiliser toujours cette même nomenclature permet d’une part de favoriser l’intuition et d’éviter les allers retours sur la structure de la base de données, et d’autre part cela facilite le travail en équipe (avec ce système je peux demander sans problèmes a un stagiaire de rajouter une paire de champs dans une de mes tables :p)

      Mais c’est vrai que cela ne prend pas compte de multiples bases de données ^^’

      Maxime Chevasson

      18 avril 2018

      • Dans le monde du BI, le préfixe est chaque fois celui du type de valeur :
        ex : nb_production_number, dt_datetime, lb_name, cd_country_code…

        Le nom de la table n’est quant à lui pas repris, car ce serait redondant lors de l’accès aux différents tables…
        Dans la table « Users », il est clair que le champs « Name » se rapporte à l’utilisateur… Donc aucun besoin de l’appeller « user_name »
        Users.lb_name est très clair, Users.name_user est redondant, long et ne donne pas d’info sur le type de données…

        Syph-

        6 juillet 2020

Poster une réponse

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

Vous pouvez utiliser ces balises et attributs HTML : <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>