Passé maitre dans l'art du teasing, voici maintenant le vrai début de cette série.

Je rappelle qu'il s'agit là, d'une série sur les bonnes pratiques du PHP, et non pas de tutoriel d'apprentissage. Pour les comprendre, il s'agit donc de déjà savoir faire du PHP avec MySQL, que ce soit à un niveau plus ou moins avancé. Pour cet article, je considère que vous savez déjà créer une BDD, une table, et concevoir un minimum pour avoir quelque chose de fonctionnel (a défaut d'être souple et/ou performant).

1. Ai-je besoin d'une base de données ?

La base de données est un système "simple" qui sert a stoquer des données (oui, je suis pas aller le chercher loin ! :$) ... Il s'agit donc de la base sur laquelle repose la plupart des applications dynamiques. Les applications les plus connues utilisant une base de données sont des forums, des blogs ou des boutiques en ligne. Elles permettent non seulement d'enregistrer des données, mais également des les récupérer de manière selective au moyen du langage SQL.
Mais attention à ne pas faire non plus du tout BDD ! En effet, certaines situations ne nécessite pas de base de données :

• Gestion de fichiers : la plupart des informations d'un fichier sont directement accessibles depuis PHP (nom, taille, date de création/modification). Une BDD qui stockerait des noms de fichiers avec ces informations ne serait qu'une répétition de ce que nous pouvons savoir sans cela (souvenez-vous de la règle d'or ;)) ! En effet, il devient difficile à ce moment là ce garder une cohérence permanente entre les données. Par exemple, on peut envisager créer un diaporama sans base de données, qui se contenterait d'afficher les images présentes dans un répertoire. Par contre, si l'on veut donner à ces images un titre ou le nom de celui qui a pris la photo, une base de vient nécessaire.
  
• Données de configuration : pour moi, une BDD est axée sur le contenu. Les directives de configuration telles que des chemins racines, la langue d'une application ou autre donnée nécessaire à l'initialisation de celle-ci doivent être regroupées dans un espace qui leur est propre. A priori, il peut s'agir d'un fichier .ini ou XML, mais certainement pas d'une base de données (ou alors une BDD dédiée). Déjà, les paramètres de connexion à une base doivent bien être initialisés quelque part, je préfère alors y mettre l'ensemble des paramètres de l'application. En gros, je dirais qu'une base doit contenir les données qui servent à faire tourner l'application, pas à la lancer ...

2. Créer sa base de données

2.1 Conventions de nommage et de structure.
Comme pour le code, il est bon de se tenir à des conventions lorsque l'on crée une base de données. Voici celles que j'utilise :

• 2.1.1 Utiliser un préfixe pour les noms de table.
  Il s'agit d'un "identifiant" qui permet de regrouper les tables par fonctionnalités. En général, il contient entre 2 et 4 lettres. Par exemple, pour le site d'une boutique appellée "Vraiment Pas Cher", tous mes noms de tables vont commencer par 'vpc_'. Dans l'optique d'un hébergement partageant plusieurs applications, cela me permet d'avoir plusieurs tables utilisateurs ou articles. En plus de différencier quelle table est utilisée par quelle application uniquement par son nom, c'est aussi utile dans phpMyAdmin pour les regrouper, quand les tables sont affichées par ordre alphabétique ! Une seule application de portail pourrait également dissocier les différents types de données gérées : usr_ pour la gestion utilisateurs, cmd_ pour les commandes, prd_ pour les produits, etc...
  
• 2.1.2 Utiliser des noms sans casse.
  En effet, si aujourd'hui vous vous servez de MySQL, sachez que la version Windows est insensible à la casse, mais pas la version Linux. D'autres systèmes ou bases de données peuvent être plus ou moins sensibles à la casse. C'est pourquoi j'utilise systématiquement des noms de table et de champs uniquement en minuscule.
  
• 2.1.3 Utiliser des noms de table sans separation.
  En gros, ca revient à ne pas mettre de underscore ( _ ) dans un nom de table "normale". Nous verrons plus tard que cette règle possède une exception qui vient la confirmer ...
  
• 2.1.4 Noms de table au singulier
  Le nom d'une table doit décrire ce que contient une ligne de cette table. Les noms de tables se doivent donc d'être au singulier.
  
• 2.1.5 Clés primaires.
  Chaque table possède une clé primaire numérique, dont le nom est id_. Le préfixe dont je parle plus haut n'est pas considéré comme faisant partie du nom de la table. Par exemple, une table supv_voyage aura une clé primaire du nom de 'id_voyage'. En MySQL, la clé primaire aura l'attribut auto_increment. Je me sers du type INT(10) UNSIGNED pour mes clés primaires.
  
• 2.1.6 Langue de la base.
  Personnellement, j'utilise généralement l'anglais dans mes noms de table et de champs, mais le plus important est de rester constant dans la langue que l'on utilise. Nous resterons en français dans les exemples de cet article.

D'autres viendront au cours de l'article pour enrichir celles-ci, au moment où nous aborderons les notions qui justifient ces conventions.

2.2 Un premier schéma
Imaginons que l'on décide de créer un jeu en ligne nommé "Maxi Questions". Ce jeu consiste en des questions sur divers thèmes, et les joueurs peuvent y répondre. A chaque question, un joueur ayant répondu juste gagne 2 point, un joueur ayant répondu faux perd 1 point.
En prenant compte des données "classiques" dont aurait besoin ce type d'application, nous avons besoin pour l'utilisateur d'un login et d'un mot de passe. Il va nous falloir aussi connaitre son score. Une question a un libellé et un theme. Une réponse consiste en un texte et une correction (juste ou faux). Voyons le trés mauvais exemple duquel nous pouvons partir pour améliorer les choses :

Voyons maintenant comment modifier ce schéma pour qu'il soit rigoureux et performant :
2.2.1 Les clés étrangères.
Dans l'exemple précédent, la table des réponses utilise le pseudo pour savoir quel utilisateur à entré cette réponse. Cela pose plusieurs problèmes :

• Tout d'abord, en terme de performances. En effet, effectuer une jointure sur un champ texte est bien plus exigeant que sur un champ numérique.
  
• En terme de fonctionnalité. Imaginons que pour une raison ou pour un autre, nous décidions d'autoriser plusieurs pseudos identiques s'ils ont des mot de passe différents (a ne pas conseiller, mais il me fallait bien un exemple :P) ... Comment retrouver alors celui qui a répondu ?

Dans ce cas précis, il est préférable d'utiliser l'id_utilisateur pour faire la liaison entre mxq_reponse et mxq_utilisateur. C'est là qu'intervient une convention suplémentaire : les clés étrangères se font sur une clé primaire, et portent le nom de cette clé primaire.
Voici ce que donne le schéma à l'issue de cette première étape :

2.2.2 Regroupement des colonnes
Voici un des premiers aspects de notre règle d'or : "Ne pas répeter deux fois la même chose !!!" !
En effet, le thème d'une question est ici saisi en toutes lettres dans la table question. Or, il se peut tout à fait qu'il existe plusieurs questions qui ont le même thême, c'est même à priori pour ça qu'il existe. Donc, si on ne veut pas répeter plusieurs fois le thême "Nature", il va falloir le centraliser. Le meilleur moyen est donc de créer une table qui va contenir les thêmes, et faire un clé étrangère dans la table des questions. Cette solution amène plusieurs avantages :

• Si l'on veut changer le nom d'un thème, il ne suffit de le modifier qu'une seule fois pour que cela soit appliqué partout.
  
• On évite les incohérences en entrant à chaque fois le nom du thême pour chaque nouvelle question. Si c'était le cas, nous ne sommes jamais à l'abris d'une faute de frappe, de casse, ou de doublons.
  
• Si l'on veut faire un formulaire qui permet de choisir un thême, il est bien plus facile de créer une liste déroulante avec le contenu d'une table à part. L'identifiant du thême sera ensuite passé en paramètre au filtre.
  
• Et du coup, on améliore les performances des requêtes : en effet, une comparaison sur un numérique est bien plus rapide que sur une chaine de caractères, de la même manière que pour les clés étrangères.

Un indice pour détecter les colonnes à externaliser est de savoir quelles vont être les colonnes qui seront selectionnables via une liste déroulante dans les différents formulaires de l'application. Voici le schéma avec les thêmes externalisés :

2.2.3 Les données déductibles
Voici encore une déclinaison de la regle d'or. En effet, pourquoi enregistrer le score, alors que nous pouvons le déduire trés facilement en fonction du nombre de réponses fausses et justes ? Le champ score de la table des utilisateurs est donc superflu. Nous pouvons l'enlever sans perdre une seule information :

Cette règle toutefois n'est pas systématique : en effet, on peut vouloir changer les attributions de score en cours de jeu, mais conserver les scores tels qu'ils étaient avant l'ancien barême. C'est assez inhabituel de changer les règles en cours de jeu, mais sachez que dans ce cas là, le champ "score" retrouve toute justification.

2.2.4 Penser aux données statistiques
Dans toute application, et plus particulièrement s'il s'agit d'une application web, il est indispensable de développer des outils statistiques. Dans notre cas, il peut être utile de savoir la moyenne du nombre de réponse à une question, du nombre d'inscrits pas jour, le temps de réponse moyen à une question, etc...
C'est pourquoi sur la plupart de mes tables, je crée un champ nommé date_creation, dont la valeur par défaut est la date de création de la ligne (en MySQL : NOW()). Afin de pouvoir obtenir un feedback efficace, je vais donc rajouter ce champ aux tables principales. Ce ne sera pas nécessaire pour la table des thèmes : en effet, il y a peu de chance qu'on ai besoin de savoir quand un thème a été créé. A la rigueur, si l'on veut savoir quand un thème a été utilisé pour la première fois, il suffit de trouver la date de création de la première question de ce thème ...

2.2.4 Eviter la suppression de données
La plupart des grosses applications sur des données plus ou moins sensibles évitent au possible de supprimer des données. Il peut s'agir de raisons toutes simples d'archivage de la table elle-même, ou pour éviter d'avoir à supprimer des données d'autres tables que l'on veut conserver et qui sont liées à la table principale. En effet, imaginons qu'on veuille supprimer un utilisateur de la base. Que faire alors de ses réponses ? Si on les conserver, elles deviendront des réponses orphelines. Si on les supprime, les statistiques deviendront complètement fausses.
Et imaginons que cet utilisateur décide de revenir tout en conservant son score, il serait bien plus facile de l'avoir simplement désactivé. Il suffirait alors de le réactiver pour le réintégrer au jeu.
C'est pourquoi la plupart des tables succeptibles d'avoir des données ne servant plus possèdent toutes un champ "actif", qui contiendra 0 ou 1 selon que la ligne est active ou non :

Il faudra prévoir dans notre application de ne choisir que les lignes actives lorsque cela sera nécéssaire. Par exemple, pour un thème, on peut ne sélectionner que les thèmes actifs lorsque l'on crée une question, mais choisir tous les thèmes quand on consulte l'historique ...

2.3 Optimisation
2.3.1 Les indexes "simples"
Nous avons déjà vu dans le chapitre précédent que le fait d'utiliser des clés étrangères numériques améliore les performances. Il existe un moyen de les améliorer encore plus avec les indexes. Un index consiste à stocker dans un espace accessible rapidement les valeurs d'un colonne. Ainsi, si l'on accède souvent à une colonne indexée, ce sera bien plus rapide que sur une colonne normale. Par exemple, je conseille de créer un index sur toutes les colonnes de clés étrangères avec MySQL. Pour la création d'index, il est facile de le faire avec phpMyAdmin. Pour en créer un à la main, référez vous à la documentation SQL.
Dans notre exemple, les indexes seraient positionnés sur mxq_reponse.id_question, mxq_reponse.id_utilisateur, mxq_quesiton.id_theme.

2.3.2 Les indexes uniques
Encore plus rapide que les indexes dits "simples", les index uniques peuvent se faire sur une colonne ou un groupe de colonnes qui n'aura pas deux fois la même valeur. Dans notre exemple, si l'on considère qu'un même utilisateur ne peut pas répondre deux fois à la même question, cela signifie que le couple id_question/id_utilisateur n'aura jamais deux fois la même valeur. Une index unique sur ce couple de colonne est donc tout à fait opportun : mxq_reponse.id_utilisateur et mxq_reponse.id_question ! Cet index unique viendra s'ajouter aux indexes simples dont j'ai parlé plus haut. Une coup d'oeil sur la documentation SQL vous renseignera sur la syntaxe à utiliser (avec l'option "UNIQUE").

