Jour 10 – Architecture de la base de données relationnelle

Bienvenue dans ce dixième jour de notre grande aventure #100JoursPourCoder. Aujourd’hui, nous allons entrer dans une phase cruciale de tout projet web : la conception de l’architecture d’une base de données relationnelle.

Si vous me suivez depuis quelques jours, vous savez que le projet Créa-code avance pas à pas, avec transparence, pédagogie et simplicité. Ce site, dont le but est de vous faire progresser en développement web grâce à des exercices, des quizz, des badges et une vraie gamification, prend vie en même temps que ces articles sont publiés. Et vous en êtes les témoins privilégiés.

Aujourd’hui, nous allons poser ensemble les fondations techniques de Créa-code : sa base de données. Ce guide vous explique comment et pourquoi on crée un schéma relationnel, à quoi servent les différentes tables, quelles sont leurs relations, et comment tout cela est pensé pour évoluer intelligemment avec le projet.

Qu’est-ce qu’une base de données relationnelle ?

Une base de données est comme la mémoire de votre site. C’est là que sont stockées toutes les informations importantes : les utilisateurs, les exercices, les résultats, les badges, les niveaux, et bien plus encore.

Pour Créa-code, j’ai choisi une base de données relationnelle. Cela signifie que les données sont organisées en tables, et que ces tables peuvent être reliées entre elles grâce à des relations. C’est un modèle très répandu, utilisé par des systèmes comme MySQL ou MariaDB.

Ce modèle est idéal pour les données bien structurées et organisées, comme c’est le cas ici. Il permet d’assurer la cohérence, la sécurité et la logique du projet, tout en gardant une bonne lisibilité.

Définir les besoins réels du projet

Avant même d’ouvrir un outil ou d’écrire une ligne de SQL, la première étape consiste à réfléchir. Que doit contenir le site ? Quelles entités vont devoir exister dans notre base de données ?

Dans le cas de Créa-code, les fonctionnalités déjà prévues sont nombreuses :

• Un système de comptes utilisateurs
• Des exercices
• Des quizz
• Des missions ou challenge
• De l’expérience à gagner
• Des badges à collectionner
• Des avatars pour se représenter
• Un suivi de progression
• Des commentaires pour interagir
• Des likes pour marquer ce qu’on apprécie

Chaque fonctionnalité est associée à une ou plusieurs tables.

Lister les tables nécessaires

Voici les principales entités que nous allons devoir représenter en base de données :

• users : les comptes utilisateurs
• avatars : les images que les utilisateurs peuvent choisir pour se représenter
• roles : pour différencier un visiteur, un utilisateur, un administrateur
• exercices : les exercices pratiques proposés sur la plateforme
• quizz : les séries de questions pour tester ses connaissances
• quizz_questions et quizz_answers : le détail des questions et des réponses possibles
• progression : le suivi de chaque utilisateur sur les exercices
• quizz_results : les scores obtenus aux quizz
• xp_history : les actions qui donnent de l’expérience (XP)
• badges : les récompenses obtenues pour certaines réussites
• user_badges : les badges remportés par chaque utilisateur
• missions : des ensembles d’objectifs à valider
• mission_elements : le lien entre une mission et ses composants
• mission_progress : le suivi de progression sur chaque mission
• commentaires : les messages laissés par les utilisateurs sur les exercices
• likes : les « j’aime » laissés sur les commentaires

Toutes ces entités seront transformées en tables dans notre base de données.

Comprendre les relations entre les tables

Dans une base de données relationnelle, les relations entre les tables sont aussi importantes que les tables elles-mêmes. C’est ce qui permet d’avoir un système logique, efficace et évolutif.

Prenons quelques exemples simples :

• Un utilisateur peut avoir plusieurs badges → cela implique une table de liaison user_badges
• Un exercice peut être fait par plusieurs utilisateurs → il faut une table progression
• Un quizz contient plusieurs questions, qui ont plusieurs réponses → il faut trois tables distinctes, liées entre elles

