Discussion :

[laurentSc]

Bonjour,

j'ai cette règle de gestion :

R014 - Une application a un autre nom, dit "produit" qui désigne la même chose.

Ce que je pense : elle va induire la création d'une table SQL de correspondance matching_appli_product(appli_name, product_name), par contre, aucun effet dans le MCD (du moins, je n'en vois aucun). OK ?

[escartefigue]

Bonjour Laurent

Nous avons déjà discuté de ce sujet.

Soit application et produit sont des synonymes, il y a bijection entre l'un et l'autre.

Dans ce cas, ce qui est fortement recommandé est de ne conserver qu'un seul des deux termes. Le dictionnaire de données mentionnera les deux en mentionnant que l'un est abandonné au bénéfice de l'autre.
Si toutefois, pour une raison quelconque, les deux doivent être maintenus, alors une table supplémentaire n'est pas utile, la table APPLICATION contiendra à la fois la colonne AP_nom et PR_nom (nom de l'application et nom du produit)

Soit application et produit ne sont pas des synonymes, en ce cas, la règle n'est pas celle formulée dans R014, elle peut être par exemple
R014 - une application correspond à zéro ou plusieurs produits et un produit correspond à au plus une application
ou bien
R014 - une application correspond à zéro ou plusieurs produits et un produit correspond à au moins une application

Dans le premier cas, la table supplémentaire est facultative, on peut aussi choisir d'avoir une clef étrangère "nullable" dans la table produit
Dans le deuxième cas, relation n-n oblige, la table supplémentaire est incontournable

[laurentSc]

Nous avons déjà discuté de ce sujet.

Je m'en souviens bien, mais vos réponses précédentes ne m'ont pas empêché de me poser encore des questions.

Soit application et produit sont des synonymes, il y a bijection entre l'un et l'autre.

C'est le cas.

Dans ce cas, ce qui est fortement recommandé est de ne conserver qu'un seul des deux termes. Le dictionnaire de données mentionnera les deux en mentionnant que l'un est abandonné au bénéfice de l'autre.
Si toutefois, pour une raison quelconque, les deux doivent être maintenus, alors une table supplémentaire n'est pas utile, la table APPLICATION contiendra à la fois la colonne AP_nom et PR_nom (nom de l'application et nom du produit)

Déjà, oui, il faut conserver les 2 noms, car l'utilisateur doit pouvoir les voir tous les 2.
Par contre, s'il n'y a pas de table supplémentaire, je ne vois pas comment traiter le scénario suivant :

Tout d'abord, la table AP_application a la structure suivante :

Code sql :

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

1
2
3
4
5
6
7
8
CREATE TABLE AP_application(
   PL_platform VARCHAR(30),
   AP_product_name VARCHAR(30),
   AP_application_name VARCHAR(30) NOT NULL,
   PRIMARY KEY(PL_platform, AP_product_name),
   UNIQUE(AP_application_name),
    FOREIGN KEY(PL_platform) REFERENCES PL_platform(PL_platform)
);

Dans mon raisonnement, pas la peine de tenir compte de la FK.

1- La bdd est vide
2- on lit un premier enregistrement concernant une licence, donc avec un nom d'application
3- on entre dans la table AP_application un enregistrement avec un attribut AP_application_name.
4- comme il n'y a pas de table de correspondance, pas possible de renseigner la colonne AP_product_name. Donc on lui met NULL (valeur par défaut). Mais impossible, vu qu'elle appartient à la PK. Donc, qu'elle est votre solution ?

Si on rajoute une colonne AP_identqui servira de PK, on pourra bien mettre NULL dans AP_product_name, mais alors, on va bloquer sur ce second scénario :

5- on lit un enregistrement concernant un ticket
6- son synonyme (colonne AP_application_name) est déjà présent dans la bdd, mais impossible de le savoir en l'absence d'une table de correspondance. Donc création d'une 2e ligne où on renseigne la colonne AP_product_name et où on met NULL dans la colonne AP_application_name. Mais on se retrouve avec 2 lignes dans la table au lieu d'une seule. Donc, qu'elle est votre solution ?

