Demande d'aide modélisation héritage

**fsmrel** · 18/01/2023, 19h19

Grâce à Looping, dans la foulée, voici les CREATE TABLE, the fingers in the nose :

CREATE TABLE Famille
(
   FamilleId INT
 , FamilleCode VARCHAR(2) NOT NULL
 , FamilleLibelle VARCHAR(48) NOT NULL
 , CONSTRAINT Famille_PK PRIMARY KEY(FamilleId)
 , CONSTRAINT Famille_AK UNIQUE(FamilleCode)
 , CONSTRAINT Famille_1_AK UNIQUE(FamilleLibelle)
);

CREATE TABLE Nomenclature
(
   NomenId INT
 , FamilleId INT NOT NULL
 , CONSTRAINT Nomenclature_PK PRIMARY KEY(NomenId)
 , CONSTRAINT Nomenclature_Famille_FK FOREIGN KEY(FamilleId) 
        REFERENCES Famille(FamilleId)
);

CREATE TABLE NomenclatureVegetal
(
   NomenclatureId INT
 , NomenId INT NOT NULL
 , NomenclatureLibelle VARCHAR(48) NOT NULL
 , CONSTRAINT NomenclatureVegetal_PK PRIMARY KEY(NomenclatureId)
 , CONSTRAINT NomenclatureVegetal_AK UNIQUE(NomenId)
 , CONSTRAINT NomenclatureVegetal_Nomenclature_FK 
       FOREIGN KEY(NomenId) REFERENCES Nomenclature(NomenId) 
);

CREATE TABLE NomenclatureViande
(
   NomenclatureId INT
 , NomenId INT NOT NULL
 , NomenclatureLibelle VARCHAR(48) NOT NULL
 , CONSTRAINT NomenclatureViande_PK PRIMARY KEY(NomenclatureId)
 , CONSTRAINT NomenclatureViande_AK UNIQUE(NomenId)
 , CONSTRAINT NomenclatureViande_Nomenclature_FK 
       FOREIGN KEY(NomenId) REFERENCES Nomenclature(NomenId) 
);

CREATE TABLE NomenclatureOrigineAnimale
(
   NomenclatureId INT
 , NomenId INT NOT NULL
 , NomenclatureLibelle VARCHAR(48) NOT NULL
 , CONSTRAINT NomenclatureOrigineAnimale_PK PRIMARY KEY(NomenclatureId)
 , CONSTRAINT NomenclatureOrigineAnimale_AK UNIQUE(NomenId)
 , CONSTRAINT NomenclatureOrigineAnimale_Nomenclature_FK 
       FOREIGN KEY(NomenId) REFERENCES Nomenclature(NomenId) 
);

CREATE TABLE Espece
(
   EspeceId INT
 , NomenId INT NOT NULL
 , EspeceLibelle VARCHAR(48) NOT NULL
 , CONSTRAINT Espece_PK PRIMARY KEY(EspeceId)
 , CONSTRAINT Espece_AK UNIQUE(NomenId)
 , CONSTRAINT Espece_1_AK UNIQUE(EspeceLibelle)
 , CONSTRAINT Espece_Nomenclature_FK FOREIGN KEY(NomenId) 
       REFERENCES Nomenclature(NomenId)
) ;

CREATE TABLE Marchandise
(
   MarchandiseId INT
 , FamilleId INT NOT NULL
 , MarchandiseLibelle VARCHAR(48) NOT NULL
 , CONSTRAINT Marchandise_PK PRIMARY KEY(MarchandiseId)
 , CONSTRAINT Marchandise_Famille_FK 
       FOREIGN KEY(FamilleId) REFERENCES Famille(FamilleId)
);

CREATE TABLE Dossier
(
   DossierId INT
 , DossierLibelle VARCHAR(24) NOT NULL
 , MarchandiseId INT NOT NULL
 , CONSTRAINT Dossier_PK PRIMARY KEY(DossierId)
 , CONSTRAINT Dossier_AK UNIQUE(MarchandiseId)
 , CONSTRAINT Dossier_1_AK UNIQUE(DossierLibelle)
 , CONSTRAINT Dossier_Marchandise_FK FOREIGN KEY(MarchandiseId) 
       REFERENCES Marchandise(MarchandiseId)
);
CREATE TABLE Lot
(
   MarchandiseId INT
 , LotId INT
 , NomenId INT NOT NULL
 , Quantite INT NOT NULL
 , CONSTRAINT Lot_PK PRIMARY KEY(MarchandiseId, LotId)
 , CONSTRAINT Lot_Marchandise_FK FOREIGN KEY(MarchandiseId) 
       REFERENCES Marchandise(MarchandiseId) 
       ON DELETE CASCADE
 , CONSTRAINT Lot_Nomenclature_FK FOREIGN KEY(NomenId) 
       REFERENCES Nomenclature(NomenId)
);

