Demande d'aide modélisation héritage

**CAymeric** · 17/02/2023, 17h45

Envoyé par fsmrel

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)
) ;

Oui tout à fait au niveau fonctionnel c'est tout à fait faisable ! Mais je voulais simplement pouvoir décider des REFERENCES moi même juste pour une présentation du MLD complet propre et un script SQL bien ficelé ! Mais à priori Looping s'entête à prendre uniquement les PK comme REFERENCES pour les FK

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

En complément :

Les suggestions que je vous ai faites peuvent choquer les inconditionnels du rôle qu’ils attribuent à la clé primaire¹, mais...
Dans le contexte du Modèle Relationnel de Données (alias Théorie Relationnelle), il n’y a que des clés candidates et des surclés telles que {id_dossier, dossier_type} et {id_dossier}, « à égalité » dans les rôles à jouer. Le concept de clé primaire en est absent. Autrement dit la sémantique, voire l’émotion, donc l’arbitraire, ne jouent plus et il serait idiot de penser que la clé primaire serait en quelque sorte « plus égale que les autres ».
En dernier ressort, c’est au DBA de secouer l’application avant la mise en production et de s’assurer de la bonne organisation des clés quant à la performance (par exemple choix des index, contrôle du verrouillage des tables). Mais, Sahib, ceci est une autre histoire...

¹ Le père du modèle relationnel de données, Ted Codd (rip) a utilisé l’expression « primary key » dans son article fondateur A Relational Model of Data for Large Shared Data Bank - Comm. ACM, V13, N6, June 1970, pp. 377-387. Mais depuis, de l’eau a coulé sous les ponts...

Référence incontournable :
Database Design and Relational Theory Normal Forms and All That Jazz (C.J. Date).

Et n’oubliez pas de voter quand j’ai pu vous aider !

**fsmrel** · 17/02/2023, 19h54

Envoyé par CAymeric

Oui tout à fait au niveau fonctionnel c'est tout à fait faisable ! Mais je voulais simplement pouvoir décider des REFERENCES moi même juste pour une présentation du MLD complet propre et un script SQL bien ficelé ! Mais à priori Looping s'entête à prendre uniquement les PK comme REFERENCES pour les FK :aie :

Pour sa part, Developpez.com n’a pas réussi à m’afficher le message #41, il a fallu que crée le message #42 pour pouvoir vous lire. Je vais regarder cette histoire de PK/FK.

**fsmrel** · 17/02/2023, 22h39

Du point de vue purement SQL, vous pouvez parfaitement coder quelque chose du genre :

CREATE TABLE dossier
(
    id_dossier integer NOT NULL
  , dossier_num character varying(50) NOT NULL
  , dossier_type character varying(2) 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(2) NOT NULL
  , id_dossier integer NOT NULL
  , machandiselibelle character varying(50)
  , CONSTRAINT marchandise_pkey PRIMARY KEY (dossier_type, id_dossier)
  , CONSTRAINT marchandise_id_dossier_dossier_type_fkey FOREIGN KEY (id_dossier)
        REFERENCES dossier (id_dossier)
) ;
ALTER TABLE marchandise
   DROP CONSTRAINT marchandise_id_dossier_dossier_type_fkey ; 

ALTER TABLE marchandise
   ADD  CONSTRAINT marchandise_id_dossier_dossier_type_fkey FOREIGN KEY (dossier_type, id_dossier)
            REFERENCES dossier (dossier_type, id_dossier) ;

De son côté, Looping accepte de créer des ALTER TABLE au moyen d’une règle :

Mais Looping fait figurer ces ALTER TABLE seulement au début ou à la fin du code SQL...

Pour plus d’information sur cette manip, voyez avec Paprick, papa de Looping...

**CAymeric** · 19/02/2023, 01h19

Bonsoir fsmrel,

Je vous présente donc comme convenu l'avancée des travaux. Les contraintes au niveau des nomenclatures ont été simplifiées étant donné que ce sont des données statiques, uniquement éditable par l'administration de la future application.

Avec ce modèle et les tests effectués, j'obtiens le résultat escompté :

Un dossier est des type A,PP,P ou D.
Chaque dossier couvre une marchandise, pouvant être composée d'un ou plusieurs lots, mais appartenant à une seule famille de produits.

Nom : Capture d’écran 2023-02-19 010710.png
Affichages : 109
Taille : 39,5 Ko

Nom : Capture d’écran 2023-02-19 010710.png
Affichages : 109
Taille : 39,5 Ko

Nom : Capture d’écran 2023-02-19 011131.png
Affichages : 105
Taille : 32,9 Ko

Script de génération de la BDD :

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
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

CREATE TABLE dossiers(
   id SERIAL,
   type VARCHAR(2) CHECK (type IN ('A','P','PP','D')),
   num VARCHAR(50) NOT NULL,
   PRIMARY KEY(id, type),
   UNIQUE(num)
);

CREATE TABLE avi(
   id SERIAL,
   latin VARCHAR(50) NOT NULL,
   nom VARCHAR(50) NOT NULL,
   PRIMARY KEY(id)
);

