Gestion de demandes de services divers

**benji1000** · 20/11/2013, 15h27

Bonjour à tous,

je dois concevoir un système permettant de gérer des demandes de services pour une boîte d'info. Ces services peuvent être très divers ; ça peut aller de la livraison d'une station de travail à la mise en place de la sauvegarde sur un serveur, en passant par un Blackberry ou une imprimante.

Lorsqu'une personne fait une demande, cela créée un dossier dans la table "dossiers" (ou "demandes", peu importe), contenant la date de la demande, la date d'exécution, le prix, les données du client... qui doit ensuite être lié à la description du service demandé. Je m'interroge sur la meilleure façon de stocker les détails d'un service demandé, sachant que chaque service n'a pas les mêmes propriétés/caractéristiques.

Exemple de services et de leurs caractéristiques :
- sauvegarde d'un serveur (nom du serveur, adresse IP, répertoires à sauvegarder, OS installé...)
- machine virtuelle (quantité de RAM, de CPU, type d'accès Internet...)
- installation des outils Office sur un poste (numéro de bureau, site géographique...)

Solution 1

Chaque service serait dans une table différente : une table "sauvegarde", une table "pc", une table "machine virtuelle", etc. C'est en fait plus ou moins comme ça que c'est fait actuellement. La table des dossiers générale contient alors l'ID du descriptif du service, et le type de service, afin que l'on sache dans quelle table aller chercher les détails du service. Je peux me tromper, mais j'ai le sentiment que c'est pas tip-top, comme méthode !

Solution 2

Je suis à peu près certains que cette solution n'est pas la bonne façon de procéder (je demande quand même pour confirmation) : tous les services seraient dans une même table, cette table possédant grosso-modo deux champs : un champ "attributs" et un champ "valeurs", chacun étant amené à recevoir des données JSON. Mais il me semble que cela viole une règle qui dit que les données doivent être décomposées, non ?

Solution 3

Cette solution m'a été donnée. Elle consiste à mettre tous les attributs de tous les services dans une même table. Cette table aurait trois champs : ID du dossier, nom de la propriété, valeur de la propriété. Ainsi, quand je souhaite afficher les détails d'un dossier, je sélectionne toutes les propriétés correspondant à l'ID du dossier voulu. Ce champ serait indexé pour gagner en rapidité.

Je penchais d'instinct plutôt pour la 1e solution, mais la 3e solution me parait bien. Je souhaiterais néanmoins avoir des avis différents, voir d'autres propositions si vous en avez.

Merci d'avance pour vos réponses

**SQLpro** · 20/11/2013, 22h35

La 1 est intéressante, mais supposerait la création dynamique de tables et de colonnes si de nouveaux services sont ajoutés et bien entendu toutes les IHM et requêtes qui vont avec... Pas simple ! C'est pourtant ce qui serait le plus performant.

Les 2 et 3 sont une même vision de la méta modélisation et je vous invite à lire les articles que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/m...n/metadonnees/

Il existe une néanmoins une meilleure solution que ces deux dernières qui consiste à n'utiliser qu'une seule table dans laquelle vous aurez les données communes à tous les services (colonnes atomiques) et une colonne complémentaire de type XML ou vous pouvez mettre ce que vous voudrez...
Néanmoins cette façon de faire doit être réservée à des SGBDR qui intègrent XQuery et XPath en standard dans SQL et surtout l'indexation des colonnes XML. Donc Oracle, DB2 ou SQL Server (PG ne permet pas d'utiliser XQuery:XPath dans les requête et ne sait pas indexer du XML... quand à MySQL... ne rêvons pas !).

Par exemple dans SQL Server, la stratégie serait la suivante :

1) créer une collection de schéma XML décrivant la structure XML des documents des différents services

Code :

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

CREATE XML SCHEMA COLLECTION XSC_MES_SERVICES...

On pourra toujours rajouter par la suite de nouveau schéma à cette collection, en fonction des services nouveaux offerts.

2) créer votre table des services avec une colonne de type XML typé avec la collection :

Code :

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

1
2
3
4
5
6
CREATE TABLE T_SERVICE_SRV
(SRV_ID          INT IDENTITY PRIMARY KEY,
 ...
 SRV_XML_DATA    XML (XSC_MES_SERVICES) NOT NULL,
 ...
)

3) indexer votre colonne XML en fonction des usages que vous en faites

Code :

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

1
2
3
4
5
6
7
CREATE PRIMARY XML INDEX X_SRV_XML_DATA_PRIM
   ON T_SERVICE_SRV (SRV_XML_DATA);

