Modéliser les lots et les ventes d’une pharmacie
Une pharmacie de quartier à Niamey gère son stock dans un tableur. Chaque produit porte un nom commercial, mais il est acheté en plusieurs lots, chacun avec un numéro, une date de péremption et une quantité. Le tableur ne contient qu’une seule ligne par produit et une quantité globale : impossible de savoir quel lot vendre en premier ni quel lot arrive à péremption. La pharmacie a déjà vendu un lot périmé et a dû retirer un autre lot après une alerte du fournisseur. Le gérant veut une base de données pour appliquer le principe FEFO (premier périmé, premier sorti), alerter 30 jours avant péremption et tracer chaque vente. Il dispose d’un PC et de compétences limitées en SQL. Les fournisseurs livrent parfois plusieurs lots du même produit dans la même commande. Vous devez proposer un modèle conceptuel, une requête d’alerte et expliquer au gérant pourquoi la quantité globale dans la table Produit est une erreur de conception.
1. Proposez un modèle conceptuel (entités, associations, cardinalités) qui permette de gérer plusieurs lots par produit, les fournisseurs et les ventes. Justifiez la place de la quantité et de la date de péremption.
Read the worked answer
Entités principales : Produit (id_produit, nom, forme, prix_vente), Lot (id_lot, numero_lot, date_peremption, quantite_stock, id_produit, id_fournisseur), Fournisseur (id_fournisseur, nom), Vente (id_vente, date_vente, client éventuel) et LigneVente (id_vente, id_lot, quantite_vendue, prix_unitaire). Associations : un Produit peut avoir 0 à n Lots, chaque Lot concerne exactement 1 Produit ; un Fournisseur peut livrer 0 à n Lots, chaque Lot est rattaché à 0 ou 1 Fournisseur ; une Vente contient 1 à n Lignes, chaque Ligne concerne 1 Lot.
La quantité et la date de péremption sont placées dans Lot car elles varient d’un lot à l’autre : un même produit peut avoir un lot périmé et un lot récent. La quantité globale du produit n’est pas stockée dans Produit ; elle se calcule par somme des quantités des lots. Pour appliquer FEFO, on trie les lots disponibles par date_peremption croissante. Le numéro de lot du fournisseur ne remplace pas l’identifiant interne, car deux fournisseurs peuvent utiliser le même numéro.
2. Écrivez une requête SQL qui liste les produits et lots encore en stock dont la péremption tombe dans les 30 prochains jours, triés par date. Précisez les tables et le dialecte supposé.
Read the worked answer
Je suppose un dialecte SQL standard proche de PostgreSQL. Requête :
SELECT p.nom AS produit, l.numero_lot, l.date_peremption, l.quantite_stock
FROM Lot l
JOIN Produit p ON p.id_produit = l.id_produit
WHERE l.quantite_stock > 0
AND l.date_peremption >= CURRENT_DATE
AND l.date_peremption <= CURRENT_DATE + INTERVAL '30' DAY
ORDER BY l.date_peremption ASC, p.nom ASC;
La jointure est nécessaire pour afficher le nom du produit ; le filtre quantite_stock > 0 évite d’alerter sur un lot déjà épuisé ; la borne inférieure exclut les lots déjà périmés, qui relèvent d’une alerte distincte. En MySQL, on écrirait CURDATE() et DATE_ADD(CURDATE(), INTERVAL 30 DAY). Si l’on veut aussi voir les lots périmés, on supprime la borne inférieure et on ajoute un indicateur. Le tri par date croissante donne l’ordre FEFO. Il faut indexer date_peremption et id_produit pour que la requête reste rapide.
3. Expliquez au gérant, sans jargon excessif, pourquoi stocker la quantité globale dans la table Produit est une erreur de conception, et quel risque cela crée pour la vente FEFO.
Read the worked answer
Stocker la quantité globale dans Produit revient à recopier le total à côté des lots. Si l’on vend un lot, il faut mettre à jour à la fois la quantité du lot et le total du produit ; si l’on oublie l’un des deux, les chiffres divergent. On ne sait plus quel lot est concerné, donc on ne peut pas appliquer FEFO ni retirer un lot périmé. La quantité du produit est une donnée calculée à partir des lots : la stocker est redondant et viole la normalisation.
Le risque concret est de vendre un lot périmé parce que le total affiche encore du stock, ou de croire qu’un produit est disponible alors que seuls des lots périmés restent. Il faut supprimer la quantité globale de Produit et interroger la somme des lots disponibles. Le gérant peut voir le total à l’écran, mais ce total doit être calculé, pas saisi.