CREATE TABLE LotViande
(
   MarchandiseId INT
 , LotId INT
 , CONSTRAINT LotViande_PK PRIMARY KEY(MarchandiseId, LotId)
 , CONSTRAINT LotViande_Lot_FK FOREIGN KEY(MarchandiseId, LotId)
       REFERENCES Lot(MarchandiseId, LotId)
       ON DELETE CASCADE
);

CREATE TABLE LotVegetal
(
   MarchandiseId INT
 , LotId INT
 , Etat VARCHAR(50) NOT NULL
 , CONSTRAINT LotVegetal_PK PRIMARY KEY(MarchandiseId, LotId)
 , CONSTRAINT LotVegetal_Lot_FK FOREIGN KEY(MarchandiseId, LotId) 
       REFERENCES Lot(MarchandiseId, LotId)
       ON DELETE CASCADE
);

CREATE TABLE LotAnimalVivant
(
   MarchandiseId INT
 , LotId INT
 , Puce VARCHAR(24) NOT NULL
 , Sexe VARCHAR(4) NOT NULL
 , CONSTRAINT LotAnimalVivant_PK PRIMARY KEY(MarchandiseId, LotId)
 , CONSTRAINT LotAnimalVivant_AK UNIQUE(Puce)
 , CONSTRAINT LotAnimalVivant_Lot_FK FOREIGN KEY(MarchandiseId, LotId) 
       REFERENCES Lot(MarchandiseId, LotId)
       ON DELETE CASCADE
);

CREATE TABLE LotOrigineAnimale
(
   MarchandiseId INT
 , LotId INT
 , CONSTRAINT LotOrigineAnimale_PK PRIMARY KEY(MarchandiseId, LotId)
 , CONSTRAINT LotOrigineAnimale_Lot_FK FOREIGN KEY(MarchandiseId, LotId) 
       REFERENCES Lot(MarchandiseId, LotId)
       ON DELETE CASCADE
);

**fsmrel** · 18/01/2023, 22h50

Un petit jeu d’essai :

INSERT INTO Famille VALUES
    (1, 'A', 'Animaux vivants')
  , (2, 'P', 'Produits d''origine animale')
  , (3, 'PP', 'Végétaux et produits végétaux')
  , (4, 'D', 'Produits destinés à l''alimentation animale')
;
SELECT '' AS Famille, * FROM Famille ;

INSERT INTO Nomenclature VALUES
    (1, 2)  -- nomenId  familleId
  , (2, 2)  -- viande
  , (3, 2)  -- viande
  , (4, 3)  -- vegetal
  , (5, 3)  -- vegetal
  , (6, 3)  -- vegetal
;
SELECT '' AS Nomenclature, * FROM Nomenclature ;

INSERT INTO NomenclatureViande VALUES
    (1, 1, 'Viande V1') -- nomenclatureId, nomenId, libellé
  , (2, 2, 'Viande V2')
  , (3, 3, 'Viande V3')
;

SELECT '' AS NomenclatureViande, v.*, FamilleLibelle 
FROM NomenclatureViande AS v
JOIN Nomenclature AS n ON v.NomenId = n.NomenId
JOIN Famille AS f ON n.FamilleId = f.FamilleId 
;

INSERT INTO NomenclatureVegetal VALUES
    (1, 4, '1MABG')  -- nomenclatureId, nomenId, libellé
  , (2, 5, 'SOLTU')
  , (3, 6, 'LYPES')
;
SELECT '' AS NomenclatureVegetal,  v.*, FamilleLibelle  
FROM NomenclatureVegetal as v
JOIN Nomenclature AS n ON v.NomenId = n.NomenId
JOIN Famille AS f ON n.FamilleId = f.FamilleId 
;
INSERT INTO Marchandise VALUES
    (1, 2, 'M1')   -- MarchandiseId, FamilleId, libelle
  , (2, 2, 'M2')   -- marchandise viande
  , (3, 3, 'M3')   -- marchandise végétale
  , (4, 2, 'M4')   -- marchandise viande
  , (5, 3, 'M5')   -- marchandise végétale
  , (6, 3, 'M6')   -- marchandise végétale
;
SELECT '' AS Marchandise, * FROM Marchandise ;
SELECT '' AS Lot, o.*
  , m.MarchandiseLibelle
  , f.FamilleLibelle
  , n.FamilleId AS NomenFamilleId
  , f2.FamilleLibelle  AS NomenFamilleLibelle     
