Créer une base de données SQL complète pour gérer une bibliothèque (cas concret expliqué pas à pas)

Vous débutez en SQL et vous êtes fatigué des exemples abstraits qui vous montrent comment compter des pommes ou trier des prénoms ? Vous avez envie d’un vrai projet, concret, utile, stimulant, avec un vrai contexte du monde réel ? Alors vous êtes au bon endroit.

Dans ce tutoriel, je vous propose de créer une base de données complète pour gérer une bibliothèque, comme si vous étiez chargé(e) de digitaliser le fonctionnement d’une bibliothèque municipale ou scolaire.

Imaginez : la mairie vous confie la mission de concevoir une solution simple pour :

• Enregistrer les livres disponibles,
• Gérer les inscriptions des membres,
• Suivre les emprunts et retours,
• Et pourquoi pas, automatiser des rapports et statistiques par la suite.

En voici un projet qui pourrait aborder la plus part des cas pratiques en SQL. Ce projet vous permettra d’explorer :

• La création d’une base avec MySQL
• Les relations entre tables (clé primaire, clé étrangère)
• Les requêtes essentielles (SELECT, JOIN, INSERT, etc.)
• Les bonnes pratiques de modélisation

Et surtout, tout est expliqué pas à pas, avec des phrases claires, des justifications techniques, et des alternatives si vous voulez aller plus loin ou adapter le projet à votre cas.

Étape 1 — Préparer son environnement de travail

Avant même de créer notre base de données, il faut d’abord mettre en place un environnement de travail stable et accessible. Voici ce que je recommande (et pourquoi).

1. MySQL ou MariaDB

Ce sont les moteurs de base de données relationnelle les plus répandus. Personnellement, j’utilise MariaDB (version libre de MySQL), car il est 100 % open source, léger et rapide.

2. phpMyAdmin (ou Adminer)

Pour ceux qui préfèrent travailler avec une interface visuelle plutôt que le terminal, phpMyAdmin est l’outil idéal. Il permet d’exécuter des requêtes SQL, de voir les tables, d’ajouter des enregistrements à la main, etc.

3. Serveur local (WAMP, XAMPP, Laragon)

Vous pouvez installer un environnement tout-en-un qui comprend :

• Apache (serveur web)
• MySQL/MariaDB
• PHP
• phpMyAdmin

Alternative recommandée : DBeaver si vous aimez les IDE plus professionnels, ou HeidiSQL sous Windows. Perso, j’utilise Wampserver et tout au long du tuto c’est cet outils que j’utiliserai.

Étape 2 – Analyse et modélisation des besoins

Avant d’écrire la moindre ligne de code SQL, il faut penser comme un analyste ou un concepteur de base de données. L’erreur la plus courante chez les débutants, c’est de se lancer tête baissée dans la création de tables sans avoir pris le temps de réfléchir à ce que le système doit réellement faire.

Dans notre cas, on imagine un scénario simple et réaliste :

🔎 Vous êtes mandaté(e) par une petite bibliothèque municipale (ou scolaire) qui veut informatiser la gestion de ses livres et des emprunts. Jusqu’ici, tout se faisait sur papier ou dans un fichier Excel bancal…

Vous posez donc les bonnes questions au client (ou à vous-même) :

Quels sont les éléments concrets à gérer ?

• Les livres disponibles dans la bibliothèque.
• Les membres inscrits (qui peuvent emprunter les livres).
• Les emprunts effectués : qui a emprunté quoi, quand, et est-ce que le livre a été rendu ?

On distingue ici trois entités principales : livres, membres, emprunts.

Modélisation logique — Quelles données pour chaque entité ?

Entité Livre: Chaque livre doit contenir au minimum

• Un identifiant unique (id_livre)
• Un titre
• Un auteur
• Une année de publication
• Un genre (roman, essai, science-fiction…)

Pourquoi pas d’exemplaires physiques ?
Pour ce premier modèle simple, on suppose qu’il y a un seul exemplaire par titre. C’est un choix de simplification.
Alternative avancée : On pourrait ajouter une table exemplaires si on veut gérer plusieurs copies d’un même livre (utile pour les grandes bibliothèques).

Entité Membre: Chaque membre doit avoir

• Un identifiant (id_membre)
• Un nom et un prénom
• Un email (qui servira de contact unique)
• Une date d’inscription

Bonnes pratiques :

• Ne jamais stocker de mot de passe en clair dans une base. Ici on ne gère pas l’authentification, donc inutile.
• L’email peut être marqué UNIQUE pour éviter les doublons.

Entité Emprunt

L’emprunt est une action réalisée entre deux entités : un membre emprunte un livre.

On aura donc besoin :

