Bonsoir mrfof,
Je n’ai ps tout saisi de votre demande...
D’instinct, je dirais que si le stock est géré par paire {article, unité}, on peut définir une association ARTICLE_UNITE entre les entités-types ARTICLE et UNITE, porteuse de la quantité en stock par article et unité.
Pour brancher les achats et les ventes, on transforme cette association en entité-type associative, donc identifiée relativement à ARTICLE et UNITE (cardinalités 1,1(R)).
Une ébauche :
MCD
MLD
Les CREATE TABLE :
CREATE TABLE UNITE
(
uniteId INT NOT NULL
, uniteLibelle VARCHAR(16) NOT NULL
, uniteQte INT NOT NULL
, CONSTRAINT UNITE_PK PRIMARY KEY (uniteId)
) ;
CREATE TABLE ARTICLE
(
articleId INT NOT NULL
, reference VARCHAR(16) NOT NULL
, statut VARCHAR(8) NOT NULL
, CONSTRAINT ARTICLE_PK PRIMARY KEY (articleId)
) ;
CREATE TABLE ARTICLE_UNITE
(
articleId INT NOT NULL
, uniteId INT NOT NULL
, quantiteStock INT NOT NULL
, CONSTRAINT ARTICLE_UNITE_PK PRIMARY KEY (articleId, uniteId)
, CONSTRAINT ARTICLE_UNITE_ARTICLE_FK FOREIGN KEY (articleId)
REFERENCES ARTICLE (articleId)
, CONSTRAINT ARTICLE_UNITE_UNITE_FK FOREIGN KEY (uniteId)
REFERENCES UNITE (uniteId)
) ;
CREATE TABLE LIGNE_COMMANDE
(
cdeId INT NOT NULL
, ligneCdeId INT NOT NULL
, articleId INT NOT NULL
, uniteId INT NOT NULL
, ligneCdeQte INT NOT NULL
, CONSTRAINT LIGNE_COMMANDE_PK PRIMARY KEY (cdeId, ligneCdeId)
, CONSTRAINT LIGNE_COMMANDE_ARTICLE_UNITE_FK FOREIGN KEY (articleId, uniteId)
REFERENCES ARTICLE_UNITE (articleId, uniteId)
) ;
CREATE TABLE LIGNE_FACTURE
(
factId INT NOT NULL
, ligneFactId INT NOT NULL
, articleId INT NOT NULL
, uniteId INT NOT NULL
, ligneFactQte INT NOT NULL
CONSTRAINT LIGNE_FACTURE_PK PRIMARY KEY (factId, ligneFactId),
CONSTRAINT LIGNE_FACTURE_ARTICLE_UNITE_FK FOREIGN KEY (articleId, uniteId)
REFERENCES ARTICLE_UNITE (articleId, uniteId)
) ;
Un début de jeu d’essai
-------------------------------------
-- Les unités
-------------------------------------
INSERT INTO UNITE (uniteId, uniteLibelle, uniteQte) VALUES
(1, 'carton', 24)
, (2, 'carton', 12)
, (3, 'carton', 6)
, (4, 'pièce', 1)
, (5, 'cageot', 12)
, (6, 'cageot', 8)
;
SELECT '' as 'unite => ', *
FROM UNITE
;
unite => uniteId uniteLibelle uniteQte
---------- ----------- ---------------- -----------
1 carton 24
2 carton 12
3 carton 6
4 pièce 1
5 cageot 12
6 cageot 8
-------------------------------------
-- Les articles
-------------------------------------
INSERT INTO ARTICLE (articleId, reference, statut) VALUES
(1, 'tomates', 's')
, (2, 'pommes', 's')
, (3, 'poires', 's')
, (4, 'scoubidous', 's')
;
SELECT '' 'as article => ', * FROM ARTICLE
;
article => articleId reference statut
------------ ----------- ---------------- ------
1 tomates s
2 pommes s
3 poires s
4 scoubidous s
-----------------------------------------
-- Les paires {article, unité}
-- et quantités en stock correspondantes
-----------------------------------------
INSERT INTO ARTICLE_UNITE (articleId, uniteId, quantiteStock) VALUES
(1,1, 110)
, (1,2, 120)
, (1,3, 130)
, (1,4, 140)
, (2,1, 210)
, (2,4, 240)
, (2,5, 250)
;
SELECT '' as 'article_unite => ', y.reference, z.uniteLibelle + ' ('+ CAST(z.uniteQte as varchar(8)) + ')', x.quantiteStock
FROM ARTICLE_UNITE as x
JOIN ARTICLE as y ON x.articleId = y.articleId
JOIN UNITE as z ON x.uniteId = z.uniteId
article_unite => reference quantiteStock
------------------ ---------------- -----------------------------
tomates carton (24) 110
tomates carton (12) 120
tomates carton (6) 130
tomates pièce (1) 140
pommes carton (24) 210
pommes pièce (1) 240
pommes cageot (12) 250
-----------------------------
-- Les lignes de commande
-----------------------------
INSERT INTO LIGNE_COMMANDE (cdeId, ligneCdeId, articleId, uniteId, ligneCdeQte)
VALUES
(1, 1, 1, 1, 10)
, (1, 2, 1, 2, 20)
, (1, 3, 2, 1, 25)
, (1, 4, 2, 5, 15)
;
SELECT '' as 'ligne_commande => '
, cdeId, ligneCdeId, y.reference, z.uniteLibelle + ' ('+ CAST(z.uniteQte as varchar(8)) + ')', t.ligneCdeQte
FROM LIGNE_COMMANDE as t
JOIN ARTICLE_UNITE as x ON t.articleId = x.articleId AND t.uniteId = x.uniteId
JOIN ARTICLE as y ON x.articleId = y.articleId
JOIN UNITE as z ON x.uniteId = z.uniteId
;
ligne_commande => cdeId ligneCdeId reference ligneCdeQte
------------------- ----------- ----------- ---------------- -----------------------
1 1 tomates carton (24) 10
1 2 tomates carton (12) 20
1 3 pommes carton (24) 25
1 4 pommes cageot (12) 15
-----------------------------------
-- Les lignes de facture
-----------------------------------
INSERT INTO LIGNE_FACTURE(factId, ligneFactId, articleId, uniteId, ligneFactQte)
VALUES
(1, 1, 1, 2, 5)
, (1, 2, 1, 3, 20)
, (1, 3, 1, 4, 12)
, (1, 4, 2, 1,21)
, (1, 5, 2, 5, 25)
;
SELECT '' as 'ligne_facture => '
, factId, ligneFactId, y.reference, z.uniteLibelle + ' ('+ CAST(z.uniteQte as varchar(8)) + ')', t.ligneFactQte
FROM LIGNE_FACTURE as t
JOIN ARTICLE_UNITE as x ON t.articleId = x.articleId AND t.uniteId = x.uniteId
JOIN ARTICLE as y ON x.articleId = y.articleId
JOIN UNITE as z ON x.uniteId = z.uniteId
;
ligne_facture => factId ligneFactId reference ligneFactQte
------------------ ----------- ----------- ---------------- -------------------------
1 1 tomates carton (12) 5
1 2 tomates carton (6) 20
1 3 tomates pièce (1) 12
1 4 pommes carton (24) 21
1 5 pommes cageot (12) 25
Maintenant si tout ça ne répond pas à votre besoin, on peut reprendre à zéro...
Concernant la cohérence du stock par rapport aux commandes et aux factures, des triggers ad-hoc devraient permettre de s’en assurer.