CREATE TABLE marchandises(
   id_dossiers INTEGER,
   type_dossiers VARCHAR(2),
   quantite_totale INTEGER NOT NULL,
   conditions_transport VARCHAR(50) NOT NULL,
   PRIMARY KEY(id_dossiers, type_dossiers),
   FOREIGN KEY(id_dossiers, type_dossiers) REFERENCES dossiers(id, type)
);

CREATE TABLE aanoa(
   id SERIAL,
   catégorie VARCHAR(50),
   PRIMARY KEY(id)
);

CREATE TABLE poa(
   id SERIAL,
   libelle VARCHAR(50),
   cs VARCHAR(50),
   hc BOOLEAN,
   PRIMARY KEY(id)
);

CREATE TABLE vpv(
   id SERIAL,
   oepp VARCHAR(50) NOT NULL,
   latin VARCHAR(50) NOT NULL,
   nom VARCHAR(50) NOT NULL,
   PRIMARY KEY(id)
);

CREATE TABLE organismes_nuisibles(
   id SERIAL,
   oepp VARCHAR(10) NOT NULL,
   PRIMARY KEY(id)
);

CREATE TABLE lots(
   id_dossiers INTEGER,
   type_dossiers VARCHAR(2),
   id SERIAL,
   quantite INTEGER NOT NULL,
   PRIMARY KEY(id_dossiers, type_dossiers, id),
   FOREIGN KEY(id_dossiers, type_dossiers) REFERENCES marchandises(id_dossiers, type_dossiers)
);

CREATE TABLE lots_a(
   id_dossiers INTEGER,
   type_dossiers VARCHAR(2),
   id_lots INTEGER,
   puce VARCHAR(50),
   sexe VARCHAR(50),
   id_avi INTEGER NOT NULL,
   PRIMARY KEY(id_dossiers, type_dossiers, id_lots),
   FOREIGN KEY(id_dossiers, type_dossiers, id_lots) REFERENCES lots(id_dossiers, type_dossiers, id),
   FOREIGN KEY(id_avi) REFERENCES avi(id)
);

CREATE TABLE lots_p(
   id_dossiers INTEGER,
   type_dossiers VARCHAR(2),
   id_lots INTEGER,
   canalisation BOOLEAN,
   id_poa INTEGER NOT NULL,
   PRIMARY KEY(id_dossiers, type_dossiers, id_lots),
   FOREIGN KEY(id_dossiers, type_dossiers, id_lots) REFERENCES lots(id_dossiers, type_dossiers, id),
   FOREIGN KEY(id_poa) REFERENCES poa(id)
);

CREATE TABLE lots_d(
   id_dossiers INTEGER,
   type_dossiers VARCHAR(2),
   id_lots INTEGER,
   prlvt BOOLEAN,
   id_aanoa INTEGER NOT NULL,
   PRIMARY KEY(id_dossiers, type_dossiers, id_lots),
   FOREIGN KEY(id_dossiers, type_dossiers, id_lots) REFERENCES lots(id_dossiers, type_dossiers, id),
   FOREIGN KEY(id_aanoa) REFERENCES aanoa(id)
);

CREATE TABLE lots_pp(
   id_dossiers INTEGER,
   type_dossiers VARCHAR(2),
   id_lots INTEGER,
   pp INTEGER,
   id_vpv INTEGER NOT NULL,
   PRIMARY KEY(id_dossiers, type_dossiers, id_lots),
   FOREIGN KEY(id_dossiers, type_dossiers, id_lots) REFERENCES lots(id_dossiers, type_dossiers, id),
   FOREIGN KEY(id_vpv) REFERENCES vpv(id)
);

CREATE TABLE vpv_organismes_nuisibles(
   id_vpv INTEGER,
   id_organismes_nuisibles INTEGER,
   PRIMARY KEY(id_vpv, id_organismes_nuisibles),
   FOREIGN KEY(id_vpv) REFERENCES vpv(id),
   FOREIGN KEY(id_organismes_nuisibles) REFERENCES organismes_nuisibles(id)
);

ALTER TABLE IF EXISTS lots_a
    ADD CONSTRAINT lots_a_type_dossiers_check CHECK (type_dossiers = 'A');

ALTER TABLE IF EXISTS lots_d
    ADD CONSTRAINT lots_d_type_dossiers_check CHECK (type_dossiers = 'D');

ALTER TABLE IF EXISTS lots_p
    ADD CONSTRAINT lots_p_type_dossiers_check CHECK (type_dossiers = 'P');

ALTER TABLE IF EXISTS lots_pp
    ADD CONSTRAINT lots_pp_type_dossiers_check CHECK (type_dossiers = 'PP');

INSERT INTO "avi" ("latin", "nom") VALUES ('Gallus Gallus', 'Poulet'),('Equus Caballus', 'Cheval');

INSERT INTO "vpv" ("oepp","latin", "nom") VALUES ('1MABG', 'Malus','Pomme'),('LYPES', 'Solanum lycopersicum','Tomate');