• D’un identifiant (id_emprunt)
• Du livre emprunté (id_livre)
• Du membre qui emprunte (id_membre)
• D’une date d’emprunt
• Et d’une éventuelle date de retour

C’est ce qu’on appelle une table de liaison avec informations supplémentaires. Ce n’est pas juste un lien entre deux entités, c’est une table à part entière.

Note importante :
On pourrait interdire qu’un même livre soit emprunté deux fois en même temps. Cela peut être fait :

• Côté logique applicative (dans le code PHP/Python)
• Ou via une contrainte plus complexe dans SQL (hors débutant)

Les relations entre les tables (modèle relationnel)

On peut maintenant dessiner un mini-MCD (Modèle Conceptuel de Données) mental :

• Un livre peut être emprunté plusieurs fois (relation 1:N)
• Un membre peut emprunter plusieurs livres (relation 1:N)
• Chaque emprunt lie un seul membre à un seul livre

On a donc deux relations 1:N, et une table pivot : emprunts.

Étape 3 — Création de la base de données (suite complète et pédagogique)

Maintenant que notre modèle est bien défini, nous allons traduire notre logique en langage SQL. L’objectif est d’implémenter ce qu’on a réfléchi de façon propre, claire et évolutive.

1️⃣ Création de la base de données

CREATE DATABASE bibliotheque
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;

Pourquoi utf8mb4 ? Parce qu’il prend en charge les accents et les caractères spéciaux (ex: emojis). C’est la meilleure pratique actuelle.

Puis on active la base :

USE bibliotheque;

2️⃣ Création de la table livres

CREATE TABLE livres (
    id_livre INT AUTO_INCREMENT PRIMARY KEY,
    titre VARCHAR(255) NOT NULL,
    auteur VARCHAR(255),
    annee_publication INT CHECK (annee_publication BETWEEN 1500 AND 2025),
    genre VARCHAR(100)
);

Le champ titre est requis. Le champ annee_publication est limité pour éviter les erreurs (livre de l’an 3000 ? non merci). Le champ genre permet de filtrer ou regrouper plus tard.

3️⃣ Création de la table membres

CREATE TABLE membres (
    id_membre INT AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(100) NOT NULL,
    prenom VARCHAR(100),
    email VARCHAR(150) UNIQUE,
    date_inscription DATE DEFAULT CURRENT_DATE
);

email UNIQUE pour éviter les doublons. DEFAULT CURRENT_DATE automatise la date d’inscription.Aucun champ sensible (mot de passe, numéro de téléphone…) n’est ici car ce n’est pas notre objectif pour le moment. Nous verrons des aspects beaucoup plus complexe au niveau de la sécurité dans d’autres tuto.

4️⃣ Création de la table emprunts

CREATE TABLE emprunts (
    id_emprunt INT AUTO_INCREMENT PRIMARY KEY,
    id_livre INT NOT NULL,
    id_membre INT NOT NULL,
    date_emprunt DATE NOT NULL,
    date_retour DATE,
    FOREIGN KEY (id_livre) REFERENCES livres(id_livre) ON DELETE CASCADE,
    FOREIGN KEY (id_membre) REFERENCES membres(id_membre) ON DELETE CASCADE
);

Cette table joue un rôle pivot entre membres et livres. Les FOREIGN KEY assurent la cohérence entre les tables. Le ON DELETE CASCADE signifie que si un membre ou un livre est supprimé, ses emprunts le sont aussi automatiquement (pratique pour éviter des références mortes)

Étape 4 — Insertion de données dans la base de données

L’objectif de cette partie est d’ Ajouter des enregistrements réalistes dans chaque table de notre BDD qu’on vient de créer. Maintenant que toutes nos tables sont fonctionnelles, nous allons ajouter des données et voir le comportement. Nous allons Utiliser INSERT INTO correctement, qui est la commande pour inserer des données dans une table. Puis nous allons tester la cohérence des relations entre les tables.

1. Insertion de livres dans la table livres

Commençons par insérer quelques livres bien connus, représentatifs de genres variés.

INSERT INTO livres (titre, auteur, annee_publication, genre) VALUES
('1984', 'George Orwell', 1949, 'Science-fiction'),
('Le Petit Prince', 'Antoine de Saint-Exupéry', 1943, 'Conte philosophique'),
('L’Étranger', 'Albert Camus', 1942, 'Roman'),
('Sapiens', 'Yuval Noah Harari', 2011, 'Essai'),
('Harry Potter à l\'école des sorciers', 'J.K. Rowling', 1997, 'Fantasy');

Attention aux apostrophes dans les titres ! Utilisez \' pour éviter une erreur de syntaxe SQL.

2. Insertion de membres dans la table membres

