Discussion :

[Nerva]

Bonjour.

Soit une population d'individus. Il y a les internes, qui font partie d'un organisme (société, entreprise, administration, c'est sans importance) et les externes, qui sont des vacataires en partenariat avec des internes pour des missions temporaires.

- 1 interne peut avoir 0 ou 1 externe à la fois. Une date de début et de fin sont précisées à cet effet.
- 1 externe (qui n'existe que si il est missionné au moins 1 fois) peut être en partenariat avec 1 ou plusieurs internes, simultanément.

Voici la structure et un petit jeu de données :

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
CREATE TABLE T_INDIVIDUS (
	IND_ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
	IND_NOM VARCHAR (50) NOT NULL,
	IND_PRENOM VARCHAR (50) NOT NULL,
	CONSTRAINT PK_IND_ID PRIMARY KEY (IND_ID)
);
 
CREATE TABLE T_PARTENARIATS (
	IND_INT_ID INTEGER,
	IND_EXT_ID INTEGER NOT NULL,
	PAR_DATE_DEB DATE,
	PAR_DATE_FIN DATE,
	CONSTRAINT PK_IND_PAR PRIMARY KEY (IND_INT_ID, PAR_DATE_DEB),
	CONSTRAINT FK_IND_INT_ID FOREIGN KEY (IND_INT_ID) REFERENCES T_INDIVIDUS (IND_ID),
	CONSTRAINT FK_IND_EXT_ID FOREIGN KEY (IND_EXT_ID) REFERENCES T_INDIVIDUS (IND_ID)
);
 
INSERT INTO T_INDIVIDUS (IND_ID, IND_NOM, IND_PRENOM) VALUES (1, 'Talbot', 'Maribelle');
INSERT INTO T_INDIVIDUS (IND_ID, IND_NOM, IND_PRENOM) VALUES (2, 'Vidal', 'Pierre');
INSERT INTO T_INDIVIDUS (IND_ID, IND_NOM, IND_PRENOM) VALUES (3, 'Nemours', 'Doralice');
INSERT INTO T_INDIVIDUS (IND_ID, IND_NOM, IND_PRENOM) VALUES (4, 'Grimaud', 'Sarah');
INSERT INTO T_INDIVIDUS (IND_ID, IND_NOM, IND_PRENOM) VALUES (5, 'Vassent', 'David');
INSERT INTO T_INDIVIDUS (IND_ID, IND_NOM, IND_PRENOM) VALUES (6, 'Carpentier', 'Thomas');
INSERT INTO T_INDIVIDUS (IND_ID, IND_NOM, IND_PRENOM) VALUES (7, 'Lefort', 'Julie');
INSERT INTO T_INDIVIDUS (IND_ID, IND_NOM, IND_PRENOM) VALUES (8, 'Jodel', 'Karine');
INSERT INTO T_INDIVIDUS (IND_ID, IND_NOM, IND_PRENOM) VALUES (9, 'Montepin', 'Victor');
INSERT INTO T_INDIVIDUS (IND_ID, IND_NOM, IND_PRENOM) VALUES (10, 'Lasalle', 'Anne-Marie');
 
INSERT INTO T_PARTENARIATS (IND_INT_ID, IND_EXT_ID, PAR_DATE_DEB, PAR_DATE_FIN) VALUES (1, 5, '2025-02-15', '2025-03-07');
INSERT INTO T_PARTENARIATS (IND_INT_ID, IND_EXT_ID, PAR_DATE_DEB, PAR_DATE_FIN) VALUES (7, 8, '2025-03-27', NULL);
INSERT INTO T_PARTENARIATS (IND_INT_ID, IND_EXT_ID, PAR_DATE_DEB, PAR_DATE_FIN) VALUES (2, 10, '2025-04-02', '2025-04-29');
INSERT INTO T_PARTENARIATS (IND_INT_ID, IND_EXT_ID, PAR_DATE_DEB, PAR_DATE_FIN) VALUES (6, 10, '2025-04-18', '2025-04-24');
INSERT INTO T_PARTENARIATS (IND_INT_ID, IND_EXT_ID, PAR_DATE_DEB, PAR_DATE_FIN) VALUES (6, 8, '2025-04-30', NULL);

Je pense que la structure est correcte (association réflexive) mais je n'en suis pas certain.

Ce sujet porte sur une chose que je ne parviens pas à faire : une requête, qui affiche le nom des internes et de leurs externes, uniquement pour les partenariats en cours (date de fin NULL). Soit pour ce jeu de données :

Code :

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

1
2
3
4
| IND_INTERNE       | IND_EXTERNE  | PAR_DATE_DEBUT |
| ----------------- | ------------ | -------------- |
| Lefort Julie      | Jodel Karine | 2025-03-27     |
| Carpentier Thomas | Jodel Karine | 2025-04-30     |

Merci.

[tatayo]

Bonjour,
Comme la table T_INDIVIDUS doit apparaître 2 fois dans la requête, il faut utilise des alias.
Peux-tu nous montrer ce que tu as tenté ?

Tatayo.

P.S. par "convention" les noms de table sont au singulier, on se doute bien qu'il y a plusieurs individus et plusieurs partenariats dans la base

[Nerva]

Je ne suis pas allé bien loin puisque selon la structure et ce que j'imagine être du bon sens, il faut, récupérer le nom d'un interne en référence à son ID et aligner dans la colonne d'à côté le nom d'un externe dont l'ID est différent.

[tatayo]

Je ne vois pas trop où est la difficulté: il faut faire 2 jointures sur la table T_INDIVIDUS, comme je l'indiquais dans ma première réponse:

Code sql :

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

1
2
3
4
5
6
7
8
 
SELECT t1.IND_NOM,t2.IND_NOM,T_PARTENARIATS.PAR_DATE_DEBUT
from T_PARTENARIATS
inner join  T_INDIVIDUS as t1
	ON T1.IND_ID = T_PARTENARIATS.IND_INT_ID
inner join  T_INDIVIDUS as t2
	ON T1.IND_ID = T_PARTENARIATS.IND_EXT_ID
where T_PARTENARIATS.PAR_DATE_FIN is null

Tatayo.

[Nerva]

C'est un exemple typique où le surnommage des tables est obligatoire ? Je n'ai dû l'utiliser qu'une seule fois...

Alors la requête fonctionne mais ne retourne rien...

Edit : il y avait une petite erreur dans l'avant-dernière ligne : T1 à la place de T2. Là c'est ok...

[tatayo]

Pour ma part j'utilise les alias quasiment systématiquement, ainsi quand j'ajoute une jointure sur une table qui est déjà dans la requête, je n'ai pas de mauvaise surprises.

Et puis la plupart du temps ça rend la requête un peu plus lisible.

Tatayo.

[Nerva]

Parce que là j'étais parti encore dans des complications. J'aurais ajouté une colonne type dans la table des individus (1 pour interne et 2 pour externe), créé deux requêtes les séparant et essayé de les joindre dans une troisième.

Je me demande quand même si des relations type et sous-type ne seraient pas mieux adaptées dans ce cas de figure...

[tatayo]

Je laisse des experts en modélisation me corriger si besoin, mais pour ma part ici il n'y a pas lieu d'avoir 2 tables différentes.
Une colonne pour le type, pourquoi pas.

Tatayo.

[escartefigue]

Bonsoir Nerva, bonsoir à tous

C'est une très bonne chose de présenter les règles de gestion, car la bonne modélisation en dépend. Bravo

Le modèle tabulaire est correct, les deux tables sont bien requises, mais attention toutefois, vu que la table associative a pour PK l'identifiant de l'individu interne et la date de début il est possible d'enfreindre la règle de gestion 1 avec le type de cas suivant :

Id interne : 1    date début : 01-04-2025    date de fin 31-12-2025
Id interne : 1    date début : 02-04-2025    date de fin 31-12-2025

Comme les dates de début diffèrent, l'unicité de la PK est respectée, mais pas la règle de gestion 1
Il faudra donc contrôler grâce une UDF associée à une contrainte CHECK ou grâce à un trigger qu'on n'enfreint pas cette règle

[escartefigue]

Je reviens sur cette question :

Je laisse des experts en modélisation me corriger si besoin, mais pour ma part ici il n'y a pas lieu d'avoir 2 tables différentes.

La table issue de l'association n'est pas obligatoire en effet, mais elle présente l'avantage d'éviter d'avoir des colonnes "nullables" dans la table issue de l'entité-type [individu].
Selon les usages dans l'entreprise concernée, on génèrera ou pas cette table associative. Si elle est présente, alors on a certes une table de plus à utiliser, mais on n'a plus à se préoccuper de la logique trivaluée, toujours un peu plus délicate que la logique vrai/faux de base.
Voir mon blog ICI pour la table de vérité prenant en compte cette logique trivaluée


Quant à ce point précis :

Une colonne pour le type, pourquoi pas.

Une table unique pour tous les individus internes comme externes, avec ou sans un attribut "type" permettant de savoir si c'est un interne ou un externe est valide si tous les attributs des internes et des externes sont communs.
Si certains attributs sont spécifiques (matricule et date d'embauche pour les internes, lien avec la société de prestation pour les externes...) alors on pourra utiliser l'héritage.

[Nerva]

@tatayo. Merci pour la requête et le surnommage (j'y penserai à l'avenir). Pour ce qui est de la colonne type, elle peut être effectivement très utile à titre informatif : avec un très grand nombre d'individus, dans un formulaire, on voit instantanément qui est interne et qui est externe (plus d'autres usages SQL possibles).

@escartefigue. Telle quelle la structure est sans filet. Dans l'état actuel, rien n'empêche en effet d'affecter plus d'un externe à un interne simultanément (et même deux fois le même simultanément). Je vais essayer de privilégier des contraintes de table pour y remédier. Dans mon idée, il faudrait que l'affectation d'un nouvel externe ne soit possible que si une date de fin est saisie dans l'affectation précédente de l'interne.

Précision que j'ai oubliée : un interne peut avoir plusieurs fois le même externe.

[escartefigue]

Le terme exact plutôt que "surnommage" est "utilisation d'alias".

Il existe deux types d'alias : ceux déclarés à la volée comme dans l'exemple communiqué par Tatayo et les alias créés en tant qu'objets de la base de données qui eux sont persistants (objets créés par CREATE ALIAS).
Un alias peut s'appliquer à une table, une vue ou un autre alias et, pour les alias déclarés à la volée seulement, on peut les appliquer à une colonne.

Dans l'exemple ci-dessous :

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
-- création d'un alias pour l'automate d'exploitation sur la table des individus
create alias ROBOT.MA_TABLE1 for schema1.ma_table1
;
-- création d'un alias pour l'automate d'exploitation sur la table des entreprises
create alias ROBOT.MA_TABLE2 for schema2.ma_table2
;
-- 
select INDV.bearthdate as "Date Nais"
     , INDV.firstname  as "Prénom"
     , INDV.lastname   as "Nom"
     , ENTR.name       as "Société"
from  ROBOT.MA_TABLE1 as INDV
inner join 
      ROBOT.MA_TABLE2 as ENTR
   on ENTR.company_ident = INDV.company_ident
;

La création des objets alias ROBOT.MA_TABLE1 et ROBOT.MA_TABLE2 est une astuce courante en entreprise, elle permet d'avoir un même nom de schéma - ici "ROBOT" - pour toutes les tables utilisées par l'automate d'exploitation.
Ces objets alias sont eux mêmes "aliassé" INDV et ENTR pour les besoins de la requête et on a aussi des alias de colonne "Date Nais", "Prénom", "Nom" et "Société".

[Nerva]

Pour ce qui est de la terminologie, je me réfère toujours au (gros) dossier SQL de Frédéric Brouard où il parle de surnommage. Pour moi, un alias c'est avant tout un "AS" qui modifie l'intitulé d'une colonne de requête.

Dans mon cas, je me demande aussi si la clé primaire de T_PARTENARIATS ne devrait pas contenir également l'externe (IND_EXT_ID) puisque les deux individus sont concernés par ledit partenariat. Quant à la contrainte CHECK spécifique, je sèche. Il doit y avoir dedans un PAR_DATE_FIN IS NOT NULL pour interdire la création d'un "couple" (ou alors procéder inversement) mais je ne sais pas comment appliquer cette interdiction à une ligne d'un interne (IND_INT_ID) qui a toujours PAR_DATE_FIN marquée comme NULL.

[tatayo]

Pour ma part j'utilise aussi des alias persistants pour des tables provenant de serveurs liés, ce qui simplifie grandement les requêtes qui "tapent" sur deux bases en même temps.

Tatayo

[escartefigue]

Plutôt que de gérer des dates de fin marquées "null" pour le cas où la période est active, je préfère utiliser une colonne "not null with default" dont la valeur par défaut est "9999-12-31".
Ainsi plus besoin de tester le marquage "null" ni d'utiliser la logique trivaluée, toujours assez délicate.

[escartefigue]

Voici une représentation utilisant la spécialisation des individus :



Création d'un surtype [individu] pour tous les attributs communs aux internes et externes, d'un sous-type [interne] comportant le matricule employé unique et la date de naissance et d'un sous-type [externe] en association avec la société qui missionne cet externe.

Création d'une association ternaire (encadrer) faisant intervenir une entité-type dite "fictive" [calendrier]. "Fictive" dans le sens où elle ne deviendra pas une table, elle ne sert qu'à faire participer la date à la PK de la table issue de l'association. C'est parce que j'ai cliqué sur la case à cocher "fictive" dans Looping que le MCD fait apparaitre le nom de l'entité entre parenthèses et que le DDL n'inclura pas cette table.

Au niveau de l'association (encadrer) la flèche est une représentation simplifiée de la contrainte selon laquelle à une date, un interne ne peut encadrer qu'un seul externe.
La conséquence est que l'identifiant de l'externe est évacué de la PK de la table associative, il n'est conservé que comme simple attribut.

Voici le DDL produit par Looping avec ce MCD après avoir choisi SQL server comme SGBD :

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

Pour associer une UDF à une contrainte CHECK, il y a un article de F. Brouard ICI

Et quant au terme "surnommage", il n'existe ni dans la langue française, ni - à ma connaissance - dans le jargon informatique

[Nerva]

Plutôt que de gérer des dates de fin marquées "null" pour le cas où la période est active, je préfère utiliser une colonne "not null with default" dont la valeur par défaut est "9999-12-31". Ainsi plus besoin de tester le marquage "null" ni d'utiliser la logique trivaluée, toujours assez délicate.

Je ne comprends pas du tout...

-----

Type et sous-type, c'est généralement la façon que j'ai de faire les choses dès qu'il y a une population commune (ici les employés humains de la boîte, dont certains sont salariés et d'autres vacataires). Je vais voir ce que donne ta structure, qui ne doit pas être loin de ce que j'aurais testé si je ne m'étais pas lancé dans une association réflexive.

HS
Surnommage. Souligné en rouge. Vu la première fois dans la documentation de F Brouard. Mais bon, vu tous les mots qui n'ont aucune base linguistique, grammaticale ou autre, qu'on nous pond à longueur d'année, je pourrais très bien être amené à utilisé ce terme, qui a une construction parfaitement cohérente, au même titre que surchauffe, suralimentation, surconsommation, surcharge, dans un autre contexte que le SQL.

[tatayo]

Bonjour,
Voici un "petit papier" de SqlPro qui explique les tenants et aboutissants de la gestion des NULL en SQL.

Tatayo.

[Nerva]

Bonjour,
Voici un "petit papier" de SqlPro qui explique les tenants et aboutissants de la gestion des NULL en SQL.
Tatayo.

Je connais ce document, intéressant (tout comme le lien indiqué par escartefigue), mais les NULL ne me préoccupent pas dans ce cas de figure : la date de fin d'un partenariat n'est remplie que lorsque ledit partenariat est terminé (et surtout pas en estimant cette fin à l'avance). C'est là dessus que je me base pour définir si un interne peut ou non entamer un nouveau partenariat avec un externe.

Mais puisque tu parles des NULL, il y a malgré tout (en dehors de ce sujet) une question à laquelle je n'ai jamais trouvé de réponse convaincante : pourquoi les fondateurs du SQL (et ceux qui ont pris la relève) ont défini et "autorisé" les valeurs vides ('') alors que les NULL peuvent parfaitement s'y substituer ? Y a-t-il un cas de figure précis où un NULL ne peut remplacer un vide et vice-versa ? Parce que pour moi :

Usage académique : ('Dupont', NULL, 'Jean')
Ne devrait pas exister : ('Dupont', '', 'Jean')

[escartefigue]

Null et vide ne sont pas du tout la même chose.

Vide est une valeur, la valeur vide et elle ne s'applique qu'aux colonnes de type char (char, varchar, nchar, nvarchar).
Dans la norme bancaire EMV par exemple, la valeur vide est valide pour certains champs des enregistrements de paiement par carte.

Null est une absence de valeur, ça signifie que la valeur est inconnue. C'est un marqueur applicable à tout type de colonne.

Attention toutefois à Oracle qui traite de façon identique null et vide.

Voir mon blog sur ce sujet.