Discussion :

[Nerva]

Ces tables simplifiées Ne répondent pas aux règles de gestion initialement exprimées :

Tu as absolument raison, je n'ai pas pensé à le préciser. C'est parce que j'envisage une autre approche (pour un autre projet) où j'aurais là besoin que tous les internes figurent dans la liste, qu'ils aient un externe ou non (case NULL donc, quand ils n'en ont pas).

Et dans ce cas de figure :

- 1 interne peut avoir 0 ou 1 externe.
- 1 externe peut avoir 1 ou plusieurs internes (là encore, 1 externe n'existe que si il s'occupe d'au moins 1 interne).
- 1 interne ne peut devenir externe et 1 externe ne peut devenir interne.
- 1 partenariat interne/externe peut à tout moment se créer ou se terminer ; il suffit dans ce cas de vider la cellule de la colonne EXT_ID.

[tatayo]

- 1 partenariat interne/externe peut à tout moment se créer ou se terminer ; il suffit dans ce cas de vider la cellule de la colonne EXT_ID.

De mon point de vue c'est une mauvaise idée, car tu pers l'historique.
Pour ma part je mettrais une date de fin, qui reste NULL (ou qui prend une date de fin prévisionnelle) tant que le partenariat n'est pas terminé.

Tatayo.

[escartefigue]

Pour respecter les règles de gestion initiales et conserver l'historique tout en ayant la possibilité d'avoir ou non une association entre interne et externe, il faut mettre en œuvre le MCD que j'avais proposé dans la réponse n° 16 et que je publie de nouveau ici, avec la date de fin dans l'asso ternaire valorisée par défaut à 9999-12-31 pour les occurrences encore actives.




Et le DDL correspondant pour SQL server

Code SQL :

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
CREATE TABLE IND_individu(
   IND_ident INT IDENTITY,
   IND_nom VARCHAR(50) NOT NULL,
   IND_prenom VARCHAR(50) NOT NULL,
   IND_nir CHAR(13),
   PRIMARY KEY(IND_ident),
   UNIQUE(IND_nir)
);
 
CREATE TABLE INI_interne(
   IND_ident_int INT,
   INI_ddn DATE NOT NULL,
   INI_matricule CHAR(6),
   PRIMARY KEY(IND_ident_int),
   UNIQUE(INI_matricule),
   FOREIGN KEY(IND_ident_int) REFERENCES IND_individu(IND_ident)
);
 
CREATE TABLE STE_societe(
   STE_ident INT IDENTITY,
   STE_rsoc VARCHAR(128) NOT NULL,
   STE_siret CHAR(13) NOT NULL,
   PRIMARY KEY(STE_ident)
);
 
CREATE TABLE INE_externe(
   IND_ident_ext INT,
   STE_ident INT NOT NULL,
   PRIMARY KEY(IND_ident_ext),
   FOREIGN KEY(IND_ident_ext) REFERENCES IND_individu(IND_ident),
   FOREIGN KEY(STE_ident) REFERENCES STE_societe(STE_ident)
);
 
CREATE TABLE ENC_encadrer(
   IND_ident_int INT,
   CAL_date DATE,
   ENC_dtfin DATE NOT NULL default '9999-12-31',
   IND_ident_ext INT NOT NULL,
   PRIMARY KEY(IND_ident_int, CAL_date),
   FOREIGN KEY(IND_ident_int) REFERENCES INI_interne(IND_ident_int),
   FOREIGN KEY(IND_ident_ext) REFERENCES INE_externe(IND_ident_ext)
);

[escartefigue]

Et un petit script pour créer quelques cas de test et restituer la liste des employés, avec leurs éventuels prestataires encadrés toutes périodes confondues.

Code SQL :

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
insert into IND_individu (IND_nom, IND_prenom, IND_nir)
values ('Bogdanov', 'Sergeï', '1880276300200')
     , ('Abadie', 'Charlotte', '2921103120012')
     , ('Volfoni', 'Raoul', '1810813100005')
     , ('Zorglub', 'Bulgroz', '1760167110088')
     , ('Martin', 'Lucette', '2830944125125')
     , ('Le Gaulois', 'Asterix', '1900529106027')
;  
insert into INI_interne (IND_ident_int, INI_ddn, INI_matricule)
values (1, '1988-02-10', 'MAT001')
     , (2, '1992-11-22', 'MAT004')
     , (5, '1983-09-07', 'MAT005')
;
 
insert into STE_societe (STE_rsoc, STE_siret)
values ('Ducran, Lapoigne et Cie', '5554443330001')
     , ('Boucherie Sanzo', '1234567900012')
     , ('Foufourche et Papaye', '6668880000013')
;     
insert into INE_externe (IND_ident_ext, STE_ident)
values (3, 1)
     , (4, 3)
     , (6, 2)
;   
;
insert into ENC_encadrer (IND_ident_int, CAL_date, ENC_dtfin, IND_ident_ext)
values (1, '2018-01-04', '2018-11-30', 3)
     , (1, '2018-02-01', '2019-06-30', 4)
     , (2, '2018-01-04', '2020-12-30', 3)
;    
select II.IND_ident_int   as "id employé"
     , II.INI_matricule   as "matricule"
     , I1.IND_nom         as "nom employé"
     , EN.IND_ident_ext   as "id presta"
     , I2.IND_nom         as "nom presta"
     , EN.CAL_date        as "deb prs"
     , EN.ENC_dtfin       as "fin prs"
from       INI_interne  as II
inner join IND_individu as I1
   on I1.IND_ident = II.IND_ident_int
left outer join ENC_encadrer as EN
     inner join IND_individu as I2
        on I2.IND_ident = EN.IND_ident_ext
   on EN.IND_ident_int = II.IND_ident_int
order by "nom employé"
       , "deb prs"
;

Résultat :



L'employé Martin n'a jamais encadré de prestataire, d'où les marqueurs "null" dans la liste (à remplacer par COALESCE ou IFNULL si besoin)
Et le prestataire "Volfoni" a travaillé simultanément pour deux internes, comme autorisé dans la règle de gestion n°2

[Nerva]

@tatayo. Pour cette "variante", ne pas avoir d'historique n'a pas d'importance.

@escartefigue. Encore du boulot ! Je vais regarder ça. Rien qu'en voyant le MCD, je comprends que tu as repris la configuration du type et des sous-types. C'est ce qui m'avait paru le plus pertinent mais c'est une fois de plus sur la requête que ça me semblait compliqué.

[Nerva]

La requête plante.



Moteur HSQLDB. Il supporte tout type de jointure mais je me demande si ça ne serait pas la jointure inner qui semble être imbriquée dans la left outer qui pose un problème. J'ai essayé en mettant de parenthèses mais ça ne résout rien.

[escartefigue]

Bonjour,

La requête peut paraitre compliquée en première approche, mais elle est finalement assez simple.
J'explique dans mon blog ICI pourquoi il faut utiliser ce type de syntaxe quand on combine des jointures internes et externes.
Ajouter des parenthèses ne sert à rien dans ce cas, seule cette syntaxe permet de garantir le résultat. Je ne connais pas HSQLDB, mais s'il ne sait pas utiliser ce type de requêtes, c'est inquiétant, la plupart des SGBD l'acceptent (en tout cas tous ceux que j'ai déjà utilisés le permettent). J'espère qu'il n'y a pas d'autres limitations encore plus graves dans ce SGBD.