FROM Lot as o
JOIN Marchandise AS m ON m.MarchandiseId = o.MarchandiseId 
JOIN Famille AS f on m.FamilleId = f.FamilleId
JOIN Nomenclature AS n ON o.NomenId = n.NomenId
JOIN Famille AS f2 ON n.FamilleId = f2.FamilleId
;

-- Lots de type viande
INSERT INTO Lot VALUES
    (1, 1, 1, 15)  -- MarchandiseId, LotId, NomenId, Quantite 
  , (1, 2, 1, 25)
  , (1, 3, 1, 36)
  , (2, 1, 1, 211)
  , (2, 2, 1, 221)
;
SELECT '' AS Lot, o.*
  , m.MarchandiseLibelle
  , f.FamilleLibelle
  , n.FamilleId AS NomenFamilleId
  , f2.FamilleLibelle  AS NomenFamilleLibelle     
FROM Lot as o
JOIN Marchandise AS m ON m.MarchandiseId = o.MarchandiseId 
JOIN Famille AS f on m.FamilleId = f.FamilleId
JOIN Nomenclature AS n ON o.NomenId = n.NomenId
JOIN Famille AS f2 ON n.FamilleId = f2.FamilleId
;
INSERT INTO LotViande VALUES
    (1, 1)  -- MarchandiseId, LotId 
  , (1, 2)
  , (1, 3)
  , (2, 1)
  , (2, 2)
;
SELECT DISTINCT '' AS LotViande, v.*
  , m.MarchandiseLibelle
  , f.FamilleLibelle
  , n.NomenclatureLibelle      
FROM LotViande AS v
JOIN Marchandise AS m ON m.MarchandiseId = v.MarchandiseId 
JOIN Famille AS f ON m.FamilleId = f.FamilleId
JOIN Lot AS o ON v.LotId = o.LotId 
             AND v.MarchandiseId = o.MarchandiseId
JOIN NomenclatureViande AS n ON o.NomenId = n.NomenId
JOIN Nomenclature AS n2 ON n2.FamilleId = f.FamilleId

-- Lots de type Vegetal
INSERT INTO Lot VALUES
   (3, 1, 4, 314)  -- MarchandiseId, LotId, NomenId, Quantite
 , (3, 2, 1, 321)
;
INSERT INTO LotVegetal VALUES
    (3, 1, 'bon état')       -- MarchandiseId, LotId, Etat 
  , (3, 2, 'état médiocre')  -- MarchandiseId, LotId, Etat 
;
SELECT '' AS LotVegetal, v.*
  , m.MarchandiseLibelle
  , f.FamilleId as FamilleId
  , f.FamilleLibelle
  , o.NomenId AS NomenId
  , n.FamilleId AS NomenFamilleId
FROM LotVegetal AS v
JOIN Marchandise AS m ON m.MarchandiseId = v.MarchandiseId 
JOIN Famille AS f ON m.FamilleId = f.FamilleId
JOIN Lot AS o ON v.LotId = o.LotId
             AND v.MarchandiseId = o.MarchandiseId
JOIN Nomenclature AS n ON o.NomenId = n.NomenId 
;

Il se trouve que le lot pour lequel MarchandiseId = 3 et LotId = 1 fait référence (via sa famille) à la famille "Produits d'origine animale", mais ce lot fait par ailleurs référence à une nomenclature faisant elle-même référence à la famille "Végétaux et produits végétaux".

Cette anomalie sera détectée et invalidée par un trigger qu’il me reste à coder. J’arrive !

**CAymeric** · 18/01/2023, 23h41

Bonsoir fsmrel,

J'ai pu analyser un peu (et non sans mal) les différentes proposition que vous avez soumises. Et effectivement le dernier MCD semble se rapprocher de la bonne solution.

Si je comprends bien, l'entité Famille permet de différencier le type de marchandise (donc de dossier , pour rappel libellé dossier = P, PP, A ou D).

Concernant l'entité Lot , un trigger afin de vérifier que marchandise.familleid = nomenclature.familleid à l'insertion ou l'update d'une ligne sera donc indispensable .

Concernant la spécialisation / généralisation , si je comprends bien , tout se joue en niveau des PK et FK de part et autres des sur-types et sous-types ?

Je constate que l'on défini des PK composites. Peut-on ensuite définir une contrainte de FK comme le montre l'extrait du MLD suivant crée grâce au script SQL fourni par vos soins ? :

Nom : Capture d’écran 2023-01-18 233845.png
Affichages : 165
Taille : 23,5 Ko