CREATE XML INDEX X_SRV_XML_DATA_PROPERTY
   ON T_SERVICE_SRV (SRV_XML_DATA)
   USING XML INDEX X_SRV_XML_DATA_PRIM
   FOR PATH;

4) créer autant de vues que de services, chaque vue dépliant les attributs du XML nécessaire au service.

La mise à jour étant assurée par du XML avec la méthode modify et pourquoi pas des déclencheurs INSTEAD OF...

C'est ce qui sera le plus performant après la solution 1.

A +

PS : j'ai mis en place cette solution pour plusieurs de mes clients, et cela dépote !

**benji1000** · 21/11/2013, 10h00

Merci Frédéric d'avoir pris le temps de me répondre.

J'ai trouvé votre article très intéressant ! Mis à part quelques termes que je n'ai pas encore le niveau pour bien saisir, j'ai compris le principe de méta-données, et c'est à priori celui que je vais implémenter. Nous sommes en effet confrontés aujourd'hui au problème de l'ajout de caractéristiques pour les services, ce qui nous oblige à retravailler nos tables, et surtout nos requêtes, à chaque fois. Utiliser le principe de méta-données pourrait permettre de mettre en place plus rapidement des modifications. De plus, nous utilisons pour l'instant MySQL, et nous n'avions pas prévu de migrer vers SQL Server, bien que ç'aurait été éventuellement envisageable.

Cependant, j'aimerais comprendre votre solution à base de XML. Finalement, elle se rapprocherait de la solution que j'avais imaginée à base de JSON, non ? Et vous dites qu'étant donné que la structure du XML serait décrite, il serait possible d'effectuer des requêtes SQL pour aller chercher des informations au sein-même du XML ? Ou je vous ai mal compris ?

**SQLpro** · 21/11/2013, 18h15

Envoyé par benji1000

...
De plus, nous utilisons pour l'instant MySQL, et nous n'avions pas prévu de migrer vers SQL Server, bien que ç'aurait été éventuellement envisageable.

Avbandonnez MySQL, c'est un véritable merde totalement bugué et si vous voulez vous en sortir, vous devrez payer des modules hors de prix auprès d'Oracle.
http://blog.developpez.com/sqlpro/p9...oudre_aux_yeux
MySQL a 20 ans de retard sur le SQL et une admin totalement déficiente... Optez au minimum pour PostGreSQL qui est du vrai libre. Sinon, il y a la version gratuite de SQL Server (SQL Server Express.... limité à 32 000 bases de 10 Go...)

Cependant, j'aimerais comprendre votre solution à base de XML. Finalement, elle se rapprocherait de la solution que j'avais imaginée à base de JSON, non ? Et vous dites qu'étant donné que la structure du XML serait décrite, il serait possible d'effectuer des requêtes SQL pour aller chercher des informations au sein-même du XML ? Ou je vous ai mal compris ?

Tout à fait !

Un petit exemple avec SQL Server (vous pourrez le reproduire avec SQL Server Express) :

Code :

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

1
2
3
4
5
6
7
8
-- création de la table avec colonne XML
CREATE TABLE T_SERVICE_SRV
(SRV_ID      INT IDENTITY PRIMARY KEY,
 SRV_DATE    DATE DEFAULT GETDATE(),
 SRV_TYPE    CHAR(8),
 SRV_NOM     VARCHAR(64),
 SRV_DATA    XML);