2.4 Notions supplémentaires
Les clés étrangères que nous avons vu jusqu'ici permettent de créer une relation 1,N (le 1 étant représenté par un losange blanc, et le N par un rond noir). En gros, cela veut dire qu'un utilisateur peut correspondre à plusieurs (N) réponses, mais qu'une réponse ne peut correspondre qu'à un utilisateur et un seul (1) ... Il s'agit de la cardinalité.

En plus ce cette cardinalité, il en existe deux autres :

2.4.1 Les relations "N,N"
Imaginons que nous voulions implémenter un système de groupes d'utilisateurs. En gros, un utilisateur peut appartenir plusieurs groupes (N), et un groupe peut avoir plusieurs utilisateurs (N) ... On est donc dans le cas d'une cardinalité N,N. Ce type de relation n'existe pas de manière indépendante. Il a besoin d'un table intermédiaire, qu'on peut appeler "Tables N,N". C'est là qu'intervient l'exception aux règles de nommage des tables dont j'ai parlé plus haut. En effet, les tables N,N ont le nom de chacune des deux tables à mettre en relation séparées par un underscore ( _ ) (l'ordre ayant peu d'importance : celle qui vous parait le plus importante est à mettre en premier) ... Cette table va contenir deux clés étrangères vers les deux tables à relier, ces deux clés étrangères formant un index unique. En effet, un groupe ne peut pas contenir deux fois le même utilisateur ... Le résultat est le suivant :