INSERT INTO "poa" ("libelle","cs", "hc") VALUES ('Viande de volaille', 'POU',TRUE),('Viande bovine', 'BOV',TRUE);

INSERT INTO "dossiers" ("type","num") VALUES ('A','2023.0000001'),('P','2023.0000002'),('PP','2023.0000003');

INSERT INTO "marchandises" ("id_dossiers","type_dossiers","quantite_totale","conditions_transport") VALUES (1,'A',1,'Ambiant'),(2,'P',250,'Réfrigéré'),(3,'PP', 800,'Congelé');

INSERT INTO "lots" ("id_dossiers","type_dossiers","quantite") VALUES (1,'A',1),(2,'P',125),(2,'P',125),(3,'PP', 300),(3,'PP', 300),(3,'PP', 200);

**CAymeric** · 19/02/2023, 01h26

Une rapide vérification :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
INSERT INTO "lots_a" ("id_dossiers", "type_dossiers", "id_lots", "id_avi") VALUES ('1', 'A', '1', '2');
/* Lignes affectées: 1  Lignes trouvées: 0  Avertissements: 0  Durée pour 1 requête: 0,000 s. */

INSERT INTO "lots_a" ("id_dossiers", "type_dossiers", "id_lots", "id_avi") VALUES ('2', 'P', '1', '2');
/* ERREUR:  la nouvelle ligne de la relation « lots_a » viole la contrainte de vérification « lots_a_type_dossiers_check »
DETAIL:  La ligne en échec contient (2, P, 1, null, null, 2). */
/* Lignes affectées: 0  Lignes trouvées: 0  Avertissements: 0  Durée pour 0 de 1 requête: 0,000 s. */

INSERT INTO "lots_a" ("id_dossiers", "type_dossiers", "id_lots", "id_avi") VALUES ('1', 'A', '1', '2'),('1', 'A', '1', '2');
/* ERREUR:  la valeur d'une clé dupliquée rompt la contrainte unique « lots_a_pkey »
DETAIL:  La clé « (id_dossiers, type_dossiers, id_lots)=(1, A, 1) » existe déjà. */

Dans cet exemple, pour un dossier de type A (couvrant donc exclusivement des animaux vivant) je ne peux rattacher de lot de type P (produit d'origine animale)

Vu l'heure tardive je ne m'attarderais pas plus, mais je pense tenir quelque chose

**fsmrel** · 21/02/2023, 14h33

Bonjour,

Je réponds rapidement parce que j’ai pas mal de fers au feu.

Le MCD a effectivement évolué...

Après un survol rapide de ma part, disons que côté SQL ça a l’air de tenir la route.

N.B. Vous avez mis au pluriel le nom des entités-types : par définition une entité-type est singulière, donc Dossiers n’est pas un nom correct, il faut le remplacer par Dossier. Même chose pour chaque entité-type.

A plus tard donc, et votez pour les réponses qui ont pu vous éclairer...

**CAymeric** · 21/02/2023, 20h36

Envoyé par fsmrel

Bonjour,

Je réponds rapidement parce que j’ai pas mal de fers au feu.

Le MCD a effectivement évolué...

Après un survol rapide de ma part, disons que côté SQL ça a l’air de tenir la route.

N.B. Vous avez mis au pluriel le nom des entités-types : par définition une entité-type est singulière, donc Dossiers n’est pas un nom correct, il faut le remplacer par Dossier. Même chose pour chaque entité-type.

A plus tard donc, et votez pour les réponses qui ont pu vous éclairer...

Bonsoir fsmrel,

Oui effectivement encore des erreurs de débutant sur les noms d'entités mais ça va bien finir par rentrer !

Au vu de ces échanges enrichissants je ne peut que "plusser" sur l'ensemble de vos messages !
J ai beaucoup appris ces dernieres semaines !

**fsmrel** · 22/02/2023, 16h55

Bonjour CAymeric,

Envoyé par CAymeric

Les contraintes au niveau des nomenclatures ont été simplifiées étant donné que ce sont des données statiques, uniquement éditables par l'administration de la future application.

Souhaitons une totale stabilité de ce système pour les siècles.

Cela dit, j’ai regardé de plus près : MCD, MLD, SQL : tout est correct.

Echanges sympathiques et productifs. Et merci pour les votes !

Y a plus qu’à !

**CAymeric** · 22/02/2023, 18h43

Envoyé par fsmrel

Bonjour CAymeric,

Souhaitons une totale stabilité de ce système pour les siècles.

Cela dit, j’ai regardé de plus près : MCD, MLD, SQL : tout est correct.

Echanges sympathiques et productifs. Et merci pour les votes !

Y a plus qu’à !

Bonsoir fsmrel,
Je marque donc notre discussion comme étant résolue !
Merci pour tout et au plaisir d'échanger sur de nouvelles problématiques 😋

Demande d'aide modélisation héritage

Looping

Discussions similaires

Partager

Partager