Discussion :

[laurentSc]

Bonjour,
je cherche une requête SELECT qui répondrait à la question du titre. En fait, c'est assez compliqué car ça implique 4 tables (d'où, je pense, 3 jointures dans la requête).
Tout d'abord, le USER émet un "ticket" sur un produit (pour signaler un bug)(les produits sont des logiciels) ; ce ticket est stocké en bdd et la structure de la table ticket 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
CREATE TABLE `ticket` (
  `id_ticket` varchar(15) COLLATE utf8_bin NOT NULL,
  `product_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `assigned_group` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `submitted_date` date DEFAULT NULL,
  `last_resolved_date` date DEFAULT NULL,
  `summary` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `priority` varchar(6) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `status` varchar(13) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `submitter_SESA` int DEFAULT NULL,
  `customer_SESA` int DEFAULT NULL,
  `type_incident` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `source` varchar(12) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `first_country` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id_ticket`),
  KEY `FK_customer_SESA` (`customer_SESA`),
  CONSTRAINT `FK_customer_SESA` FOREIGN KEY (`customer_SESA`) REFERENCES `customer` (`customer_SESA`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Ce que je nomme "user" est dans cette table la colonne customer_SESA (le SESA est l'identifiant du user) et "produit" est dans la colonne product_name.
On veut donc savoir si ce "user" a une licence. Celles-ci sont stockées dans la table license :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE `license` (
  `sesa` int NOT NULL,
  `application_key` smallint NOT NULL,
  `currently_active` tinyint(1) DEFAULT NULL,
  `activate_date` date DEFAULT NULL,
  `deactivate_date` date DEFAULT NULL,
  `first_country` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`sesa`,`application_key`) USING BTREE,
  KEY `FK_APPLICATION` (`application_key`),
  CONSTRAINT `FK_APPLICATION` FOREIGN KEY (`application_key`) REFERENCES `application` (`application_key`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Comme on peut le voir, celle-ci a une clé étrangère sur la table application : CONSTRAINT `FK_APPLICATION` FOREIGN KEY (`application_key`) REFERENCES `application` (`application_key`) ON DELETE CASCADE ON UPDATE CASCADE dont la structure est :

Code sql :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE `application` (
  `application_key` smallint NOT NULL AUTO_INCREMENT,
  `application_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `platform` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `platform_owner` bigint DEFAULT NULL,
  `publisher` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`application_key`),
  UNIQUE KEY `UK_appli_name` (`application_name`) /*!80000 INVISIBLE */
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Le nom de l'application est donc stocké dans cette table. Dernière difficulté : le nom product_name de la table ticket diffère du nom application_name de la table application. J'ai donc créé la table matching_ticket_license pour lister les correspondances :

Code sql :

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

1
2
3
4
CREATE TABLE `matching_ticket_license` (
  `product_name` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  `application_name` varchar(30) COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Y a plus qu'à trouver une requête impliquant ces 4 tables pour répondre à la question du titre. Voici ce que j'ai essayé :

Code sql :

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

SELECT l.sesa FROM license l JOIN application a ON a.application_key=l.application_key JOIN matching_ticket_license m ON m.application_name=a.application_name JOIN ticket t ON t.customer_SESA=l.sesa where t.customer_SESA='3016'

Mais le résultat est vide alors que ici la colonne product_name de la table ticket vaut "AUTOCAD" et que selon la table license, le user "3016" a bien une licence sur "AUTOCAD". Où est mon erreur, SVP ?

[escartefigue]

Bonjour,

Voilà un modèle de données inutilement compliqué.

Un modèle de données compliqué implique des requêtes compliquées, peu performantes et peu fiables.

Est-ce une BDD en cours de développement ou bien elle est déjà en production ?
Si ce n'est pas trop tard, il faut revoir complètement ce modèle de données en commençant par un modèle conceptuel, ça évitera bien des erreurs, à commencer par des noms de FK différents du nom de la PK dont elles héritent, des contraintes manquantes et autres redondances telles que la présence du nom du produit dans la table ticket.

Remarques

• présenter avec soin les requêtes en facilite la compréhension. Un retour à la ligne sur chaque clause est le bienvenu.
• vous pouvez vous débarrasser des quotes inversées quand les noms d'objets ne sont pas des noms réservés, là aussi, ça facilite la lecture
• choisir un varchar(15) comme PK de la table ticket est une hérésie

[laurentSc]

Merci pour ces remarques.

Non, cette bdd n'est pas encore en production. Par contre, la modifier signifie aussi modifier les requêtes donc le code qui la gère, ce qui me tente guère. Mais bon, la première étape serait d'en être capable. Mon logiciel pour la gérer est MySQL Workbench et j'ai constaté hier matin, que plus moyen d'y accéder : https://www.developpez.net/forums/d2...sql-workbench/. Si vous avez une idée...Pour redémarrer Workbench, j'ai tenté de suivre http://salihayacoub.com/420Ke9/Mysql...e1/Seance1.pdf mais ça échoue à l'étape 3 (création d'un user) : il veut pas de mon mot de passe et je pense avoir respecté la règle...

[escartefigue]

En attendant de corriger votre modèle, voici une requête qui devrait fonctionner

J'ai utilisé des jointures OUTER car on voit qu'il manque plusieurs contraintes FK entre les tables, on peut donc douter de l'intégrité des données
(par exemple la table matching_ticket_license ne référence rien alors que c'est une table associative )
D'ailleurs pourquoi avoir créé cette table associative en utilisant le libellé de l'application plutôt que sa clef ? derechef !

Et aussi, pourquoi déclarer le jeu de caractère et la collation sur chaque colonne alors que ce sont toujours les mêmes ?

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
select TI.id_ticket         as num_ticket
     , TI.product_name      as produit_ticket
     , MA.application_name  as application     
     , TI.customer_SESA     as num_client
     , CU.customer_name     as nom_client
     , LI.sesa              as num_license
     , case LI.currently_active
            when 1 then 'active'
            else 'inactive'
       end as val_license
from ticket TI
inner join customer CU
   on CU.customer_SESA=TI.customer_SESA
left join matching_ticket_license MA
   on MA.product_name=TI.product_name
left join application AP
   on AP.application_name = MA.application_name
left join license LI
   on LI.application_key=AP.application_key

Puisque la base n'est pas en production, vous avez grandement intérêt à corriger le modèle avant d'aller plus loin !
Le modèle de données c'est comme les fondations d'une maison, quand c'est instable, les murs se lézardent, on met des renforts partout et finalement ça s'effondre.
Modifier des requêtes ce n'est pas beaucoup de travail, alors que refaire le modèle une fois qu'il est en prod, ça revient à tour recommencer à zéro, ca coute la peau des c(bip)

Vous pouvez vous faire aider sur la modélisation en posant vos question ici :
https://www.developpez.net/forums/f6...sation/schema/

EDIT : MySQL workbench a le mérite d'exister mais le grand tort d'attaquer directement la modélisation par les tables, or, c'est exactement ce qu'il ne faut pas faire !
Un modèle de données bien conçu commence par le modèle conceptuel. Oubliez MySQL workbench et utilisez un logiciel de modélisation gratuit et sérieux, il y en a quelques uns téléchargeables sur le web (dont looping que j'utilise).

[laurentSc]

Oubliez MySQL workbench et utilisez un logiciel de modélisation gratuit et sérieux, il y en a quelques uns téléchargeables sur le web (dont looping que j'utilise).

En vous lisant, j'ai vu que Looping est plébiscité et qu'ils viennent de créer un forum qui lui est consacré : https://www.developpez.net/forums/f2...utils/looping/ par contre, pas trouvé de tuto pour démarrer ni de lien pour télécharger ce logiciel. Pouvez-vous me conseiller ?

[escartefigue]

Pour le téléchargement c'est ici : https://www.looping-mcd.fr/

L'utilisation est très intuitive, il ne faut pas hésiter à poser des questions dans le cadre de la modélisation si besoin

[laurentSc]

Merci pour le lien, mais :

Nous avons détecté un risque de sécurité.
In order to give priority to Internet access for professional use, as well as a part of our policy to master expenses of network facilities, we have decided to block the access for the following internet categories
Pornography, Adult Content, Nudity, Hacking, Illegal, Racism, Hate or violence, Phishing

Please, contact your usual IT Support (29 29 or your local IT Manager) if this site is mandatory for your business.
Vous avez tenté de consulter: https://www.looping-mcd.fr/Looping.exe
Menace détectée: Virus

et idem si je désactive la protection...

[escartefigue]

Très curieux sans doute votre serveur d'entreprise qui est très restrictif, car à mon boulot comme à mon domicile je n'ai pas ce souci.

[isabelle.letrong]

Un modèle de données bien conçu commence par le modèle conceptuel. Oubliez MySQL workbench et utilisez un logiciel de modélisation gratuit et sérieux).

Hum, certes mais pour citer l'excellent document 'Modéliser les données avec MySQM Workbench', de fsmrel (François de Sainte Marie) à qui l'on ne peut vraiment pas faire le procès de prendre des raccourcis avec la modélisation et la théorie :


A l'actif de Workbench il ne faut pas non plus perdre de vue que c'est l'outil par excellence qui permet d'intégrer des routines stockées qui ne soient pas limitées à 5 lignes de code qui se battent en duel....

[escartefigue]

Tout à fait, mais François (Fsmrel) est expert en modélisation des bases de données, il sait éviter les pièges dans lesquels les béotiens tombent régulièrement en attaquant directement le sujet avec le modèle tabulaire.

Passer par l'étape MCD n'est pas une solution miracle, en particulier si on manque d'expertise, mais c'est d'un grand secours et c'est fortement recommandé, surtout si on manque de maîtrise dans la modélisation.

[laurentSc]

Je l'ai téléchargé sans problème sur mon PC perso, puis ai transféré le fichier sur un disque dur externe (> 5 mO quand même). J'ai ainsi pu le télécharger sur mon PC pro (car évidemment, je suis à la maison (pas été travailler sur le site depuis octobre...)), mais quand je le lance (c'est un .exe), ça mouline indéfiniment...

[Paprick]

Bonjour,

Je l'ai téléchargé sans problème sur mon PC perso, puis ai transféré le fichier sur un disque dur externe (> 5 mO quand même). J'ai ainsi pu le télécharger sur mon PC pro (car évidemment, je suis à la maison (pas été travailler sur le site depuis octobre...)), mais quand je le lance (c'est un .exe), ça mouline indéfiniment...

Looping vérifie au lancement s'il existe une nouvelle version (ce qui sera d'ailleurs le cas dès demain !). Il est possible que votre firewall ou antivirus bloque l'exécution à cause de cette vérification...
Je ne vois rien d'autre, le logiciel étant utilisé sans problème par des centaines d'utilisateurs tous les jours sans le moindre problème.
L'avez-vous lancé sur votre PC perso ?

[laurentSc]

L'avez-vous lancé sur votre PC perso ?

Oui (à l'instant) ; ça marche très bien. Pouvez-vous créer une version de ce logiciel qui ne comporterait pas cette vérification ?

il sait éviter les pièges dans lesquels les béotiens tombent régulièrement en attaquant directement le sujet avec le modèle tabulaire.

Passer par l'étape MCD n'est pas une solution miracle, en particulier si on manque d'expertise, mais c'est d'un grand secours et c'est fortement recommandé, surtout si on manque de maîtrise dans la modélisation.

D'après la définition ci-dessus, je suis bien un béotien ne sachant même pas ce qu'est un MCD (première étape : je vais me documenter) et d'habitude, je fais directement le modèle tabulaire...

[escartefigue]

D'après la définition ci-dessus, je suis bien un béotien ne sachant même pas ce qu'est un MCD (première étape : je vais me documenter) et d'habitude, je fais directement le modèle tabulaire...

En ce cas, un petit tour dans le forum ad hoc communiqué dans ma réponse n°4 serait sans doute bénéfique

[isabelle.letrong]

Passer par l'étape MCD n'est pas une solution miracle, en particulier si on manque d'expertise, mais c'est d'un grand secours et c'est fortement recommandé, surtout si on manque de maîtrise dans la modélisation.

Si la réalisation d'un MCD permet de faire de beaux rectangles connectés via des de belles patates, qu'il facilite le dialogue avec les fonctionnels. il n'en reste pas moins que pour être exploitable sans être la source de problèmes futurs, il doit obéir à certaines règles, justement issues de la modélisation relationnelle. Par exemple, la validation d'une relation ternaire (3 rectangles connectés à une patate) , bidule que l'on trouve couramment dans un MCD, se fait concrètement à l'aune du respect de la 4ème forme normale. Reconnaissons que la 4NF n'est pas vraiment la tasse de thé des béotiens…

En réalité, même s'il parait plus simple de dessiner des rectangles et des patates, la production d'un MCD correct n'est pas plus à la portée d'un béotien que la production directe d'un MLD normalisé ....
Donc, de mon point de vue, rien de très choquant à partir bille en tête sur un MLD avec MySQL Workbench, et pourquoi pas, de réfléchir à coder le cœur pérenne de gestion de l'application sous la forme de routines stockées puisque l'outil le permet, mais ceci est une autre histoire…

[Paprick]

Bonsoir,

Si la réalisation d'un MCD permet de faire de beaux rectangles connectés via des de belles patates, qu'il facilite le dialogue avec les fonctionnels. il n'en reste pas moins que pour être exploitable sans être la source de problèmes futurs, il doit obéir à certaines règles, justement issues de la modélisation relationnelle. Par exemple, la validation d'une relation ternaire (3 rectangles connectés à une patate) , bidule que l'on trouve couramment dans un MCD, se fait concrètement à l'aune du respect de la 4ème forme normale. Reconnaissons que la 4NF n'est pas vraiment la tasse de thé des béotiens…

Il me parait quelque peu excessif de considérer qu'un MCD se limite à la production de beaux rectangles connectés à de jolies patates...
Son pouvoir d'expression au niveau conceptuel à travers des notions comme l'identification relative, les contraintes d'intégrité fonctionnelles (CIF), les contraintes inter-associations (inclusion, exclusion mutuelle, ...), l'héritage (spécialisation, généralisation), etc... permettent une modélisation du système d'information qui peut parfaitement prendre en compte la problématique relationnelle, tout en offrant une représentation bien plus claire qu'un MLD qui ramène immédiatement tous ces concepts à des tables reliées par des clés.
Et, même si je suis d'accord avec le fait qu'il n'est pas à la portée de tout béotien de produire des MCD complets (et parfois complexes), cela n'en reste pas moins facilité par les nombreux outils disponibles en phase de conception offrant un pouvoir d'expression bien plus important qu'un MLD, tout en gommant certains aspects techniques qui polluent bien trop souvent une bonne définition du modèle.

[laurentSc]

Bonsoir,

je viens de réussir à restaurer MySQL WorkBench (le reverse engineering m'a sauvé). Donc, comme je ne peux utiliser Looping sur le PC du boulot, je vais continuer avec cet outil. Néanmoins, la première étape reste de me former au MCD puis de prendre en compte vos remarques aux posts #2 et #4.

[escartefigue]

Bonjour,

Il me parait quelque peu excessif de considérer qu'un MCD se limite à la production de beaux rectangles connectés à de jolies patates...

Je ne peux qu'abonder dans le sens des propos de Paprick !
J'invite Isabelle.Letrong à visiter le forum consacré à la modélisation, de nombreux sujets variés y ont trouvé réponse et on y voit souvent pour ne pas dire toujours, l'importance de la phase conceptuelle.

[isabelle.letrong]

J'invite Isabelle.Letrong à visiter le forum consacré à la modélisation

Je donne des formations de modélisation Merise et UML, donc je connais bien l'importance de la modélisation...

Je voulais insister sur 2 points :
- La réalisation d'un MCD correct, même si elle traduit avec plus de facilités les échanges avec les fonctionnels, n'est en réalité pas plus à la portée d'un débutant que celle d'un MLD.
- Qu'on ne peut pas jeter MySQL Workbench tout simplement parce qu'il ne traite pas le niveau MCD. Cet outil a bien des qualités qu'il faut savoir utiliser lorsqu'on travaille avec MySQL et que l'on souhaite utiliser toute les capacités de son moteur relationnel.

[Paprick]

Bonjour,

La réalisation d'un MCD correct, même si elle traduit avec plus de facilités les échanges avec les fonctionnels, n'est en réalité pas plus à la portée d'un débutant que celle d'un MLD.

Désolé d'insister, mais un MCD n'a pas seulement vocation à faciliter les échanges avec les fonctionnels : il permet une conception précise et une représentation fidèle de la façon dont le schéma relationnel sera structuré au niveau logique. Ce n'est certes pas à la portée d'un débutant de concevoir immédiatement des modèles complexes, mais le problème de l'approche directe au niveau du MLD est qu'elle prive le concepteur d'outils essentiels, et est source d'une mauvaise conception car trop souvent focalisée sur la relation logique entre les tables (liens entre clés primaires et clé étrangères). Sachant que le passage du MCD au MLD est parfaitement assuré par les principaux outils de modélisation, il est dommage de se priver de cette étape.

Qu'on ne peut pas jeter MySQL Workbench tout simplement parce qu'il ne traite pas le niveau MCD. Cet outil a bien des qualités qu'il faut savoir utiliser lorsqu'on travaille avec MySQL et que l'on souhaite utiliser toute les capacités de son moteur relationnel.

Bien sûr que Workbench a de nombreuses qualités qui méritent d'être étudiées, comme bien d'autres outils qui traitent directement les modèles au niveau MLD. Mais, dire qu'il ne faut pas démarrer la modélisation au niveau conceptuel sous prétexte MySQL Workbench est un super outil ne me parait pas être une bonne approche.