Gestion de stock

Bonjour Cyrano,

j'aurais besoin de quelques conseils pour la gestion de stock de mon projet (boutique).

Je comprends en gros le principe : Produits entrés - produits sortis = Stock

Mais je ne vois pas trop comment construire mes tables pour cela.

En gros ma base comprend déjà les tables Produits, Catégories, Commandes, Clients

Aurais-tu quelques conseils qui pourraient me guider un peu?

Merci d'avance pour tes conseils

Salut Paintbox,

Il faut découper le problème. D'abord identifier les données : de quoi est composé une entité « produit »:

1. Un libellé;
2. Un code ? (fabriquant, interne, autre..?) pouvant donner lieu à la génération des code-barre par exemple;
3. Un fournisseur;
4. Une marque ?
5. Un Modèle ? (avec un numéro de version ?)
6. Un tarif hors-taxe;
7. la TVA qui peut s'appliquer dessus ?;
8. Une quantité en stock;
9. Autres...?

Attention, certains des éléments mentionnés peuvent provenir d'autres tables.

Les clients, c'est autre chose quoique ce soit lié d'une certaine manière : si un client achète un produit donné, on doit retirer la quantité vendue du stock;

De même que lorsque tu reçois une lirvraison, tu vas mettre à jour le stock en augmentant la quantité enregistrée du nombre de produits reçus.