Nous ajoutons des lecteurs fictifs mais réalistes.

INSERT INTO membres (nom, prenom, email) VALUES
('Durand', 'Julie', '[email protected]'),
('Martin', 'Léo', '[email protected]'),
('Nguyen', 'Minh', '[email protected]'),
('Delvaux', 'Claire', '[email protected]');

La date d’inscription est automatique (DEFAULT CURRENT_DATE). L’email est unique. Si vous tentez d’insérer deux fois le même, MySQL vous renverra une erreur.

3. Insertion d’emprunts dans la table emprunts

Maintenant que nous avons des livres et des membres, on peut enregistrer les emprunts. N’oubliez pas que les IDs doivent correspondre à des valeurs existantes dans les autres tables.

INSERT INTO emprunts (id_livre, id_membre, date_emprunt, date_retour) VALUES
(1, 1, '2025-04-02', '2025-04-10'),
(2, 2, '2025-04-03', NULL),
(3, 3, '2025-04-05', NULL),
(4, 1, '2025-04-06', '2025-04-20');

Détails importants :

• NULL dans date_retour signifie que le livre n’a pas encore été rendu.
• Si vous entrez un id_livre ou id_membre inexistant, MySQL refusera l’insertion à cause de la contrainte FOREIGN KEY.

Erreurs courantes et comment les éviter

Vérification : afficher les données insérées

Pour afficher les livres disponibles:

SELECT * FROM livres;

Pour afficher les membres

SELECT id_membre, CONCAT(prenom, ' ', nom) AS nom_complet, email FROM membre

Pour afficher les emprunts

SELECT
  e.id_emprunt,
  l.titre AS livre,
  CONCAT(m.prenom, ' ', m.nom) AS emprunteur,
  e.date_emprunt,
  e.date_retour
FROM emprunts e
JOIN livres l ON e.id_livre = l.id_livre
JOIN membres m ON e.id_membre = m.id_membre;

Étape 5 – Requêtes SQL utiles pour exploiter les données

Une base de données, ce n’est pas juste une boîte de stockage : c’est surtout un outil d’analyse. Grâce aux requêtes SQL, on peut répondre à des questions concrètes comme :

• Quels sont les livres actuellement empruntés ?
• Qui a emprunté le plus récemment ?
• Combien de livres un membre a-t-il déjà empruntés ?
• Quels livres n’ont jamais été empruntés ?

Dans cette section, nous allons voir plusieurs requêtes concrètes, classées par type d’usage.

Afficher les livres disponibles (non empruntés ou déjà rendus)

SELECT * FROM livres
WHERE id_livre NOT IN (
  SELECT id_livre FROM emprunts WHERE date_retour IS NULL
);

La sous-requête cherche les livres actuellement empruntés (date_retour IS NULL). Le NOT IN exclut ces livres de la liste affichée. Cette requête est très utile dans une interface utilisateur pour filtrer les livres “empruntables”.

Liste des emprunts en cours

SELECT
  e.id_emprunt,
  l.titre AS livre,
  CONCAT(m.prenom, ' ', m.nom) AS emprunteur,
  e.date_emprunt
FROM emprunts e
JOIN livres l ON e.id_livre = l.id_livre
JOIN membres m ON e.id_membre = m.id_membre
WHERE e.date_retour IS NULL
ORDER BY e.date_emprunt DESC;

Cette requête montre les livres empruntés mais pas encore rendus, triés par date décroissante (du plus récent au plus ancien).

Emprunts d’un membre en particulier

SELECT
  l.titre,
  e.date_emprunt,
  e.date_retour
FROM emprunts e
JOIN livres l ON e.id_livre = l.id_livre
WHERE e.id_membre = 1;

Il suffit de remplacer 1 par l’ID du membre concerné.

Nombre total d’emprunts par membre

SELECT 
  m.id_membre,
  CONCAT(m.prenom, ' ', m.nom) AS nom,
  COUNT(e.id_emprunt) AS nb_emprunts
FROM membres m
LEFT JOIN emprunts e ON m.id_membre = e.id_membre
GROUP BY m.id_membre
ORDER BY nb_emprunts DESC;

Cette requête permet de savoir combien de fois chaque membre a emprunté un livre, utile pour des statistiques ou des rapports.

Livres jamais empruntés

SELECT * FROM livres
WHERE id_livre NOT IN (
  SELECT DISTINCT id_livre FROM emprunts
);

Cela permet d’identifier les ouvrages ignorés des lecteurs – utile pour repenser les collections ou organiser des mises en avant !

Dernier emprunt effectué

SELECT
  l.titre,
  CONCAT(m.prenom, ' ', m.nom) AS emprunteur,
  e.date_emprunt