Nom : Capture d’écran 2023-01-18 233845.png
Affichages : 165
Taille : 23,5 Ko

Question de débutant probablement, mais à laquelle je n'ai pas trouvé de réponse satisfaisante.

Merci pour ces avancées !

**CAymeric** · 18/01/2023, 23h50

Rebonsoir fsmrel,

Je viens de voir votre dernier post après avoir envoyé le mien.

C'est très prometteur, mais cela va nécessiter un bonne gymnastique mentale et un bonne compréhension de tout ces concepts pour être capable de reproduire ce type de modélisation en autonomie.

Je serais plus disponible en fin de semaine pour tenter de finaliser tout cela avec vous,

Je vous souhaite une bonne fin de soirée

**fsmrel** · 19/01/2023, 02h42

Bonsoir CAymeric,

J’ai vu vos messages seulement au moment où j’allais envoyer ce qui suit. Donc je balance quand même, et examinerai plus tard les points qui vous tracassent.

Voici donc le trigger invalidant les inserts délinquants.

/*
La famille de la nomenclature référencée par un lot doit être celle de la famille de ce lot.
*/

CREATE TRIGGER Lot_Coherence_Famille_trigger ON Lot
AFTER INSERT, UPDATE AS
DECLARE @n as INT ;
DECLARE @Engueulade AS VARCHAR(254)
SET @n =
 (
    SELECT COUNT(*)
    FROM INSERTED
    WHERE  EXISTS
   (
    SELECT 
            o.MarchandiseId
          , o.LotId
          , o.NomenId
          , f.FamilleId
          , n.FamilleId
    FROM INSERTED AS o
         JOIN Marchandise AS m ON m.MarchandiseId = o.MarchandiseId 
         JOIN Famille AS f ON m.FamilleId = f.FamilleId
         JOIN Nomenclature AS n ON o.NomenId = n.NomenId 
    WHERE f.FamilleId <> n.FamilleId
   )
 )
;
IF @n > 0
  BEGIN
    -- Les délinquants 
    SELECT  '' AS 'Lot delinquant => '
          , o.MarchandiseId, o.LotId
          , f.FamilleId as MarchandiseFamilleId
          , o.NomenId AS NomenId
          , n.FamilleId AS NomenFamilleId
    FROM INSERTED AS o
         JOIN Marchandise AS m ON m.MarchandiseId = o.MarchandiseId 
         JOIN Famille AS f ON m.FamilleId = f.FamilleId
         JOIN Nomenclature AS n ON o.NomenId = n.NomenId 
    WHERE f.FamilleId <> n.FamilleId
;   
    SET @Engueulade = 'La famille de la nomenclature référencée par un lot doit être celle de la famille de ce lot.'
    RAISERROR (@Engueulade, 16,1)  
    ROLLBACK
  END ;

Chose intéressante : dans le post #16, j’ai précisé qu’il fallait quatre triggers pour contrôler l’ensemble des lots, à savoir celui que j’avais fourni, affecté à la table LotVegetal, et ses cousins affectés respectivement aux tables LotViande, LotOrigineAnimale et LotAnimalVivant.

Eh bien ! comme le nouveau trigger est appliqué à la table Lot, il remplace les quatre premiers !

Mais c’est du donnant donnant, vous n’échapperez pas aux triggers de contrôle de l’exclusion entre les quatre sous-types LotVegetal, LotViande, LotOrigineAnimale et LotAnimalVivant...

Et pour faire bonne mesure vous m’en mettrez quatre en plus pour garantir l’exclusion entre NomenclatureVegetal, NomenclatureViande, NomenclatureOrigineAnimale et Espece, du fait de leur généralisation...

Ces huit triggers se ressemblant, une fois qu’on en a créé un, pour les autres, à epsilon près, c’est du copier/coller et de l’huile de coude.

En voici un, plutôt bourrin, mais je n’ai pas fait dans la dentelle :

CREATE TRIGGER LotViande_Exclusion_trigger ON LotViande 
FOR INSERT, UPDATE AS

DECLARE @n1 as INT ;
DECLARE @n2 as INT ;
DECLARE @n3 as INT ;
DECLARE @Engueulade AS VARCHAR(254)

SET @n1 =
  (SELECT COUNT(*) 
   FROM INSERTED
   WHERE EXISTS 
    (
     SELECT ''
     FROM INSERTED AS i
     JOIN LotVegetal AS v
         ON i.MarchandiseId = v.MarchandiseId AND i.LotId = v.LotId 
    )
  )