[Nerva]

J'ai fait une modification car il n'accepte pas les AS comme surnoms de tables (il suffit de ne rien mettre entre le nom et le surnom). Ça je le savais. Et là comme message d'erreur il renvoie un Unexpected token INNER requires ON in statement, ce que j'avais suspecté car encore jamais vu de jointure imbriquée dans une jointure. Je vais essayer de voir si il n'y a pas une syntaxe particulière pour ce moteur...

[escartefigue]

Il y avait toutefois une autre coquille dans la requête (je viens de la corriger dans le post concerné) : les noms d'alias doivent être encadrés de doubles quotes et non pas de quotes simples

[Nerva]

Il y avait toutefois une autre coquille dans la requête (je viens de la corriger dans le post concerné) : les noms d'alias doivent être encadrés de doubles quotes et non pas de quotes simples

Je les avais supprimés ainsi que les accents et remplacé les espaces par des soulignements.

[Nerva]

HSQLDB ne supporte pas les jointures externes imbriquées mais uniquement les internes imbriquées. Il semble qu'il y ait moyen de contourner le problème :

https://github.com/slick/slick/issues/1720

Je vais regarder ça...

[Nerva]

Ça fonctionne... presque !

Requête "adaptée" à HSQLDB selon les instructions du monsieur :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select II.IND_ident_int   as id_employe
     , II.INI_matricule   as matricule
     , I1.IND_nom         as nom_employe
     , EN.IND_ident_ext   as id_presta
     , I2.IND_nom         as nom_presta
     , EN.CAL_date        as deb_prs
     , EN.ENC_dtfin       as fin_prs
from       INI_interne   II
inner join IND_individu  I1 on I1.IND_ident = II.IND_ident_int
left outer join ENC_encadrer  EN
on EN.IND_ident_int = II.IND_ident_int
inner join IND_individu  I2
on I2.IND_ident = EN.IND_ident_ext
order by nom_employe, deb_prs

Exécution :



Je dis presque parce que je n'ai pas Martin, qui n'a pas de presta...

[escartefigue]

Eh oui... c'est la raison d'être de la syntaxe de jointure que j'ai communiquée et que j'explique dans mon blog.

[escartefigue]

Une solution de contournement pour obtenir aussi les employés n'ayant encadré aucun prestataire : deux requêtes, l'une pour ceux ayant encadré, l'autre pour ceux qui n'ont jamais encadré dont on fait l'union :

Code SQL :

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
select II.IND_ident_int   as "id employé"
     , II.INI_matricule   as "matricule"
     , I1.IND_nom         as "nom employé"
     , EN.IND_ident_ext   as "id presta"
     , I2.IND_nom         as "nom presta"
     , EN.CAL_date        as "deb prs"
     , EN.ENC_dtfin       as "fin prs"
from       INI_interne  as II
inner join IND_individu as I1
   on I1.IND_ident = II.IND_ident_int
inner join ENC_encadrer as EN
   on EN.IND_ident_int = II.IND_ident_int
inner join IND_individu as I2
   on I2.IND_ident = EN.IND_ident_ext
union all
select II.IND_ident_int   as "id employé"
     , II.INI_matricule   as "matricule"
     , I1.IND_nom         as "nom employé"
     , null               as "id presta"
     , null               as "nom presta"
     , null               as "deb prs"
     , null               as "fin prs"
from       INI_interne  as II
inner join IND_individu as I1
   on I1.IND_ident = II.IND_ident_int  
where not exists
     (select 1
      from ENC_encadrer as EN
      where EN.IND_ident_int = II.IND_ident_int
     )
order by "nom employé"
       , "deb prs"
;

C'est bien moins performant, mais faute de mieux

[Nerva]

On imagine la différence du temps de réponse avec une table comportant plusieurs dizaines ou centaines de milliers de lignes. Mais comme tu dis, c'est mieux que rien...