GO

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
42
43
44
45
46
-- insertion de 3 services dont deux de même type
INSERT INTO T_SERVICE_SRV (SRV_NOM, SRV_TYPE, SRV_DATA)
VALUES 
('Service 1', 'Boite',
'
<DATA>
   <ITEM>
      <NOM>couleur</NOM>
      <VAL>bleu</VAL>   
   </ITEM>
   <ITEM>
      <NOM>longueur</NOM>
      <VAL>23.47</VAL>   
   </ITEM>
   <ITEM>
      <NOM>date de péremption</NOM>
      <VAL>2015-01-01</VAL>   
   </ITEM>
</DATA>
'),
('Service 2', 'Boite',
'
<DATA>
   <ITEM>
      <NOM>couleur</NOM>
      <VAL>blanc</VAL>   
   </ITEM>
   <ITEM>
      <NOM>date de péremption</NOM>
      <VAL>2016-01-01</VAL>   
   </ITEM>
</DATA>
'),
('Service 3', 'Ville',
'
<DATA>
   <ITEM>
      <NOM>Nom</NOM>
      <VAL>Seattlec</VAL>   
   </ITEM>
   <ITEM>
      <NOM>Altitude</NOM>
      <VAL>52</VAL>   
   </ITEM>
</DATA>
');

Code :

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

1
2
3
4
5
6
7
8
9
--- exemple de rerquête de mise à plat des données XML :
SELECT SRV_ID, SRV_DATE, SRV_TYPE, SRV_NOM,
       --> extraction de l'élément NOM de la pseudo table XML
       ITEMS.value('./NOM[1]', 'NVARCHAR(128)') AS NOM_ATTRIBUT, 
       --> extraction de l'élément VAL de la pseudo table XML
       ITEMS.value('./VAL[1]', 'NVARCHAR(128)') AS VAL_ATTRIBUT 
FROM   T_SERVICE_SRV
       --> méthode pour déplier le noeud XML sous forme de table
       CROSS APPLY SRV_DATA.nodes('//DATA/ITEM') T_XML(ITEMS);

La méthode nodes appliqué à une colonne XML tabularise le XML à partir du nœud indiqué par le chemin.
L'opérateur CROSS APPLY permet de faire le produit cartésien entre la table SQL et la pseudo table extraite du XML
La méthode value appliqué à un fragment XML extrait la valeur d'un élément ou d'un attribut.

Voici le résultat de cette requête :

Code :

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

1
2
3
4
5
6
7
8
9
10
SRV_ID      SRV_DATE   SRV_TYPE SRV_NOM       NOM_ATTRIBUT            VAL_ATTRIBUT
----------- ---------- -------- ------------- ----------------------- ------------------
3           2013-11-21 Boite    Service 1     couleur                 bleu
3           2013-11-21 Boite    Service 1     longueur                23.47
3           2013-11-21 Boite    Service 1     date de péremption      2015-01-01
4           2013-11-21 Boite    Service 2     couleur                 blanc
4           2013-11-21 Boite    Service 2     date de péremption      2016-01-01
5           2013-11-21 Ville    Service 3     Nom                     Seattle
5           2013-11-21 Ville    Service 3     Altitude                52

Il en existe bien d'autres méthodes dans SQL Server pour manipuler du XML, y compris générer du XML directement en sortie de requête. Lisez l'article de mon confrère rudi bruchez à ce sujet :
http://rudi.developpez.com/sqlserver/tutoriel/xquery/

Plus généralement sur l'intégration de XML dans les SGBDR : http://www.amazon.com/Querying-XML-c.../dp/1558607110

******************* EN SUS ********************

Voici le XMLschema correspondant à nos données XML. Je l'ai obtenu à l'aide de http://www.freeformatter.com.
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns

s="http://www.w3.org/2001/XMLSchema">
<xs:element name="DATA">
<xs:complexType>
<xs:sequence>
<xs:element name="ITEM" maxOccurs="unbounded" minOccurs="0">
<xs:complexType>
<xs:sequence>
<xs:element type="xs:string" name="NOM"/>
<xs:element type="xs:string" name="VAL"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

Nous pouvons l'inclure dans une collection de schéma XML afin de typer le XML :

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
CREATE XML SCHEMA COLLECTION XSC_SRV_COMP
AS 
'<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="DATA">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="ITEM" maxOccurs="unbounded" minOccurs="0">
          <xs:complexType>
            <xs:sequence>
              <xs:element type="xs:string" name="NOM"/>
              <xs:element type="xs:string" name="VAL"/>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>';

Dans le create table, il suffit de modifier la colonne SRV_DATA comme suit :

Code :

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

SRV_DATA    XML (XSC_SRV_COMP)

Le XML typé est plus rapide en manip vu que SQL connait à l'avance les éléments composant le doc XML....

On peut aussi indexer le XML...

Par exemple l'index suivant :

Code :

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

1
2
CREATE PRIMARY XML INDEX X_SRV_XML_DATA
   ON T_SERVICE_SRV (SRV_DATA);

Qui optimise par un facteur 300 l'accès aux données !

Quand à JSON c'est à dire une transfo de XML et qui plus est côté PHP, ce ne sera jamais performant ! Ne rêvez pas !!! Pour manipuler rapidement des masses d'information, il faut des index... Ce que JSon ni PHP ne permet....

A +

**benji1000** · 22/11/2013, 23h25

Ah oui, pas mal comme méthode. Ça devient complexe, mais puissant. Merci pour votre réponse, je vais approfondir vos liens

Gestion de demandes de services divers [MCD]

Schéma

Discussions similaires

Partager

Partager