IF @n1 > 0
  BEGIN
     SELECT '' AS 'Viol exclusion', i.MarchandiseId, i.LotId
     FROM INSERTED AS i
     JOIN LotVegetal AS v
         ON i.MarchandiseId = v.MarchandiseId AND i.LotId = v.LotId
     ;
     SET @Engueulade = 'Table LotViande : lot déjà affecté à la table LotVegetal.'
     RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
     ROLLBACK
  END ;

SET @n2 =
  (SELECT COUNT(*) 
   FROM INSERTED
   WHERE EXISTS 
    (
     SELECT i.MarchandiseId, i.LotId
     FROM INSERTED AS i
     JOIN LotOrigineAnimale AS v
         ON i.MarchandiseId = v.MarchandiseId AND i.LotId = v.LotId 
    )
  )
IF @n2 > 0
  BEGIN
     SELECT '' AS 'Viol exclusion', i.MarchandiseId, i.LotId
     FROM INSERTED AS i
     JOIN LotOrigineAnimale AS v
         ON i.MarchandiseId = v.MarchandiseId AND i.LotId = v.LotId 
     ;
     SET @Engueulade = 'Table LotViande : lot déjà affecté à la table LotOrigineAnimale.'
     RAISERROR (@Engueulade, 16,1)
     ROLLBACK
  END ;

SET @n3 =
  (SELECT COUNT(*) 
   FROM INSERTED
   WHERE EXISTS 
    (
     SELECT i.MarchandiseId, i.LotId
     FROM INSERTED AS i
     JOIN LotAnimalVivant AS v
         ON i.MarchandiseId = v.MarchandiseId AND i.LotId = v.LotId 
    )
  )
IF @n3 > 0
  BEGIN
     SELECT '' AS 'Viol exclusion', i.MarchandiseId, i.LotId
     FROM INSERTED AS i
     JOIN LotAnimalVivant AS v
         ON i.MarchandiseId = v.MarchandiseId AND i.LotId = v.LotId 
     ;
     SET @Engueulade = 'Table LotViande : lot déjà affecté à la table LotAnimalVivant.'
     RAISERROR (@Engueulade, 16,1)
     ROLLBACK
  END ;

Pour les autres, à vous de jouer

Et n’oubliez pas de

quand vous estimez qu’on progresse, même si tout n'est pas compréhensible tout de suite, patience.

**CAymeric** · 20/01/2023, 11h28

Bonjour fsmrel,

Je travaille actuellement sur le sujet. J'arrive à quelque chose de plutôt prometteur concernant la contrainte d'unicité entre les 4 sous-types de Lot

Création d'un fonction générique appelée dans une contrainte "CHECK" définie sur chaque sous-type qui vérifie si la PK composite (machandiseid , lotid) existe déjà dans un des 4 sous-types.

Ce qui éviterais la redondance des 4 triggers et bien plus lisible. Je vous fais un retour détaillé dès que je serais sûr de mon coup !

Bonne journée

**fsmrel** · 20/01/2023, 16h39

Bonjour CAymeric,

Je vois que vous ne perdez pas votre temps ! Manifestement vous progressez, bonne idée que votre proposition de trigger, restera à secouer. celui-ci...

Votre sujet est bien intéressant, aussi j’y fais référence dans la discussion où l’on traite de l’évolution de Looping. Pour Paprick, ça devient un cas d’école

En fouillant dans mes archives, j’ai retrouvé des vieilles discussions où non seulement je me suis frotté à PostgreSQL, mais en plus j’y avais programmé des triggers (bien sûr bourrins) ! Par exemple ici ou là.

Encore une fois, courage !

**CAymeric** · 20/01/2023, 18h20

Voila donc le résultat :

Définition de la fonction :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

CREATE FUNCTION contrainte_lot_unique(_soustype1 TEXT, _soustype2 TEXT, _soustype3 TEXT, _lotid INTEGER, _marchandiseid INTEGER) 
RETURNS BOOLEAN
LANGUAGE PLPGSQL
AS $function$
DECLARE
result BOOLEAN;
sql TEXT;
BEGIN
sql = 'SELECT NOT EXISTS(
SELECT (lotid, marchandiseid) FROM ' ||_soustype1|| ' WHERE lotid = '||_lotid|| 'AND marchandiseid = '||_marchandiseid||
' UNION ALL
SELECT (lotid, marchandiseid) FROM ' ||_soustype2|| ' WHERE lotid = '||_lotid|| 'AND marchandiseid = '||_marchandiseid||
' UNION ALL
SELECT (lotid, marchandiseid) FROM ' ||_soustype3|| ' WHERE lotid = '||_lotid|| 'AND marchandiseid = '||_marchandiseid||
')';
execute sql INTO result;
RETURN result;
   END;