Mais fonctionnellement, essaye d'imaginer ton application : il y a deux parties : d'abord le site où le client navigue, remplit un panier, passe commende paye et s'en va : les retraits de stocks seront en grande partie effectués depuis le système gérant les commandes. L'autre partie, c'est le back-office de ton application, là où justement tu gères ton catalogue. il va te falloir une interface de gestion des livraisons de fournisseurs où, lorsqu'une livraison sera effectuée, tu mettras à jour le stock produit par produit. C'est aussi là que tu pourras retirer du stock indépendamment des ventes, par exemple suite à un sinistre : si par exemple tu vends des produits frais et qu'à cause d'une panne de frigo tu dois jeter un certain nombre des produits, ou encore tu organise la promotion d'un évènement et tu offres mettons 10 produits en prime : il faut pouvoir les retirer. Après, il faut voir quelles sont les obligations comptables auquelles tu es astreint pour vérifier si d'autres informations sont indispensables et intégrer ça dans le fonctionnel d'abord, dans ton modèle de données ensuite. Je dis ça parce que si tu fais des retrait de stock non vendu pour quelque raison que ce soit, il faut pouvoir le justifier auprès de certains organismes (assurances en cas d'incident, ou le fisc si tu fais un don et que tu ne veux pas être soupçonné de vente sous la table)

Pars sur ces bases, tu dervais arriver à un modèle de données pas très compliqué

Salut Cyrano,

merci pour tes explications.

Comme je te l'ai dit, mes tables Produits, Prix, Marques, Categories, Clients, Commandes, Détail de commande, Taux TVA, existent déjà et j'utilise des vues.

Ma partie Back-Office est déjà créée, où je peux ajouter un nouveau produit, une nouvelle catégorie Je peux retrouver les commandes en cours, celles finies

Je coince pour le stock. Je ne sais pas comment représenter les mouvements. Est ce que j'encode tous les mouvements de façon a conserver un "historique" de mes entrées et sorties? Dans ce cas, j'aurai les colonnes suivantes : id, date, Quantite_Entrées, Quantité_Sorties, Reference_du_produit_concerné, Descriptif_du_mouvement ?

Je suppose que dans ce cas, l'état du stock par articles se fera via une vue ?

Ou alors j'ai une table Entrées et une autre Sortie, j'additionne chacune et je soustrait les Sorties aux Entrées?

Le but est juste de connaître l'état du Stock et de pouvoir éventuellement faire des statistiques.

Bref, je n'y vois pas trop claire. Aurais-tu un lien vers un exemple?

Merci

Je n'ai pas d'exemple en tête. La question que ça m'inspire est la suivante : As-tu besoin d'avoir un historique des entrées/sorties de marchandise ?

Si oui, je serais tenté d'explorer l'idée d'une relation « Livraisons » entre « Fournisseur » et « Produit » et tu y ajoutes dans ce cas les propriétés « Quantité » et « Date_livraison ».

Partant de là, tu gères les entrées et avec une propriété « Stock » dans ton entité « Produit » tu peux avoir un état des lieux en tous temps. Ce que ça impliquerait au niveau de la base, ce sont deux triggers :

1. Un trigger lorsque tu enregistres la livraison d'un fournisseur qui, lorsque tu enregistreras la quantité dans la table « Livraisons » va ajouter cette quantité dans la colonne « Stock » de ta table produit;
2. Un trigger lorsque tu enregistres une commande de client (au moins lors de la confirmation) qui va retirer la quantité vendue dans la même colonne « Stock » de la table « Produit ».
3. En option un troisième qui va remettre la quantité retirée en cas d'annulation de commande.

En théorie c'est un champ calculé puisqu'on pourrait l'obtenir à partir des enregistrements de commandes et les enregistrements de livraisons de fournisseurs et on ne devrait pas le stocker en base, mais ce ne serait valable que pendant un temps. Avec les années, les lignes de données se multipliant, les calculs prendront de plus en plus de temps.

Dans ce cas, pas besoin d'une vue supplémentaire, il suffit d'interroger directement la table « Produit » pour savoir quelle quantité de tel ou tel article est disponible, et si tu utilises déjà une vue pour ton catalogue, il suffit de la modifier pour y ajouter cette valeur.

Il te faudra quand même une interface de gestion des stocks pour pouvoir ajuster les valeurs lorsque tu feras des inventaires, lorsque tu perdras de la marchandise en cas d'incident ou de vol voire lorsque tu en donneras pour une raison ou une autre. Mais cette interface ne modifiera que la quantité du produit sans toucher au reste.

Hello Cyrano,

merci pour tes explications. Si je comprends bien j'aurais d'une part ceci pour les entrées :

Articles Livraison Fournisseur

---|---|---|- ---|---|---|---| ---|---|---|---|---|-

Stock ID

Date_de_livraison

Ref_Article

Ref_Fournisseur

Quantité

Ma table livraison conserverait tout l'historique de mes entrées et les Triggers mettraient à jour le champ Stock de chacun des articles

et d'autre part pour les sorties :

Articles Commande Client

---|---|---|- ---|---|---|---|---|- ---|---|--

Stock

Je suis en train de me documenter sur les Triggers. J'avoue que ce n'est pas encore tres clair.

Merci

Les triggers, c'est pas très difficile.

En fait, ça consiste à déporter certains traitements automatisés directement dans la base de données au lieu de les traiter par programmation.

Un exemple « live » à partir de ce sur quoi je travaille actuellement : j'ai une table de facturation et j'y enregistre entre autres choses la date de création de la facture mais également la date de facturation effective. Si dans ma requête d'insertion mes dates ne sont pas fournies ou sont des chaines vides, le trigger vas les alimenter avec la date du jour. ça donne ceci :

DROP TRIGGER facturation_insert;
DELIMITER $$
CREATE TRIGGER facturation_insert
  BEFORE INSERT
  ON t_facturation_fct
  FOR EACH ROW
  BEGIN
    IF NEW.fct_date = '' THEN
      SET NEW.fct_date = LAST_DAY(NOW());
    END IF;
    IF NEW.fct_creation = '' THEN
      SET NEW.fct_creation = NOW();
    END IF;
  END$$
DELIMITER ;

Là, il n'y a qu'une seule table d'affectée. Maintenant, supposons que la valeur insérée dans une table doivent affecter la valeur d'une autre table, comme justement dans ton cas la quantité d'un produitlivré dans le stock de la table produit. ça donnerait quelque chose dans ce style :

DROP TRIGGER livraison_insert;
DELIMITER $$
CREATE TRIGGER livraison_insert
  AFTER INSERT
  ON r_fournisseur_livre_produit_liv
  FOR EACH ROW
  BEGIN
    IF NEW.liv_quantite > 0 THEN
      UPDATE t_produit_prd SET prd_stock = prd_stock + NEW.liv_quantite;
    END IF;
  END$$
DELIMITER ;

Explications :

• « livraison_insert » est le nom du trigger créé;
• « r_fournisseur_livre_produit_liv » est le nom de la table relationnelle où tu enregistres les livraisons;
• « t_produit_prd » est le nom de la table des produits;
• « prd_stock » et le nom de la colonne où est enregistré le stock dans la table des produits;
• « NEW.liv_quantité » : liv_quantité est la colonne où est enregistrée la quantité livrée dans la table des livraisons et NEW capture la valeur de la ligne envoyée dans le requête d'insertion.

La première ligne supprime le triggerau cas où il existerait déjà;

La ligne suivante modifie le délimiteur de requête : ça permet d'utiliser le « ; » dans les requêtes contenues dans le trigger sans les exécuter lors de l'enregistrement du trigger

Ensuite, CREATE TRIGGER nom-du-trigger quant (AFTER ou BEFORE) type-de-requête(INSERT, UPDATE ou DELETE) ON nom-de-la-table-qui-reçoit-la-requete

On ajoute FOREACH ROW pour le cas où ça affecte plusieurs lignes

Ensuite un bloc BEGIN/END et dedans les traitements à effectuer.

Et là, j'ai mis une condition : par exemple on vérifie que le nombre de produits livrés par le fournisseur est bien supérieur à zéro. Attention avec la syntaxe du IF : ce n'est pas du PHP, c'est du SQL, donc on fait IF condition THEN suivi de la commande à exécuter et on termine avec un END IF;

Et le traitement lui-même , on effectue une requête UPDATE sur la table produit en ajoutant la quantité livrée (NEW.liv_quantite) à la colonne prd_stock de la table des produits.

Voilà,plonge toi dans la doc de MySQL sur les triggers, c'est assez simple à mettre en oeuvre et tu ne devrais pas avoir de difficultés majeures.

Merci pour tes explications je comprends un peu mieux. Je suis en train de faire des tests.

Comment indiquer dans le trigger la ligne (correspondant à l'article) qui doit être mis à jour?

J'ai essayé en faisant ceci mais ça ne donne rien :

UPDATE t_produit_pro SET pro_stock=pro_stock+NEW.liv_quantite WHERE pro_reference=liv_ref_article;

Et comment aussi indiquer que le TRIGGER doit s'exécuter pour un INSERT, un UPDATE ?

Salut,

ta question m'a fait réaliser que j'ai fait une erreur dans l'exemple, la requête UPDATE que j'ai mise n'a pas de clause WHERE, ce qui pourrait être catastrophique puisque ça mettrait à jour toutes les lignes.

Bien, ceci dit, relis les précédentes explications observe bien un détail : « AFTER INSERT » indique que la commande doit être exécutée après l'insertion de la nouvelle ligne.

Lorsque tu enregistres une livraison, comme la table est relationnelle, tu dois fournir dans ta requête les identifiant du fournisseur d'une part et du produit d'autre part. Donc ta requête est bonne mais il manque l'élément permettant de capturer la bonne valeur pour ta clause WHERE et doit donc être corrigée comme suit :

UPDATE t_produit_pro SET pro_stock=pro_stock+NEW.liv_quantite WHERE pro_reference=NEW.liv_ref_article;

Éventuellement, affiche-moi ici le code complet de ton trigger au cas où ça ne fonctionnerait toujours pas.

Il me fait la mise à jour du champs Stock mais cela pour tous mes produits alors que je précise de rechercher l'article concerné.

Voici le code de mon Trigger , y-a-t-il une erreur?

DELIMITER $$CREATE TRIGGER livraison_insertAFTER INSERTON t_livraison_livFOR EACH ROWBEGINIF NEW.liv_quantite >0 THENUPDATE t_produit_pro SET pro_stock=pro_stock+NEW.liv_quantite WHERE pro_reference=NEW.liv_ref_article;END IF;END $$DELIMITER;

Je ne vois toujours pas comment exécuter ce Trigger sur differents types d'actions (INSERT, UPDATE). J'ai essaye en mettant AFTER INSERT, UPDATE ou AFTER INSERT OR UPDATE mais ça ne fonctionne pas.

Il ne faut quand même pas créer un Trigger par type d'action ?

Tu dois créer un trigger par type de requête, donc un pour les requêtes INSERT, un autre pour les requêtes UPDATE.

L'exécution est ensuite automatique : lorsque tu insèreras ou mettras à jour une ligne de la table des livraisons, le trigger sera exécuté par la base de données sans que tu aies quoi que ce soit à coder en plus.

Un détail : attention avec le trigger sur un UPDATE : là tu ne devras pas ajouter la quantité au stock mais retirer ou ajouter la différence avec la précédente valeur : donc réfléchis bien au problème. Dois-tu mettre son déclenchement avant ou après la requête de mise à jour ? Pense à récupérer l'ancienne valeur de livraison... donc... à toi la main ;)

Bon, ça coince j'ai lu la théorie et je ne vois pas où je me trompe.

j'essaie de faire un update comme ceci mais il me dit "Unknown column 'pro_stock' in 'OLD'"

et mon code est le suivant :

DROP TRIGGER livraison_insert;DELIMITER $$CREATE TRIGGER livraison_insertBEFORE UPDATEON t_livraison_livFOR EACH ROWBEGINIF NEW.liv_quantite >0 THENUPDATE t_produit_pro SET pro_stock=OLD.pro_stock-NEW.liv_quantite WHERE pro_reference=NEW.liv_ref_article;END IF;END $$DELIMITER;

La colonne pro_stock existe bien et j'essaie de déduire New.liv_quantite à OLD.pro_stock

Normal, petite explication :

NEW et OLD permettent de capturer les valeurs des colonnes dans la table qui reçoit la requête, donc ici ta table de livraisons. MySQL cherche donc une colonne pro_stock dans ta table de livraison, colonne qui n'existe pas.

Comme ta requête UPDATE exécutée par le trigger concerne la table des produits, tu ne dois utiliser OLD et NEW que pour pointer sur les valeurs de la table de livraison.

Tout ça pour dire que tu dois virer le OLD devant pro_stock, ton trigger devient donc :

DROP TRIGGER livraison_insert;
DELIMITER $$
CREATE TRIGGER livraison_insert
BEFORE UPDATE
ON t_livraison_liv
FOR EACH ROW
BEGIN
  IF NEW.liv_quantite >0 THEN
    UPDATE t_produit_pro
    SET pro_stock = pro_stock - NEW.liv_quantite
    WHERE pro_reference = NEW.liv_ref_article;
  END IF;
END $$
DELIMITER;

Bon, ceci dit, sur un UPDATE dans la table livraison, tu vas avoir un résultat faux dans ta table des produits. En effet, lorsque tu as fait la première insertion, le stock a été mis à jour avec un + nouvelle-valeur-insérée-dans-les-livraisons. Supposons que tu aies reçu 15 articles, le trigger a donc normalement fait +15 dans la colonne pro_stock pour l'article correspondant : si tu en avais encore 2, ton stock est à présent de 2 + 15 soit 17 articles.

Et puis tu t'es rendu compte que tu t'es trompé, ce n'était pas 15 mais 10. Avec ton trigger tel que tu me le montres, ça va faire17 - 10 et ton stock affichera 7 articles alors qu'en réalité, tu en as 17 - 15 + 10, donc 12.

Il manque donc dans ton trigger la récupération de la précédente livraison erronnée et la récupération : là, ton trigger reprend du sens mais tu t'es juste trompé de colonne, regarde ce que ça donnerait :

DROP TRIGGER livraison_insert;
DELIMITER $$
CREATE TRIGGER livraison_insert
BEFORE UPDATE
ON t_livraison_liv
FOR EACH ROW
BEGIN
  IF NEW.liv_quantite >0 THEN
    UPDATE t_produit_pro
    SET pro_stock = pro_stock - OLD.liv_quantite + NEW.liv_quantite
    WHERE pro_reference = NEW.liv_ref_article;
  END IF;
END $$
DELIMITER;

Essaye donc ça ;)

Cela fonctionne effectivement pour ma table livraison. J'ai entre temps fais un trigger sur ma table commande qui déduit donc la quantité commandée au stock existant. J'ai quand meme encore quelques questions.

1° Dans le cas d'une commande je peux avoir le cas ou la commande passée est plus importante que le stock existant, je ne peux donc pas me retrouver avec un stock négatif. Je dois donc mettre un truc du style IF commande > que Stock alors quoi ? ET peut-on comme en PHP mettre 2 IF (ou plus) l'un dans l'autre ?

2° Si je comprend bien, on ne peut avoir qu'un seul trigger par table ?

3° Dans le cas de mon Back-Office je peux être amené a devoir modifier manuellement mon stock ou ma table livraison ou commande. Comment faire dans ce cas? J'affiche mon stock sous forme de tableau avec des champs input contenant la valeur du stock pour chaque produit et je fais une fonction qui met à jour si je modifie un champs stock d'un produit?

4° Bête question peut être mais où est stocké mon trigger dans Mysql ? Si dans x mois je ne me souviens plus du Trigger que j'ai appliqué comment puis-je le consulter, le retrouver?

Salut,

on va voir ça point par point :

1. Si tu as une commande plus importante que le stock existant, il faut valider la commande et donc constater que la quantité disponible est insuffisante, et donc bloquer ladite commande. Tu gères ça au niveau de ton panier. Lorsque tu affiches le formulaire de gestion de panier du client, tu pourrais avoir en champ caché les quantités disponibles pour les articles sélectionnés, partant de là, lors de la validation, tu as un élément te permettant sans difficulté de dire au client « *Je n'en ai pas assez, la commande auprès du fournisseur est en cours, etc... *» : donc tu ne valides de commande que pour des quantités valides. Ou alors il existe une autre option : tu ne modifies le stock qu'à partir du moment où tu indiques que la commande est expédiée. Ça voudrait dire que ta table où sont enregistrées les commandes doit comporter une ou plusieurs colonnes concernant l'expédition (par exemple, date_expedition, id_transporteur, code_barre chronopost, etc... ) avec éventuellement un lien vers d'autres tables sur les entreprises de transport.
2. Tu peux avoir le nombre de trigger que tu veux par table, de 0 à n, quoique le « n » ici sera relativement limité, mais ça veut dire un seul ou plusieurs ou aucun selon le besoin. En revanche, tu ne peux avoir un unique trigger à la fois pour un INSERT et pour un UPDATE ni pour aucune combinaison de type de requête.
3. La mise à jour de ton stock peut faire suite par exemple à un inventaire. La base t'indique qu'il reste 12 articles d'un produit donné mais l'inventaire en recense 14, dans ce cas, tu modifies directement la valeur du stock et les triggers des autres tables n'interviennent pas puisque leurs tables respectives ne sont pas affectées ni même touchées.
4. Tu peux voir la liste des triggers avec un simple « SHOW TRIGGERS; " ou « SHOW TRIGGERS\G » : selon que tu termines la commande avec un « ; » ou un « \G », l'affichage sera différent, essaye dans le client en ligne de commande de MySQL, ce sera plus parlant. Ensuite, pour un trigger donné, tu peux voir ce qu'il contient avec un « SHOW CREATE TRIGGER nom-du-trigger\G » et ça t'affichera le code qui a servi à le créer.

Ok merci pour toutes ces explications j'y vois un peu plus clair. Pour l'instant je n'ai fait que des tests. Je vais à présent adapter cela à mon projet. Je pense que ça devrait aller.

Et j'ai encore appris quelque chose de nouveau (les Triggers).

Encore merci pour tes explications et ton aide !

A+

Hello Cyrano,

j'ai un autre probleme sur lequel je voudrais avoir ton avis.

J'ai un stock général. Mais il arrive que des vendeurs empruntent des produits pour les vendre. Même s'ils sont sorti physiquement du stock, ils en font partie tant qu'ils ne sont pas vendu. Donc en gros, mon stock général est constitué du stock "interne" + le stock détenu par chacun des vendeurs.

On doit savoir quel vendeur a quel produit en sa possession et en quelle quantité.

Si j'ai 100 exemplaires d'un même article constitué comme suit: 90 en interne + 10 chez vendeur A. Si le vendeur A vend ses 10 articles, mon stock général devient 90 et le stock du vendeur A pour ce produit devient 0.

Et donc sur la page Stock de mon Back-Office je voudrais retrouver le fait que pour ce produit le stock interne est à 90 et que le stock du vendeur A est à 0.

Je n'arrive pas à représenter cela.

Comment ferais-tu?

J espere que ces clair?

Mouais, je vois à peu près, et je suppose que ce ne sont pas des articles de démonstration, s'ils sont vendus, ils le sont au même titre que les autres.

À première vue, pour ne pas dire « à l'arrache », je serais tenté de mettre une relationnelle entre produits et vendeurs, avec dans cette relationnelle un stock externe : s'il vend un article, on le décompte de son propre stock.

Par exemple, on pourrait avoir des stocks séparés : dans ta table des produits le stock effectif que tu as en magasin, dans la relationnelle entre produit et vendeur le stock dont dispose le vendeur et pour avoir le stock total, tu pourrais effectuer un total du tout. Ce serait peut-être le moins complexe à mettre en place. Le stock de chaque vendeur n'étant pas compté dans le stock de la table produit ne serait pas pour autant comptabilisé comme vendu et lorsqu'un vendeur effectue la vente d'un de ses articles, ça n'affectera pas la table des produits. J'ajoute que pour des besoins statistiques, ça permettra de savoir où en sont les stocks des différents endroits incluant au niveau individuel des vendeurs. Et lorsque tu confies n articles à un vendeur, tu enlève ce nombre n de la table des produits et tu l'additionnes dans la relationnelle.

C'est à peu près ce que j'avais aussi imaginé.

Mais dans ma relation produit - Vendeurs, mon nombre de vendeurs peu évoluer, idem pour les produits. C'est ce que j'ai du mal à visualiser.

Je pourrais avoir dans cette relation les champs suivants:

id

ref_produit

ref_vendeur

Produit_En_Stock

Produit_vendu

et donc pour savoir quel quantité du produit X le vendeur A détient, je ferais total des Produit_En_Stock pour le produit X - total Produit_vendu du produit X pour le vendeur A?

De cette manière je sais quel vendeur à quelle quantité de chaque produit.

Dans ma table produit j'aurais ma colonne stock effectif et mon total général par article je le calculerais dans une vue (Total stock effectif - total Stock Tous vendeur du produit X) .

Ca me parrait quand même un peu lourd.

Tu crois que ça tiendrait la route ?

Tu peux virer les colonnes id et Produit_vendu : l'identifiant est une cléprimaire composite constituée de la référence du produit et de la référence du vendeur qui sont en même temps chacune des clé étrangères. Ensuite, les ventes, tu ne les enregsitres pas là, ou alors c'est parce que tu voudrais enregistrer ça à des fins statistiques seulement et effectivement lorsqu'un vendeur effectue la vente du produit en question, tu incrémentes la vente et décrémentes le stock.

Mais attention à un détail : j'expliquais précédemment que la valeur de stock de la table produit ne devrait pas compter le stock « itinérant » des vendeurs, donc ta vue devrais faire un produit.stock + SUM(vendeur_has_produit.stock) WHERE produit.prod_id = xyz , tu saisis ?

Je ne trouve pas ça si lourd que ça, et à mon sens ça tient bien la route.

Ok je vais tenter cela.

Merci pour tes conseils.