Ce type de relation est utilisé par exemple pour un caddie sur un site de ecommerce : un produit peut-être dans plusieurs caddies, et un caddie peut contenir plusieurs produits.

2.4.2 Les relations "1,1"
Par abus de langage, on peut considérer que la relation 0...1,1 rentre également dans le même cadre. Cela signifie qu'une table a une correspondance directe avec aucune ou une seule ligne de l'autre. En pratique, il s'agit d'une clé étrangère associée à un index unique. Cela peut servir lorsque l'on sait que des données d'une table ne seront pas forcément nécessaire pour toutes ses lignes. Imaginons par exemple que certains utilisateurs sont des administrateurs, et qu'ils ont un mot de passe différent pour accéder à la partie administration du site.
La solution de facilité serait de rajouter un champ 'mot_passe_admin' dans la table utilisateur. Mais il ne faut pas créer de champs dont la plupart seront vides. Il vaut bien mieux créer une table d'administrateur, reliée à la table utilisateur. Nous savons alors que chaque utilisateur à alors une correspondance dans la table adimnistrateur (s'il est admin) ou aucune (s'il est simple joueur). Voilà ce que cela donne :

L'index unique (non visible sur le schéma) sert à s'assurer que l'utilisateur n'aura pas deux mot de passes possibles pour la partie administration. Par similitude avec les tables question/theme, on voit bien que si l'index unique n'existait pas, on pourrait avoir plusieurs lignes administrateur correspondant à un seul utilisateur ...

Les schémas de base de données qui illustreront les articles sont fait avec un outil OpenSource performant que je vous conseille : DBDesigner (Window & Linux). Il est optimisé pour MySQL, mais peut servir d'outil de conception pour tout type de base.