$function$;

Puis définition d'une contrainte CHECK sur la fonction pour chaque sous-type de l'entité Lot :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

ALTER TABLE IF EXISTS public.lotanimalvivant
    ADD CONSTRAINT lot_subtype_unique CHECK (contrainte_lot_unique('lotorigineanimale'::text, 'lotviande'::text, 'lotvegetal'::text, lotid, marchandiseid))
    NOT VALID;

ALTER TABLE IF EXISTS public.lotvegetal
    ADD CONSTRAINT lot_subtype_unique CHECK (contrainte_lot_unique('lotorigineanimale'::text, 'lotviande'::text, 'lotanimalvivant'::text, lotid, marchandiseid))
    NOT VALID;

ALTER TABLE IF EXISTS public.lotorigineanimale
    ADD CONSTRAINT lot_subtype_unique CHECK (contrainte_lot_unique('lotvegetal'::text, 'lotviande'::text, 'lotanimalvivant'::text, lotid, marchandiseid))
    NOT VALID;

ALTER TABLE IF EXISTS public.lotviande
    ADD CONSTRAINT lot_subtype_unique CHECK (contrainte_lot_unique('lotorigineanimale'::text, 'lotvegetal'::text, 'lotanimalvivant'::text, lotid, marchandiseid))
    NOT VALID;

Si la fonction retourne TRUE -> la PK(lotid, marchandiseid) n'existe pas dans les 3 autres sous-types donc -> INSERT autorisé.

Si la fonction retourne FALSE - la PK(lotid, marchandiseid) existe dans l'un des sous-type, donc violation de contrainte CHECK -> INSERT impossible

Que pensez vous de cette approche ?

**CAymeric** · 20/01/2023, 19h45

Envoyé par fsmrel

Bonjour CAymeric,

Je vois que vous ne perdez pas votre temps ! Manifestement vous progressez, bonne idée que votre proposition de trigger, restera à secouer. celui-ci...

Votre sujet est bien intéressant, aussi j’y fais référence dans la discussion où l’on traite de l’évolution de Looping. Pour Paprick, ça devient un cas d’école

En fouillant dans mes archives, j’ai retrouvé des vieilles discussions où non seulement je me suis frotté à PostgreSQL, mais en plus j’y avais programmé des triggers (bien sûr bourrins) ! Par exemple ici ou là.

Encore une fois, courage !

Bonsoir fsmrel,

Je ne manquerais pas de m'y attarder

**fsmrel** · 21/01/2023, 19h36

Bonsoir CAymeric,

Envoyé par CAymeric

Si la fonction retourne TRUE -> la PK(lotid, marchandiseid) n'existe pas dans les 3 autres sous-types donc -> INSERT autorisé.

Si la fonction retourne FALSE - la PK(lotid, marchandiseid) existe dans l'un des sous-type, donc violation de contrainte CHECK -> INSERT impossible

Que pensez vous de cette approche ?

20/20 et en cotant vache !

Je me suis dit que j’arriverais bien à trouver un petit update piégeux, du genre :

UPDATE LotVegetal
SET MarchandiseId = 9, LotId = 1
WHERE MarchandiseId = 3 AND LotId = 2 ;

Eh bien, je suis refait :

Envoyé par PostgreSQL

ERROR: ERREUR: la nouvelle ligne viole la contrainte de vérification « lotvegetal » de la relation « lot_subtype_unique »

Conclusion : Bravo !

Cela dit, pour m’en assurer, il a fallu que j’aille rechercher un vieux PostgreSQL, mais c’est jpgAmin 4 qui m’en a fait voir, j’ai dû finalement le réinstaller...

**CAymeric** · 21/01/2023, 23h48

Bonsoir fsmrel,