FROM emprunts e
JOIN livres l ON e.id_livre = l.id_livre
JOIN membres m ON e.id_membre = m.id_membre
ORDER BY e.date_emprunt DESC
LIMIT 1;

Affiche le dernier livre emprunté, par qui, et à quelle date. Parfait pour une section “activités récentes”.

BONUS – Vue SQL des emprunts en cours

Créer une vue permet de simplifier l’usage de requêtes complexes dans votre code PHP ou vos outils de reporting :

CREATE VIEW emprunts_en_cours AS
SELECT
  e.id_emprunt,
  l.titre AS livre,
  CONCAT(m.prenom, ' ', m.nom) AS emprunteur,
  e.date_emprunt
FROM emprunts e
JOIN livres l ON e.id_livre = l.id_livre
JOIN membres m ON e.id_membre = m.id_membre
WHERE e.date_retour IS NULL;

Ensuite, vous pouvez simplement faire :

SELECT * FROM emprunts_en_cours;

Cela permet de ne pas répéter la même requête complexe à plusieurs endroits.

Étape 6 – Extensions possibles pour aller plus loin

Une base de données bien conçue, c’est bien. Mais une base évolutive, c’est mieux. À ce stade, vous avez un projet solide, fonctionnel et compréhensible. Maintenant, je vous propose quelques améliorations qui vous permettront :

• De vous entraîner davantage,
• De répondre à des cas d’usage plus réalistes,
• De renforcer vos compétences en SQL et conception.

Par exemple, gérer plusieurs exemplaires d’un même livre

Dans la vraie vie, une bibliothèque possède plusieurs copies d’un même titre. Notre modèle initial ne le permet pas.

Solution : ajouter une table exemplaires

CREATE TABLE exemplaires (
    id_exemplaire INT AUTO_INCREMENT PRIMARY KEY,
    id_livre INT NOT NULL,
    etat VARCHAR(50),
    disponible BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (id_livre) REFERENCES livres(id_livre) ON DELETE CASCADE
);

Ensuite, la table emprunts fera référence à id_exemplaire plutôt qu’à id_livre.

ALTER TABLE emprunts
ADD id_exemplaire INT,
ADD FOREIGN KEY (id_exemplaire) REFERENCES exemplaires(id_exemplaire);

Cela permet :

• D’indiquer quel exemplaire précis a été emprunté,
• De gérer l’état de chaque exemplaire,
• De bloquer les emprunts pour les exemplaires non disponibles.

Un autre exemple, une situation ou il faudra détection des retards

Vous pouvez ajouter une règle de prêt : un livre est censé être rendu après 15 jours.

Voici une requête pour détecter les emprunts en retard :

SELECT
  e.id_emprunt,
  l.titre,
  m.nom,
  e.date_emprunt,
  DATEDIFF(CURDATE(), e.date_emprunt) AS jours_depuis_emprunt
FROM emprunts e
JOIN livres l ON e.id_livre = l.id_livre
JOIN membres m ON e.id_membre = m.id_membre
WHERE e.date_retour IS NULL AND DATEDIFF(CURDATE(), e.date_emprunt) > 15;

Cette requête va:

• Affiche les emprunts en cours depuis plus de 15 jours
• Peut être utilisée pour générer des rappels automatiques via une interface web ou un script

Il pourrait y avoir plein d’autres d’exemples d’améliorations pour rendre votre projet beaucoup plus robuste. Comme par exemple: Ajouter des commentaires ou remarques à un emprunt, Créer une interface utilisateur, Créer des scénarios de test ou encore Gérer les comptes utilisateurs. Ici nous nous sommes limités à la base, afin de déjà mieux comprendre tous les aspects.

En bonus, voici ce qui pourrait être le schema physique de notre BDD après quelques améliorations:

Ce projet de gestion de bibliothèque vous a permis de découvrir l’ensemble du cycle de création d’une base de données relationnelle, depuis l’analyse des besoins jusqu’aux requêtes d’exploitation.

En partant d’un contexte réel et concret, vous avez :

• Modélisé les entités essentielles d’un système de prêt de livres 📚
• Mis en œuvre des relations logiques et cohérentes entre les tables
• Manipulé les données via des requêtes SQL puissantes et réalistes
• Et ouvert la porte à des fonctionnalités avancées, prêtes pour une intégration web ou logicielle

Ce projet n’est pas qu’un simple exercice : c’est une base solide, réutilisable et extensible, qui vous servira dans d’autres contextes (médiathèque, vidéothèque, système de réservation, etc.).

N’hésites pas aussi à consulte les autres articles du blog et à t’abonner à la NewsLetter pour ne rien rater. A bientôt !