Pour chaque relation, on utilise ce qu’on appelle une clé étrangère : un champ qui permet de faire le lien entre deux tables. Par exemple, dans la table progression, on aura un champ user_id et un champ exercice_id pour savoir quel utilisateur a fait quel exercice.

Consultez notre guide pour en savoir plus sur les clés et index pour une base de données.

Créer un schéma relationnel

Une fois les entités identifiées et les relations entre elles comprises, vient le moment de les représenter de manière visuelle sous forme d’un schéma relationnel. Cela permet d’avoir une vue d’ensemble et de vérifier que tout est cohérent.

Pour cela, j’ai utilisé l’outil dbdiagram.io, qui permet de créer un diagramme simple, en texte, et de le transformer en image ou en schéma interactif.

Voici le schéma de nos tables :

Pourquoi une telle organisation ? Une base solide mais évolutive

Le but ici n’est pas seulement de faire un schéma “pour faire joli”. Il s’agit de construire une architecture pensée pour durer, qui permettra d’ajouter de nouvelles fonctionnalités sans tout casser.

Par exemple, le système de missions peut être enrichi plus tard avec des types d’objectifs supplémentaires (comme des vidéos à regarder, ou des défis communautaires). La table xp_history pourra enregistrer toutes sortes d’actions à l’avenir (connexion quotidienne, commentaire utile, partage sur les réseaux…)

Le fait d’avoir séparé les quizz, questions et réponses permet de gérer facilement des quizz complexes, avec plusieurs types de réponses.

Cette structure est aussi pensée pour éviter les doublons, centraliser les données importantes, et assurer l’intégrité des informations (un commentaire ne peut exister que s’il est lié à un utilisateur et à un exercice, par exemple).

Explorer chaque table et ses rôles dans la base de données

Maintenant que vous avez une vision d’ensemble du schéma relationnel de Créa-code, prenons le temps d’examiner chaque table une à une. Cela vous permettra de mieux comprendre le rôle de chacune, ainsi que les raisons de leur existence.

La table users

C’est la table la plus centrale. Elle contient toutes les informations relatives à un utilisateur inscrit sur Créa-code. Chaque ligne représente une personne.

Elle est liée à :

• Un avatar (via avatar_id)
• Un rôle (via role_id)
• Sa progression (via progression)
• Ses badges gagnés (via user_badges)
• Son historique d’XP (via xp_history)
• Ses réponses à des quizz (quizz_results)
• Ses commentaires (commentaires)
• Ses missions en cours ou terminées (mission_progress)

C’est ici que se trouve l’essentiel : pseudo, mot de passe sécurisé, adresse e-mail, date d’inscription, dernier accès, etc.

Les tables roles et avatars

Ces deux tables servent à compléter l’utilisateur sans alourdir la table users.

• roles permet de différencier un simple visiteur, un utilisateur classique ou un administrateur. Ce champ pourrait à l’avenir gérer des accès avancés.
• avatars contient des images ou icônes que l’utilisateur peut choisir pour se représenter sur la plateforme.

La table exercices

Chaque exercice correspond à un petit défi de codage, un problème à résoudre ou une tâche à accomplir. C’est le cœur de l’apprentissage sur Créa-code.

Chaque exercice pourra comporter :

• Un énoncé détaillé
• Un exemple de solution
• Des indices
• Une validation automatique

La table progression

Elle enregistre la progression de chaque utilisateur, exercice par exercice. Par exemple, si vous avez terminé 8 exercices sur 10 dans un parcours, c’est ici que cela est stocké.

Des formations informatique pour tous !

Débutant ou curieux ? Apprenez le développement web, le référencement, le webmarketing, la bureautique, à maîtriser vos appareils Apple et bien plus encore…

Formateur indépendant, professionnel du web depuis 2006, je vous accompagne pas à pas et en cours particulier, que vous soyez débutant ou que vous souhaitiez progresser. En visio, à votre rythme, et toujours avec pédagogie.