J'avais rencontré un problème lors des updates après d'un premier jet de ma fonction, que j'ai pu corriger par la suite. Petit bémol sur mon travail : j'aimerais pouvoir passer les noms de table dans les paramètres de la fonction sans "bricoler" en concaténant, histoire que cela fasse plus propre (si toutefois c'est possible).

Je vais reproduire cette même contrainte au niveau des nomenclatures. Et pourquoi pas même me lancer le défi de coupler ces contraintes CHECK avec le système d'héritage proposé par Postgresql. Qui me permettrait, si j'ai bien compris le principe, de récupérer les données unifiés du sur-type et du sous-type en effectuant une simple requête sur le sous-type

Grâce à nos échanges, cette partie du MCD (marchandises) prends une tournure que je n'aurais pas soupçonnée

, et c'est fort encourageant ! Les acquis sur cette partie vont très certainement servir pour le reste du projet ! Il y a encore beaucoup de chemin mais c'est en bonne voie et m'amène du coup à remettre en question certains choix qui ont été fais.

Je devrais arriver à une version finale de cette partie "Marchandises" la semaine prochaine.

Encore

pour ce partage de connaissances

**fsmrel** · 22/01/2023, 01h23

Muy bien!

Peaufinez, peaufinez ! A la semaine prochain donc, avec, espérons-le, un MCD stabilisé. On croise les doigts

**fsmrel** · 03/02/2023, 14h00

Bonjour CAymeric,

Envoyé par CAymeric

Je devrais arriver à une version finale de cette partie "Marchandises" la semaine prochaine.

On attend avec impatience !

**CAymeric** · 03/02/2023, 14h31

Bonjour fsmrel,

Les impératifs pro font que ca piétine un peu !!! Je n'ai pas eu le temps voulu à consacrer au projet ! Mais je ne vous oublie pas

**CAymeric** · 17/02/2023, 14h20

Bonjour fsmrel,

Ca avance ça avance ! Je pense même avoir réussi à simplifier grandement le MCD

J'ai rencontré néanmoins un point bloquant sur Looping :

En effet je souhaiterais pouvoir définir une contrainte de clé étrangère faisant référence à un groupe de colonnes UNIQUE mais NON PK. Looping ne prend que les PK comme référence de FK ou je m'y suis mal pris ?

Voila ce que je voudrais modéliser dans Looping :

```
CREATE TABLE IF NOT EXISTS public.dossier
(
id_dossier integer NOT NULL DEFAULT nextval('dossier_id_dossier_seq'::regclass),
dossier_num character varying(50) COLLATE pg_catalog."default" NOT NULL,
dossier_type character varying(50) COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT dossier_pkey PRIMARY KEY (id_dossier),
CONSTRAINT dossier_num_key UNIQUE (dossier_num),
CONSTRAINT id_dossier_dossier_type_key UNIQUE (id_dossier, dossier_type) //Cette contrainte UNIQUE doit servir pour les FK
)

CREATE TABLE IF NOT EXISTS public.marchandise
(
machandiselibelle character varying(50),
dossier_type character varying(50) NOT NULL,
id_dossier integer NOT NULL,
CONSTRAINT marchandise_pkey PRIMARY KEY (id_dossier, dossier_type),
CONSTRAINT marchandise_id_dossier_dossier_type_fkey FOREIGN KEY (dossier_type, id_dossier)
REFERENCES public.dossier (dossier_type, id_dossier) // Ici les FK en question
```

Cette approche me permet de critériser le type de marchandise depuis le type de dossier directement, me permettant de me passer de l'entité Famille et également de triggers grâce à de simples CHECK sur les Entité Lots/Nomenclature

Dès que j'arrive à résoudre cela dans LOOPING, je poste une pré-version finale !

**fsmrel** · 17/02/2023, 15h21

Bonjour CAymeric,

Table dossier

Il suffit de rendre (dossier_type, id_dossier) clé primaire et (id_dossier) clé alternative :

CREATE TABLE dossier
(
    id_dossier integer NOT NULL
  , dossier_num character varying(50) NOT NULL
  , dossier_type character varying(50) NOT NULL
  , CONSTRAINT dossier_pkey PRIMARY KEY (dossier_type, id_dossier)
  , CONSTRAINT dossier_num_key UNIQUE (dossier_num)
  , CONSTRAINT id_dossier_dossier_type_key UNIQUE (id_dossier)
) ;

CREATE TABLE marchandise
(
    dossier_type character varying(50) NOT NULL
  , id_dossier integer NOT NULL
  , machandiselibelle character varying(50)
  , CONSTRAINT marchandise_pkey PRIMARY KEY (id_dossier, dossier_type)
  , CONSTRAINT marchandise_id_dossier_dossier_type_fkey FOREIGN KEY (dossier_type, id_dossier)
        REFERENCES dossier (dossier_type, id_dossier)
) ;

**fsmrel** · 17/02/2023, 15h38

La colonne dossier est du type character varying(50), ce qui pour une clé primaire peut être source de mauvaises performances. Soit vous utilisez un type character de taille réduite, soit, au minimum, vous permutez les colonnes dans la clé primaire.

Exemple :

CREATE TABLE dossier
(
    id_dossier integer NOT NULL
  , dossier_num character varying(50) NOT NULL
  , dossier_type character varying(50) NOT NULL
  , CONSTRAINT dossier_pkey PRIMARY KEY (id_dossier, dossier_type)
  , CONSTRAINT dossier_num_key UNIQUE (dossier_num)
  , CONSTRAINT id_dossier_dossier_type_key UNIQUE (id_dossier)
) ;

CREATE TABLE marchandise
(
   id_dossier integer NOT NULL
  , dossier_type character varying(50) NOT NULL
  , machandiselibelle character varying(50)
  , CONSTRAINT marchandise_pkey PRIMARY KEY (id_dossier, dossier_type)
  , CONSTRAINT marchandise_id_dossier_dossier_type_fkey FOREIGN KEY (id_dossier, dossier_type)
        REFERENCES dossier (id_dossier, dossier_type)
) ;

**CAymeric** · 17/02/2023, 16h14

Bonjour fsmrel,

Effectivement j'avais pensé passer (dossier_type, id_dossier) clé primaire et (id_dossier) clé alternative mais l'entité dossier contiens de nombreuses autres relations (qui n'ont pas été présentée dans cette discussion) et cela pose problème dans les autres relations.
Du coup je me retrouve avec (dossier_type, id_dossier) en comme FK sur toutes mes autres relations, ce que je ne souhaites pas ! Seul l'ID du Dossier m'intéresse, sauf pour la partie Marchandise ou je souhaites (dossier_type, id_dossier) afin de critériser.
Cela ne m'empêche pas de finaliser le projet car mais je voulais présenter un MCD/MLD final et un script SQL au poil sur LOOPING!

Effectivement la colonne dossier_type sera VARCHAR(2)

Juste à titre informatif, vue d'ensemble :
Nom : Capture d’écran 2023-02-17 160900.png
Affichages : 89
Taille : 83,8 Ko

**fsmrel** · 17/02/2023, 17h32

Voyez la table truc ci-dessous, elle a une clé étrangère faisant référence à la clé alternative (id_dossier)...

CREATE TABLE dossier
(
    id_dossier integer NOT NULL
  , dossier_num character varying(50) NOT NULL
  , dossier_type character varying(50) NOT NULL
  , CONSTRAINT dossier_pkey PRIMARY KEY (id_dossier, dossier_type)
  , CONSTRAINT dossier_id_key UNIQUE (id_dossier)
  , CONSTRAINT dossier_num_key UNIQUE (dossier_num)
  , CONSTRAINT id_dossier_dossier_type_key UNIQUE (id_dossier)
) ;

CREATE TABLE marchandise
(
   id_dossier integer NOT NULL
  , dossier_type character varying(50) NOT NULL
  , machandiselibelle character varying(50)
  , CONSTRAINT marchandise_pkey PRIMARY KEY (id_dossier, dossier_type)
  , CONSTRAINT marchandise_id_dossier_dossier_type_fkey FOREIGN KEY (id_dossier, dossier_type)
        REFERENCES dossier (id_dossier, dossier_type)
) ;

CREATE TABLE truc
(
    trucid int not null
  , id_dossier int not null
  , truc_nom   varchar(16) not null
  , constraint truc_pk PRIMARY KEY (trucid)
  , constraint truc_dossier_fk  FOREIGN KEY (id_dossier) 
    REFERENCES dossier (id_dossier)
) ;

Quant à votre MCD, je n’arrive pas à le lire. Il faudrait l’urbaniser, mais avec Looping ça pose quelques problèmes, voyez ici...

**fsmrel** · 17/02/2023, 17h44

Symétrie oblige, vous pouvez permuter les rôles et rendre à (id_dossier) sa primauté, la table marchandise n'y verra que du feu !

CREATE TABLE dossier
(
    id_dossier integer NOT NULL
  , dossier_num character varying(50) NOT NULL
  , dossier_type character varying(50) NOT NULL
  , CONSTRAINT dossier_pkey PRIMARY KEY (id_dossier)
  , CONSTRAINT dossier_num_key UNIQUE (dossier_num)
  , CONSTRAINT id_dossier_dossier_type_key UNIQUE (dossier_type, id_dossier)
) ;

CREATE TABLE marchandise
(
    dossier_type character varying(50) NOT NULL
  , id_dossier integer NOT NULL
  , machandiselibelle character varying(50)
  , CONSTRAINT marchandise_pkey PRIMARY KEY (id_dossier, dossier_type)
  , CONSTRAINT marchandise_id_dossier_dossier_type_fkey FOREIGN KEY (dossier_type, id_dossier)
        REFERENCES dossier (dossier_type, id_dossier)
) ;

Demande d'aide modélisation héritage

Looping

Discussions similaires

Partager

Partager