[escartefigue]

Si, même de façon transitoire, on peut avoir une application sans correspondance avec un produit, alors il ne s'agit pas d'une bijection.

Ma réponse précédente s'applique, on est dans le deuxième cas, on peut créer une table de correspondance à alimenter quand cette correspondance est connue.
On est ici visiblement dans le cas d'une nomenclature interne en correspondance avec une nomenclature externe, mais en ce cas, attention, il y a souvent des cas 1-n ou 0-n.

[laurentSc]

A mon avis, la règle de gestion est :

R014- une application correspond à 0 ou 1 produit et un produit correspond à 0 ou 1 application.

Je me demande comment traduire ça au niveau MCD et qu'est-ce que ça donnera au niveau SQL...

[escartefigue]

Le modèle le plus évolutif est [APPLICATION] 0,1 --- correspondre --- 0,1[PRODUIT] en optant pour la génération d'une table de correspondance
L'avantage de ce choix est d'éviter les attributs "null" dans la table APPLICATION (ce qui n'est pas très gênant en soi), mais aussi de permettre une gestion plus souple de la correspondance entre l'une et l'autre terminologie.
Avec cette table, si un jour il y a plusieurs produits qui correspondent à une même application, c'est indolore au niveau traitement puisque les tables restent les mêmes.

[laurentSc]

Ce modèle me plaît ; je l'ai créé dans Looping et le SQL généré est :

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
CREATE TABLE AP_application(
   AP_ident INT UNSIGNED AUTO_INCREMENT,
   AP_application_name VARCHAR(20) NOT NULL,
   PRIMARY KEY(AP_ident),
   UNIQUE(AP_application_name)
);
 
CREATE TABLE PR_product(
   PR_ident INT UNSIGNED AUTO_INCREMENT,
   PR_product_name VARCHAR(20) NOT NULL,
   PRIMARY KEY(PR_ident),
   UNIQUE(PR_product_name)
);
 
CREATE TABLE corresponds(
   AP_ident INT UNSIGNED,
   PR_ident INT UNSIGNED NOT NULL,
   PRIMARY KEY(AP_ident),
   UNIQUE(PR_ident),
   FOREIGN KEY(AP_ident) REFERENCES AP_application(AP_ident),
   FOREIGN KEY(PR_ident) REFERENCES PR_product(PR_ident)
);

Si par exemple, à partir d'un AP_application_name donné, je veux trouver le PR_product_name :

Code sql :

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

1
2
3
4
5
 
SELECT PR_product_name from PR_product WHERE (
SELECT PR_ident from corresponds WHERE (
SELECT AP_ident from AP_application WHERE AP_application_name='nom_application'
) );

testé !

[escartefigue]

Bonjour Laurent

Testé, mais pas approuvé : cette syntaxe est erronée

Voici comment rechercher le ou les produits correspondants aux applications.

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
select AP.AP_ident
     , AP.AP_application_name
     , PR.PR_ident
     , PR.PR_product_name
from AP_application AP
left join
     PA_corresponds PA
     inner join PR_product PR
        on PR.PR_ident=PA.PR_ident
  on PA.AP_ident=AP.AP_ident
order by AP.AP_application_name
       , PR.PR_product_name
;

Si on recherche pour une application particulière, il suffit d'ajouter une restriction WHERE sur l'application.
Ici j'ai utilisé une jointure externe pour avoir la liste de toutes les applis, qu'il y ait ou non correspondance.
Pour une recherche de correspondance sur une appli précise, on utilisera le plus souvent une jointure interne, qui ne renverra aucun résultat si la correspondance n'existe pas.

[laurentSc]

Merci pour la réponse, mais mes connaissances limitées en SQL font que je suis largué

Déjà, dans votre requête, une jointure interne est "imbriquée" dans une jointure externe (guillemets car j'ai lu que ce terme n'est pas employé pour les SGBDR) ; je connais pas ; est-ce équivalent à une autre requête avec seulement des jointures simples ?

Vous distinguez Si on recherche pour une application particulière et Pour une recherche de correspondance sur une appli précis : c'est quoi la différence ? Selon moi, le seul objectif est la 2e expression...

Si je teste votre requête, elle donne (pas réussi à mettre une bordure)

AP_ident	AP_application_name	PR_ident	PR_product_name
1	appli1	NULL	NULL

Comment se fait-il que les 2 dernières colonnes soient à NULL alors que les 2 tables sont renseignées ?

Code sql :

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

1
2
INSERT INTO `ap_application` VALUES (1,'appli1');
INSERT INTO `pr_product` VALUES (1,'prod1');

Pourquoi la syntaxe de ma requête est incorrecte alors qu'elle me donne le résultat attendu ?

[escartefigue]

Je reprends votre script de création des tables (j'ai seulement ajouté un préfixe PA pour la table associative) :

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
CREATE TABLE AP_application(
   AP_ident INT UNSIGNED AUTO_INCREMENT,
   AP_application_name VARCHAR(20) NOT NULL,
   PRIMARY KEY(AP_ident),
   UNIQUE(AP_application_name)
);
 
CREATE TABLE PR_product(
   PR_ident INT UNSIGNED AUTO_INCREMENT,
   PR_product_name VARCHAR(20) NOT NULL,
   PRIMARY KEY(PR_ident),
   UNIQUE(PR_product_name)
);
 
CREATE TABLE PA_corresponds(
   AP_ident INT UNSIGNED,
   PR_ident INT UNSIGNED NOT NULL,
   PRIMARY KEY(AP_ident),
   UNIQUE(PR_ident),
   FOREIGN KEY(AP_ident) REFERENCES AP_application(AP_ident),
   FOREIGN KEY(PR_ident) REFERENCES PR_product(PR_ident)
);

J'y insère un petit jeu d'essai : 3 applis, 2 produits et 2 liens

Code SQL :

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

1
2
3
4
5
6
7
8
9
insert into AP_application (AP_application_name)
values ('appli 1'), ('appli 2'), ('appli X')
;
insert into PR_product(PR_product_name)
values ('Produit A'), ('Produit B')
;
insert into PA_corresponds (AP_ident, PR_ident)
values (1,2), (3, 1)
;

Je vérifie le contenu de chaque table :

Code SQL :

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

1
2
3
4
5
6
select * from AP_application
;
select * from PR_product
; 
select * from PA_corresponds
;

Le contenu est conforme aux insertions :

AP_ident	AP_application_name
1	appli 1
2	appli 2
3	appli X

PR_ident	PR_product_name
1	Produit A
2	Produit B

AP_ident	PR_ident
3	1
1	2

Si j'essaye votre requête, il y a erreur de syntaxe, pas de résultat
Si j'essaye la mienne que je recopie ici :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
select AP.AP_ident
     , AP.AP_application_name
     , PR.PR_ident
     , PR.PR_product_name
from AP_application AP
left join
     PA_corresponds PA
     inner join PR_product PR
        on PR.PR_ident=PA.PR_ident
  on PA.AP_ident=AP.AP_ident
order by AP.AP_application_name
       , PR.PR_product_name
;

J'obtiens le résultat attendu :

AP_ident	AP_application_name	PR_ident	PR_product_name
1	appli 1	2	Produit B
2	appli 2	null	null
3	appli X	1	Produit A

Appli 2 renvoie "null" puisqu'aucun produit n'a été mis en correspondance

Pour comprendre la syntaxe particulière de la jointure, il faut consulter l'article de mon blog consacré aux combinaisons de jointure INNER et OUTER ici