Découvrez mes formations Qui suis-je ?

Chaque ligne associe :

• Un user_id
• Un exercice_id
• Et potentiellement une date ou un score

Les tables liées aux badges : badges et user_badges

Les badges sont un moyen de récompenser l’engagement et la réussite. Vous en gagnez quand vous atteignez un objectif : finir un module, réussir un quizz, commenter régulièrement, etc.

• La table badges contient tous les types de badges disponibles.
• La table user_badges fait le lien entre un utilisateur et les badges qu’il a débloqués.

Les quizz : quizz, quizz_questions, quizz_answers, quizz_results

Un quizz est une série de questions sur un thème précis (HTML, CSS, JS, etc.).

• quizz stocke les quizz généraux (titre, thème, difficulté).
• quizz_questions contient toutes les questions liées à un quizz.
• quizz_answers propose les différentes réponses à une question (avec un booléen pour indiquer si c’est la bonne).
• quizz_results enregistre les résultats d’un utilisateur sur un quizz donné (score, date, durée, etc.)

Ce système permet de construire des quizz dynamiques et de suivre les performances de chaque participant.

Les missions ou challenge : missions, mission_elements, mission_progress

Les missions sont comme des quêtes : elles rassemblent plusieurs exercices et/ou quizz dans un même objectif (par exemple : “Devenir un pro de HTML”).

• missions : contient le titre, la description et les conditions générales
• mission_elements : fait le lien entre une mission et ses éléments (quizz ou exercices)
• mission_progress : suit ce que l’utilisateur a validé ou non dans chaque mission

L’approche gamifiée renforce la motivation, en rendant l’apprentissage plus progressif et satisfaisant.

L’XP et l’historique d’actions : xp_history

Chaque action importante (finir un exercice, réussir un quizz, finir une mission…) vous rapporte des points d’expérience (XP). Ces XP permettent de monter en niveau et débloquer des fonctionnalités.

• La table xp_history enregistre toutes les sources de ces gains d’expérience.
• À terme, cela pourra permettre d’ajouter des statistiques, des classements ou des analyses personnalisées.

Les interactions : commentaires et likes

Pour faire de Créa-code une plateforme vivante, on permet aux utilisateurs de :

• Commenter les exercices
• Liker les commentaires des autres

Les tables concernées sont :

• commentaires, qui lie un texte à un utilisateur et à un exercice
• likes, qui relie un utilisateur à un commentaire

Cela favorise la communauté, les échanges, et la valorisation des utilisateurs actifs.

Ce qu’une structure permet

Grâce à cette architecture, Créa-code est déjà capable de :

• Gérer proprement les comptes utilisateurs
• Proposer des exercices et suivre leur réalisation
• Afficher des quizz, corriger automatiquement, enregistrer les scores
• Récompenser avec des badges
• Proposer des missions complexes, à débloquer
• Accumuler des XP et passer des niveaux
• Permettre à la communauté de s’exprimer (commentaires, likes)

Et tout cela de manière organisée et évolutive, sans que les données soient dupliquées ou mal liées. Chaque table a sa fonction, et grâce aux relations définies dès le départ, tout s’imbrique logiquement.

Vous l’avez peut-être remarqué : certaines fonctionnalités évoquées ne sont pas encore actives (comme les niveaux, ou les classements). C’est normal : cette version 1 de la base de données pose des bases solides.

Dans une version 2, je pourrai :

• Ajouter une table pour gérer les niveaux
• Ajouter une table pour les sessions d’apprentissage en direct (liées à Twitch)
• Ajouter une table pour les retours utilisateurs
• Intégrer des modules pour les statistiques et l’analyse comportementale
• Ajouter une description pour apparaitre sur le profil d’un utilisateur

Mais tout cela se fera sans casser l’existant, grâce à l’architecture bien pensée.

Première évolution de la table users

