Discussion :

[antoinelac]

Bonjour à tous!

J'ai un problème que je n'arrive pas à résoudre et ce, depuis quelques mois déjà.

Bien que je n'ai pas travaillé constament dessus depuis tout ce temps, j'y ai mit beaucoup de réflexion.

Mon problème est le suivant :

J'ai une production à gérer. Selon les caratéristiques du produit commandé, il peut ou non se faire sur une ou plusieurs machines.

Ce que je désire, c'est une requête SQL qui retourne le ou les numéros de machine qui peuvent fabriquer le produit.

J'ai construit deux table test qui représente sommairement le problème et une requête qui se rapproche au mieux de ce que j'ai de besoin.

Vous aurez besoin de créer une base de donnée au nom de Thermoform ou de modifier la requête suivante pour l'adapter à votre base de donné.

Voilà pour la table regroupant les critères :

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
-- phpMyAdmin SQL Dump
-- version 3.2.0.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 18, 2010 at 03:17 PM
-- Server version: 5.1.36
-- PHP Version: 5.3.0
 
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
 
--
-- Database: `thermoform`
--
 
-- --------------------------------------------------------
 
--
-- Table structure for table `test_produit_critere`
--
 
CREATE TABLE IF NOT EXISTS `test_produit_critere` (
  `NoIdent` int(11) NOT NULL AUTO_INCREMENT,
  `CodeProduit` varchar(20) NOT NULL,
  `CouleurProduit` varchar(5) NOT NULL,
  `Machine` varchar(10) NOT NULL,
  PRIMARY KEY (`NoIdent`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
 
--
-- Dumping data for table `test_produit_critere`
--
 
INSERT INTO `test_produit_critere` (`NoIdent`, `CodeProduit`, `CouleurProduit`, `Machine`) VALUES
(1, '55', '', '1'),
(2, '55', '10', '2'),
(3, '', '9', '3'),
(4, '5623', '', '4');

Voilà pour la table regroupant les commandes client :

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
-- phpMyAdmin SQL Dump
-- version 3.2.0.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 18, 2010 at 03:27 PM
-- Server version: 5.1.36
-- PHP Version: 5.3.0
 
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
 
--
-- Database: `thermoform`
--
 
-- --------------------------------------------------------
 
--
-- Table structure for table `test_produit`
--
 
CREATE TABLE IF NOT EXISTS `test_produit` (
  `NoIdent` int(11) NOT NULL AUTO_INCREMENT,
  `CodeProduit` varchar(20) NOT NULL,
  `CouleurProduit` int(11) NOT NULL,
  PRIMARY KEY (`NoIdent`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;
 
--
-- Dumping data for table `test_produit`
--
 
INSERT INTO `test_produit` (`NoIdent`, `CodeProduit`, `CouleurProduit`) VALUES
(1, '2400', 10),
(2, '2401', 10),
(3, '5501', 10),
(4, '5501', 13),
(5, '5501', 12),
(6, '5501', 10),
(7, '5503', 9),
(8, '5623', 10);

Voilà la requête que j'utilise pour l'extration du numéro de machine :

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT test_produit.*,test_produit_critere.Machine 
 
FROM test_produit_critere 
 
INNER JOIN test_produit ON 
 
((test_produit.CodeProduit REGEXP CONCAT('^',test_produit_critere.CodeProduit) 
AND test_produit_critere.CodeProduit != '') OR test_produit_critere.CodeProduit = test_produit.CodeProduit OR test_produit_critere.CodeProduit = '') 
 
AND (test_produit_critere.CouleurProduit = '' OR test_produit_critere.CouleurProduit  = test_produit.CouleurProduit)

Résultat de la requête :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
NoIdent   CodeProduit  CouleurProduit                  Machine
3               5501              10                      1
3               5501              10                      2
4               5501              13                      1
5               5501              12                      1
6               5501              10                      1
6               5501              10                      2
7               5503              9                        1
7               5503              9                        3
8               5623              10                      4

Puisque je ne sais pas d'avance les critères et de plus, ils peuvent changer d'une semaine à l'autre de façon imprévisible (exemple : un outil brise sur la machine #2 empêchant de faire tel ou tel produit).

Le résultat de cette requête est presque ce que j'ai de besoin. Il y a un problème. Certain produit tel que le 5501 en couleur 10 ne peut seulement se faire sur la machine 2 (tel que présenté dans la table critere), mais ma requête retourne les deux machines. Un autre problème, la couleur 9 ne peut se faire que sur la machine 3 et la requête retourne deux machines, la 1 et la 3 (voir NoIdent 7).

Alors, ce qui me manque c'est d'exclure les produits qui ont déjà une combinaison gagnante.

Enfin, peut-être que mon approche est fausse aussi.

Dans mon cas réel, j'ai 9 critères qui ont chacun une possibilité plusieurs possibilité (dans certain cas, booléen, dans d'autre VARCHAR de 20, certain sont des int, enfin un mélange de tout).

Le fait d'avoir des millions de possibilité m'empêche de faire une table exhaustive de toutes les possibilités et d'y lister les machines correspondantes. De plus, la maintenance serait compliqué.

Alors, peut-être dois-je aller plus simple et gérer les doublons dans mon code?

Si vous avez déjà vécu une sitution semble, qu'avez-vous fait?

Merci à tous!

antoine

[gene69]

ok je relève le défi. Ya quoi à gagner?

[gene69]

je comprend pas pourquoi les valeurs codeProduit ne sont pas les mêmes dans les deux tables? c'est un faux ami?

[antoinelac]

Malheureusement, il n'y a rien à gagner, sinon mon respect et ma gratitude.

Peut-être pourrais-je offrire une canne de sirop d'érable à celui qui m'apporte la solutions finale, mais c'est compliquer si plusieurs personnes contribuent à la solution finale!

Désolé, j'ai oublier d'expliquer la portion du REGEXP :

En fait, dans la table crière, j'ai mit seulement 55 comme valeur. C'est que tout les produit commençant par 55 (exemple : 5501) doivent être sélectionné lors de la requête.

C'est l'équivalent de dire :

Code :

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

1
2
3
SELECT * 
FROM test_produit 
WHERE CodeProduit LIKE '55%'

Mais puisque ma donnée est 5501 dans mon INNER JOIN j'utilise REGEXP pour faire ressortir les 55%. Le REGEXP prend comme argument un filtre sous forme spécial (http://dev.mysql.com/doc/refman/5.1/en/regexp.html). Il permet de "valider" une structure, il est fréquement utiliser pour valider les adresses courriels, c'est-à-dire de vérifier si la structure est bien "AZ0-9@X0-9.com".

C'est pourquoi les valeurs de CodeProduit ne sont pas identiques de la table produit à la table produit_critere.

[gene69]

digression:
Qui a étudié une base de donnée relationnelle sait que ta regex est une cochonceté conceptuelle. Une jointure c'est fait pour être faite sur un index.

Ceci dit entre du temps de cerveau disponible et du temps de processeur, c'est le processeur qui coute le moins cher. Soyons pragmatique. Cependant, à force de bétise et d'erreur dans ce genre, j'ai vu de petites bases de données 60 To qui était lentes a cause d'un défaut de normalisation, du coup pour calculer une simple moyenne, fallait 20 minutes.

Après ce que je pense des expressions régulieres pour valider les mails, je crois que je vais éditer ma signature.


sur le défit:
Je ferai n'importe quoi pour du sirop d'érable 100% pur jus d'érable.

Je crois que je vais me retirer du défit, s'il s'agit de tout faire une requete.

Je sais faire tout ce à quoi vous avez probablement déjà pensé, mais si j'ai pas le droit d'utiliser des variables ou des tables temporaires, je ne peux pas. Et la réponse sera une mauvaise réponse parce que, par exemple, à ce genre de probleme, l'ensemble vide est une solution correcte pour répondre à la question et que ta boite ne peut pas se permettre ce genre de trivialité.


En plus, le sql sera jamais assez intelligent pour faire ce calcul métier. Utilise prolog si tu as envie de le faire facilement (si tu comprends prolog et sa programmation déclarative) ou n'importe quel langage de programmation impérative (php, .Net ... ) sinon.

[gene69]

sinon, ce que je ferai sous Oracle c'est une vue (matérialisée au besoin) avec toutes les possibilités et des indexes dans tous les sens pour tous les produits avec l'ensemble des prérequis et quand je veux savoir ce que je dois produire je fait une simple selection where mes criteres sont remplis.

Je ramene mes données et je lance #(mon calcul matriciel)|(ma heuristique)#i pour trouver la combinaison qui me permet de produire le plus.

[CinePhil]

Il faut en effet repasser par une étape de modélisation des données puisque, apparemment, ce n'est pas trop tard...

À la place du sirop d'érable, tu prendras bien un peu de Merise ?

Selon les caratéristiques du produit

Dans ton exemple, la seule caractéristique que je vois est la couleur.
Y a t-il d'autres caractéristiques à prendre en compte ?
Ces caractéristiques sont-elles en nombre variable selon le produit ?

En tout cas, il y aura une entité Produit et, pour le moment, je vais rester conforme à ton exemple et considérer que la seule caractéristique importante est la couleur.

du produit commandé

Il y a donc une entité Commande, et probablement une entité Client.

MCD :
Commande -1,1----Concerner----0,n- Client

Tables :
Client (cli_id, cli_nom...)
Commande (cde_id, cde_id_client, cde_date...)

Par contre, tu emploies le singulier dans cet extrait de phrase ; le client ne peut-il commander plusieurs produits sur une même commande ?
Je vais supposer que oui.

MCD :
Commande -1,n----Comprendre----0,n- Produit

Tables :
Commande (cde_id, cde_id_client, cde_date...)
Produit (prd_id, prd_code, prd_nom...)
Ligne_commande (lc_id_commande, lc_id_produit, lc_id_couleur, lc_quantite, lc_prix_unitaire...)

Nota :
Souvent, on transforme l'association Comprendre en entité Ligne_commande afin de pouvoir gérer séparément les lignes de commande. Nous pouvons en avoir besoin ici à cause du choix de la couleur par le client. Modifions le MCD :
Couleur -0,n----Affecter----------------1,1--------------|
Commande -1,n----Comprendre----(1,1)- Ligne_commande -1,1----Concerner----0,n- Produit

Les cardinalités entre parenthèses signifie "identification relative", ce qui fait que l'identifiant de la ligne de commande contiendra l'identifiant de la commande.

Tables :
Commande (cde_id, cde_id_client, cde_date...)
Produit (prd_id, prd_code, prd_nom...)
Couleur (clr_id, clr_code, clr_nom...)
Ligne_commande (lc_id_commande, lc_num_ligne, lc_id_produit, lc_id_couleur, lc_quantite, lc_prix_unitaire...)

Selon les caratéristiques du produit commandé, il peut ou non se faire sur une ou plusieurs machines.

Il y a donc une entité Machine qui est associée à l'entité Produit.
La couleur fait-elle partie des caractéristiques qui influent sur la machine potentielle ou les caractéristiques importantes pour le choix de la machine font-elles partie de la définition du produit, indépendamment de la couleur choisie par le client ?
D'après ton histoire de REGEXP, je pense que la couleur n'intervient pas dans le choix de la machine.

MCD :
Machine -1,n----Pouvoir_fabriquer----1,n- Produit

Tables :
Machine (mch_id, mch_numero, mch_nom, ...)
Produit (prd_id, prd_code, prd_nom...)
Produit_machine (pm_id_produit, pm_id_machine...)

Ce que je désire, c'est une requête SQL qui retourne le ou les numéros de machine qui peuvent fabriquer le produit.

Rien de plus simple avec le modèle que je propose.
Quelles sont les machines qui peuvent fabriquer le produit de la deuxième ligne de la commande 123 ?

Code :

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

1
2
3
4
5
6
SELECT m.mch_numero
FROM Machine AS m
INNER JOIN Produit_machine AS pm ON pm.pm_id_machine = m.mch_id
    INNER JOIN Ligne_commande AS lc ON lc.lc_id_produit = pm.pm_id_produit
WHERE lc.lc_id_commande = 123
    AND lc.lc_num_ligne = 2

À toi d'adapter ce modèle à ton cas réel et plus précis.

À mon tour, je t'invite à lire ce qui est écrit en bleu dans ma signature...

J'aimerais mieux comprendre pourquoi REGEXP est à bannir?

Parce que c'est une opération complexe et coûteuse pour le SGBD donc à utiliser avec parcimonie.

Pour ce qui est des tables temporaires, je croyais que le moins que je les utilisais, le mieux mon serveur mySQL se porterait.

Effectivement !
Et en principe, il n'y en a pas besoin avec mon modèle.

[antoinelac]

Bonjour à tous,

Merci CinePhil pour ta réponse très élaborée,

Je suis en cours de lecture de MERISE et lorsque j'aurais fini mon schéma réel, je le mettrai ici.

Je me rend compte que puisque mon problème est complexe (du moins pour moi), je dois présenté la situation réelle sinon, toutes solutions ne sera qu'incomplète.

De plus, je me rend compte que j'ai mal présenté mon problème ici. J'ai commencé par présenté le travail que j'avais fait plutôt que présenté la situation problématique.

Enfin, je retourne faire mes devoirs et je vous reviens avec quelque chose de plus intéressant.

Merci de votre patience.


Antoine

[antoinelac]

Bon, j'ai une première esquisse à vous présenter :

Il s'agit tout d'abord de la structure de mes tables. Je n'y ai pas présenté trop d'intéraction entre les tables. Les tables présentées n'ont qu'un lien simple entre-elles.

Il existe plusieurs autres tables telles que Client, Commande, Adresse, etc. Ici, seulement les principales concernées sont présentées.

La table "Porte" se trouve à être un des produits traités, il existe plusieurs autres type de produit qui suivent la même idée, mais avec des caractéristiques différentes. La table "Critère_Machine" est la table regroupant les différents critères permettant de connaître sur quelle machine tel porte peut ou non être fabriquée.

Alors, je pourrais lister la combinaison de toutes les possibilités de chacune des caractéristiques et ensuite associer chacune de ces combinaisons à une machine. Cette solution me semble un peu exagérée puisqu'il y aura des millions de combinaisons. À elles seules, les différentes options de type booléenne représentent 256 possibilités (2^8). Les autres caractéristiques se dénombrent de la façon suivante :

Profil Extérieur : 25 possibilités
Style : 12 possibilités
Profil Intérieurs : 150 possibilités
Couleur : 79 possibilités (en évolution constante)
Matériel : 81 possibilités (évolution suivant de près celle des couleurs)

Selon google calculator :
(2^8) * 81 * 79 * 150 * 12 * 25 = 73 716 480 000 combinaisons

Je pensais lister seulement les exceptions, ce qui me semble plus réaliste et plus facile d'entretien. Seulement, mon problème vient à savoir ce que je met dans les cases critères dans la situation suivante :

Dans ma table des critères machine, la machine #3 est la seule à avoir l'outils permettant le profil extérieur '559'. Les autres cases qui n'ont pas d'importance, c'est-à-dire que la couleur, le profil intérieur, le matériel, etc. n'influence pas le fait que la porte doit être fait sur la machine #3.

De plus, si le client désire un profil extérieur qui se fait uniquement sur la machine #3 et une couleur qui se fait uniquement sur la machine #1, alors nous avons un produit qui n'est pas possible. Le système ne doit pas retourner de machine. Une absence de machine indique que nous dans l'indisponibilité (temporaire ou non) de traiter ce produit.

[peufeu]

Je me doutais que c'était un truc comme ça. Donc, la regexp est un genre de façon boiteuse de parser le code produit pour en extraire les informations de style, profil, couleur, etc ?

Vu le nombre de produits possible il est bien évidemment exclu de les matérialiser tous dans une table.

Je te suggère une méthode par attributs.

Code :

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

1
2
3
4
5
6
7
8
9
 
Table attributs_types :
 
att_type_id   att_type_nom
1             Profil Extérieur
2             Style
3             Profil Intérieurs
4             Couleur
etc

Puis tu listes tous les attributs possibles que ton entreprise sait fabriquer.

Code :

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

1
2
3
4
5
6
7
 
Table attributs_possibles :
 
att_type_id   att_valeur att_nom
4             1          Rouge
4             2          Vert
etc

(par exemple ici att_type_id = 4, FK sur attributs_types, donc l'attribut est la Couleur).

Ensuite tu créé une table machines (machine_id, ... ) et une table qui pour chaque machine donne la liste des attributs qu'elle est capable de fabriquer machine_capabilites(machine_id, att_type_id, att_valeur), chaque ligne représente par exemple (machine X, couleur, rouge).

Pour savoir si ton produit est fabricable il suffit de connaître les valeurs de chacun de ses attributs et de regarder dans la table machine_capabilites si les lignes correspondantes existent.

Après,tu disais qu'il y avait des exceptions XDDD

[antoinelac]

Merci PeuFeu d'avoir pris connaissance de mon problème,


Ton idée m'intéresse puisque je me trouve alors à additionner les différentes possibilités plutôt que de les multiplier.

J'ouvre mon MSPaint et je refais mes tables.

Je repost le tout ici ensuite!

salutations,

antoine