Lors du jour 08 de #100JoursPourCoder, nous avons défini les bases de la table users. Nous allons maintenant l’enrichir avec de nouvelles colonnes pour permettre à chaque membre de personnaliser son profil.

Pourquoi ces champs supplémentaires ?

La plateforme Créa-code se veut communautaire et ludique. Pour que les utilisateurs puissent s’identifier, se reconnaître, et s’approprier leur espace, il est important qu’ils puissent :

• renseigner leur prénom et leur nom,
• écrire une petite bio ou description personnelle,
• ajouter des liens vers leurs profils GitHub et Twitter,
• tout en conservant les éléments essentiels à la sécurité et à la progression (mot de passe, rôles, XP, etc.).

Nous allons donc ajouter à la table users les champs suivants :

• prenom (VARCHAR) : le prénom du membre,
• nom (VARCHAR) : son nom de famille,
• bio (TEXT) : une courte description libre,
• github_url (VARCHAR) : lien vers son GitHub,
• twitter_url (VARCHAR) : lien vers son Twitter.

Ces champs ne sont pas obligatoires. Cela permet à l’utilisateur de rester anonyme s’il le souhaite, tout en donnant la possibilité de se présenter s’il le désire.

Le nouveau code SQL de la table users enrichie

Voici le code SQL mis à jour, avec toutes les colonnes actuelles :

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    pseudo VARCHAR(100) NOT NULL UNIQUE,
    prenom VARCHAR(100) DEFAULT NULL,
    nom VARCHAR(100) DEFAULT NULL,
    niveau VARCHAR(30) NOT NULL,
    bio TEXT DEFAULT NULL,
    github_url VARCHAR(255) DEFAULT NULL,
    twitter_url VARCHAR(255) DEFAULT NULL,
    instagram_url VARCHAR(255) DEFAULT NULL,
    role ENUM('public', 'user', 'admin') NOT NULL DEFAULT 'user',
    avatar_id INT UNSIGNED DEFAULT NULL,
    xp INT UNSIGNED DEFAULT 0,
    date_inscription DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_login DATETIME DEFAULT NULL DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    status ENUM('actif', 'suspendu', 'supprimé') DEFAULT 'actif',
    verification_token VARCHAR(255) DEFAULT NULL,
    reset_token VARCHAR(255) DEFAULT NULL,
    reset_token_expiry DATETIME DEFAULT NULL,
    FOREIGN KEY (avatar_id) REFERENCES avatars(id)
);

Explication des nouveaux champs

Ces informations seront visibles sur les profils publics des membres si vous choisissez de les rendre accessibles. Cela encourage la création d’une vraie communauté d’apprentissage.

Et côté sécurité ?

Ces champs étant tous facultatifs, ils ne remettent pas en question la sécurité de l’espace membre. Il faudra simplement veiller à nettoyer les entrées (pour éviter les injections ou les scripts malveillants), et à valider les URLs pour s’assurer qu’elles pointent vers des liens valides.

En ajoutant simplement quelques champs à notre table users, nous venons de transformer un système de connexion basique en une véritable plateforme humaine, plus riche, plus engageante, et plus conviviale.

L’objectif de Créa-code est de motiver, d’encourager et de connecter les développeurs en herbe. Et cela passe aussi par la personnalisation de leur espace.

Créer une base de données est souvent vu comme une étape technique, abstraite, voire pénible. Mais c’est en réalité un moment passionnant : celui où l’on pense son projet en profondeur, avec méthode.

Dans cet article, je vous ai présenté la première version du schéma relationnel de Créa-code, et je vous ai expliqué, pas à pas, les raisons derrière chaque choix.
L’objectif n’est pas d’avoir une structure parfaite dès le départ, mais une architecture claire, logique, et qui peut évoluer.

Demain, nous continuerons à construire et à apprendre ensemble.

À demain pour le Jour 11 de #100JoursPourCoder !

Alban

Fondateur de l’agence Créa-troyes.
Intervenant en Freelance.
Contactez-moi

crea-troyes.fr