Conception d'un MPD - user->(n)property

**rgomes** · 05/08/2011, 14h48

Hello,

Je souhaite concevoir le modèle suivant :
- un User possède 0 ou n Property
- un Property correspond à un User

Au départ j'avais utilisé 2 tables :
- 1 table User
- 1 table Property avec idUser (référence sur le user), propertyId (l'id de la property, référence sur une 3e table de définition des property) et propertyValue (la value).

Exemple : User #001 possède 2 property
-> COULEUR_CHEVEUX=brun
-> TAILLE=170
Cela se traduit par une entrée dans la table User et 2 dans la table Property.

Le problème : niveau SQL c'est assez complexe. Exemple : faire une recherche du type "tous les user brun, qui mesure plus de 150 etc....".

Ne faut-il pas mieux tout mettre à plat dans une seule table avec autant de colonne que de propriétés ?
Avez-vous une expérience de ce type de problématique ?

Merci !

**fsmrel** · 06/08/2011, 13h36

Bonjour,

Votre présentation de la structure des trois tables est ambiguë. D’après la présence de la 3e table, appelons-la T3, il semble que votre structure soit la vaguement la suivante :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
USER (UserId, UserNom, ...)
PRIMARY KEY (UserId)

PROPERTY (PropertyId, UserId, PropertyValue ?)
PRIMARY KEY (PropertyId)
FOREIGN KEY (UserId) REFERENCES USER
FOREIGN KEY (???) REFERENCES PROPERTY_DEFINITION

PROPERTY_DEFINITION (???, ???)
PRIMARY KEY (???)
FOREIGN KEY (???)

Mais on ne sait pas à quoi ressemble la 3e table (PROPERTY_DEFINITION) et quelles sont les relations qu’elle entretient avec la table PROPERTY Merci de présenter la structure exacte de vos tables sous la forme que j’ai utilisée et non pas sous forme littéraire.

**rgomes** · 08/08/2011, 10h55

Merci pour votre réponse rapide !

USER (UserId, UserNom, ...)
PRIMARY KEY (UserId)

PROPERTY (PropertyId, UserId, PropertyValue ?)
PRIMARY KEY (PropertyId)
FOREIGN KEY (UserId) REFERENCES USER
FOREIGN KEY (PropertyDefId) REFERENCES PROPERTY_DEFINITION

PROPERTY_DEFINITION (PropertyDefId, PropertyDefType, PropertyDefCode, PropertyDefRange, PropertyDefMax, PropertyDefMin)
PRIMARY KEY (PropertyDefId)

Exemple :
Dans property_defintion je peux envisager les entrées suivantes :

Code :

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

1
2
3
4
ID     TYPE   CODE        RANGE       MIN          MAX
099   INT     TAILLE      1               100          250
100   INT     REVENUS   500            0             100 000
101   VAR     SPORT      null             null          null

Dans property (les propriétés d'un user) :

Code :

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

1
2
3
4
5
ID       ID_USER      ID_PROPERTY     PROPERTY_VALUE
001     001            099                  175
002     001            100                  1500
003     001            101                  'Le tennis'

Merci pour toute suggestion !

Merci

**fsmrel** · 08/08/2011, 17h40

Bonjour,

Votre table PROPERTY_DEFINITION contient des métadonnées, par exemple elle est dotée d’une colonne TYPE (de type disons CHAR) qui permet de connaître le type (INT, VAR, etc.) d’autres données : vous vous embarquez pour une programmation a priori sophistiquée.

En outre, certaines valeurs y sont remplacées par le marqueur NULL (colonnes RANGE, MIN, MAX pour la ligne où ID = 101). Mais le bonhomme NULL, voilà l’ennemi, l’inhibiteur des optimiseurs, celui qui avec sa logique trivaluée (VRAI, FAUX, INCONNU) fiche la zoubia dans les bases de données. A tout le moins, remplacez le marqueur NULL par une valeur, par exemple 'sans objet'.

Accessoirement, la colonne PROPERTY_VALUE ressemble à un inventaire à la Prévert, un salmigondis hétéroclite, une auberge espagnole, une pétaudière, alors que ses valeurs devraient être sémantiquement homogènes, à savoir seulement des montants, seulement des tailles, seulement des noms de sports, etc.

Bref, aujourd’hui vous maîtriserez sans doute la complexité de la table PROPERTY_DEFINITION, mais qu’en sera-t-il dans un an ? Et si vous refilez le bébé à quelqu’un d’autre, ne se posera-t-il pas bien des questions ? La prudence recommande de simplifier tout cela, donc d’éviter la mise en œuvre de cette table même si intellectuellement il y a un défi intéressant à relever.

Concernant la table PROPERTY_DEFINITION :

La colonne CODE contient les valeurs suivantes : 'TAILLE', 'REVENUS', 'SPORT'.

Combien de valeurs distinctes contient-elle ? Je pose cette question parce que ça rendrait les choses moins compliquées, dans la mesure où chacune d’elles peut faire l’objet d’une colonne de la table USERX (je renomme ainsi votre table USER, car 'USER' est un mot réservé), et ou d’une table.

Par exemple, si tous les utilisateurs ont obligatoirement une taille, celle-ci peut faire l’objet d’une colonne de la table USERX. Le contrôle des fourchettes de valeurs peut être intégré à la table. Même principe pour les revenus.

Les sports ne font pas l’objet d’une fourchette de valeurs mais d’une énumération : ils font de préférence l’objet d’une table, qui en plus du nom du sport peut fournir d’autres indications. Comme un utilisateur ne pratique pas forcément un sport, on définit une table associative (USER_SPORT ci-dessous). Si un utilisateur peut pratiquer plus d’un sport, il est facile de prendre cela en compte.

Je n’ai pas pris en compte la notion de RANGE. Pourriez-vous fournir la signification que vous donnez à la colonne PropertyDefRange, ses caractéristiques ? Si par exemple, cette donnée est propre à un thème (taille, revenus, ...) il faudra sans doute mettre en oeuvre des tables ad-hoc (TAILLE_RANG, REVENUS_RANGE, ...)

En attendant, dans le style SQL, on aurait une ébauche d’un jeu de tables :

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
CREATE  TABLE USERX 
(
  UserId          INT           NOT NULL ,
  UserNom         VARCHAR(45)   NOT NULL ,
  UserTaille      INT           NOT NULL ,
  UserRevenus     INT           NOT NULL ,
  CONSTRAINT USER_PK PRIMARY KEY (UserId),
  CONSTRAINT USER_TAILLE_CHK CHECK (UserTaille BETWEEN 100 AND 250),
  CONSTRAINT USER_REVENUS_CHK CHECK (UserRevenus BETWEEN 0 AND 100000)
) ;  
 
CREATE  TABLE SPORT
(
  SportId          INT           NOT NULL ,
  SportNom         VARCHAR(45)   NOT NULL ,
  CONSTRAINT SPORT_PK PRIMARY KEY (SportId)
) ;
 
CREATE  TABLE USER_SPORT 
(
  UserId           INT        NOT NULL ,
  SportId          INT        NOT NULL ,
  CONSTRAINT USER_SPORT_PK PRIMARY KEY (UserId) ,
   CONSTRAINT USER_SPORT_USER_FK
    FOREIGN KEY (UserId) REFERENCES USERX (UserId),
 CONSTRAINT USER_SPORT_SPORT_FK 
    FOREIGN KEY (SportId) REFERENCES SPORT (SportId)
) ;

Remplissons ces tables :

Code SQL :

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

1
2
3
INSERT INTO USERX VALUES (1, 'Cézig', 175, 1500) ;
INSERT INTO SPORT VALUES (1, 'Tennis') ;
INSERT INTO USER_SPORT VALUES (1, 1) ;

Requête pour tout savoir sur les utilisateurs :

Code SQL :

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

1
2
3
4
5
SELECT UserNom, UserTaille, UserRevenus, SportNom 
FROM   USERX AS x JOIN USER_SPORT AS y 
       ON x.UserId = y.UserId 
                  JOIN SPORT AS z 
       ON y.SportId = z.SportId ;

Au résultat :

Code :

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

1
2
3
UserNom  UserTaille  UserRevenus  SportNom
-------  ----------  -----------  --------
Cézig           175         1500    Tennis

**rgomes** · 09/08/2011, 08h27

Merci pour votre réponse détaillée, c'est très intéressant.

J'ai encore une zone d'ombre sur un aspect. Si mon userX pratique plusieurs sports :

INSERT INTO SPORT VALUES (1, 'Tennis') ;
INSERT INTO SPORT VALUES (2, 'Foot') ;
INSERT INTO SPORT VALUES (3, 'Course à pied') ;
INSERT INTO USER_SPORT VALUES (1, 1);
INSERT INTO USER_SPORT VALUES (1, 2) ;
INSERT INTO USER_SPORT VALUES (1, 3) ;

mes criterias de recherche vont être complexes non ? Par exemple, si je souhaite retrouver les users qui pratiquent au moins le tennis et course à pied ?

Finalement, ne vaut-il pas mieux tout mettre à plat (une colonne de type boolean TENNIS, une colonne de type boolean FOOT, etc...) ?

Concernant les "ranges" et fourchette de la table PORPERTY_DEF, ils me permettaient de générer les filtres (liste déroulante/combo) dynamiquement.

Merci encore

**fsmrel** · 09/08/2011, 17h42

Bonjour,

Envoyé par rgomes

Concernant les "ranges" et fourchette de la table PORPERTY_DEF, ils me permettaient de générer les filtres (liste déroulante/combo) dynamiquement.

Vous écrivez « permettaient » et non pas « permettent » : ils ne sont plus pris en compte dans la base de données ? Sinon, je répète ce que j’ai déjà écrit : Pourriez-vous fournir la signification que vous donnez à la colonne PropertyDefRange, ses caractéristiques ? Si par exemple, cette donnée est propre à un thème (taille, revenus, ...) il faudra sans doute mettre en oeuvre des tables ad-hoc (TAILLE_RANG, REVENUS_RANGE, ...)

Envoyé par rgomes

Finalement, ne vaut-il pas mieux tout mettre à plat (une colonne de type boolean TENNIS, une colonne de type boolean FOOT, etc.) ?

Non. Je suppose que vous parlez de la table USERX. Vous fabriqueriez une sorte de gruyère plein de trous, car les utilisateurs ne sont pas censés pratiquer tous les sports. Plus grave : si vous posez la question « quels sont les utilisateurs qui pratiquent le tennis et la course à pied », le SGBD devra accéder à chaque ligne de la table, ce qui peut poser des problèmes de performance et de consommation de ressources si la table est volumineuse et si de nombreux utilisateurs posent ce type de question en même temps. Vous pourriez me dire : il n’y a qu’à affecter un index à chaque colonne associée à un sport. Malheureusement, cela fait beaucoup d’index et quand la table USERX est mise à jour (ajout, modification, suppression de lignes), ça devient la cata (tous les index sont secoués). Par ailleurs, de tels index ne seront jamais utilisés en consultation par le SGBD, car ils ne contiennent que deux valeurs, disons VRAI et FAUX et dans ces conditions, le SGBD estimera qu’il perdra moins de temps en parcourant directement la table.

Par ailleurs, à chaque fois que vous ajouterez un sport, il faudra modifier la structure de la table USERX, ce qui est une opération lourde le plus souvent.

Il est donc nettement préférable de conserver la structure que je vous ai proposée, en complétant la clé primaire de la table USER_SPORT puisqu’un utilisateur peut pratiquer plusieurs sports :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
CREATE  TABLE USER_SPORT 
(
  UserId           INT        NOT NULL,
  SportId          INT        NOT NULL,
  CONSTRAINT USER_SPORT_PK PRIMARY KEY (UserId, SportId),
   CONSTRAINT USER_SPORT_USER_FK
    FOREIGN KEY (UserId) REFERENCES USERX (UserId),
 CONSTRAINT USER_SPORT_SPORT_FK 
    FOREIGN KEY (SportId) REFERENCES SPORT (SportId)
) ;

Envoyé par rgomes

mes criterias de recherche vont être complexes non ? Par exemple, si je souhaite retrouver les users qui pratiquent au moins le tennis et course à pied ?

Tout est relatif ! Pour un habitué de SQL, ce genre de requête n’est pas un problème. Pour un néophyte, évidemment c’est moins simple qu’un banal SELECT * FROM USERX...

Préparons un jeu d’essai :

Les utilisateurs :

Code SQL :

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

1
2
3
4
INSERT INTO USERX VALUES (1, 'Cézig',   175, 1500) ;
INSERT INTO USERX VALUES (2, 'Albert',  180, 1400) ;
INSERT INTO USERX VALUES (3, 'Philou',  180, 1700) ;
INSERT INTO USERX VALUES (4, 'Mimile',  190, 1500) ;

Les sports :

Code SQL :

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

1
2
3
4
5
6
INSERT INTO SPORT VALUES (1, 'Tennis') ;
INSERT INTO SPORT VALUES (2, 'Billard 3 bandes') ; 
INSERT INTO SPORT VALUES (3, 'Foot') ;
INSERT INTO SPORT VALUES (4, 'Course à pied') ; 
INSERT INTO SPORT VALUES (5, 'Basket') ;
INSERT INTO SPORT VALUES (6, 'Handball') ;

Les sports pratiqués par les utilisateurs :

Code SQL :

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

1
2
3
4
5
6
7
8
INSERT INTO USER_SPORT VALUES (1, 1) ;
INSERT INTO USER_SPORT VALUES (1, 2) ;
INSERT INTO USER_SPORT VALUES (1, 3) ;
INSERT INTO USER_SPORT VALUES (2, 2) ;
INSERT INTO USER_SPORT VALUES (2, 4) ;
INSERT INTO USER_SPORT VALUES (3, 1) ;
INSERT INTO USER_SPORT VALUES (3, 2) ;
INSERT INTO USER_SPORT VALUES (4, 1) ;

Requête 1 : Ensemble des UserId des utilisateurs pratiquant le tennis :

Code SQL :

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

1
2
3
SELECT UserId
FROM   USER_SPORT AS y JOIN SPORT AS z ON y.SportId = z.SportId 
WHERE  z.SportNom = 'Tennis' ;

=>

Code :

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

1
2
3
4
5
UserId
------
1
3
4

Requête 2 : Ensemble des UserId des utilisateurs pratiquant le billard à 3 bandes :

Code SQL :

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

1
2
3
SELECT UserId
FROM   USER_SPORT AS y JOIN SPORT AS z ON y.SportId = z.SportId 
WHERE  z.SportNom = 'Billard 3 bandes' ;

=>

Code :

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

1
2
3
4
5
UserId
------
1
2
3

Requête 3 : Ensemble des UserId des utilisateurs pratiquant le tennis et le billard à 3 bandes :

On effectue une opération d’intersection (n’oublions pas qu’une table a vocation à être un ensemble) :

Code SQL :

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

1
2
3
4
5
6
7
SELECT UserId
FROM   USER_SPORT AS x JOIN SPORT AS y ON x.SportId = y.SportId 
WHERE  y.SportNom = 'Tennis'      
INTERSECT
SELECT UserId
FROM   USER_SPORT AS x JOIN SPORT AS y ON x.SportId = y.SportId 
WHERE  y.SportNom = 'Billard 3 bandes' ;

=>

Code :

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

1
2
3
4
UserId
------
1
3

A noter que, pour des raisons x ou y, certains préfèrent la jointure à l’intersection :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT x.UserId, 'Jointure'
FROM
(SELECT UserId
 FROM   USER_SPORT AS x JOIN SPORT AS y ON x.SportId = y.SportId 
 WHERE  y.SportNom = 'Tennis'
) AS x      
JOIN   
(SELECT UserId
 FROM   USER_SPORT AS x JOIN SPORT AS y ON x.SportId = y.SportId 
 WHERE  y.SportNom = 'Billard 3 bandes'      
) AS y
ON x.UserId = y.UserId ;

Requête 4 : Ensemble des Noms des utilisateurs pratiquant le tennis et le billard à 3 bandes (pour présenter les noms des utilisateurs pratiquant le tennis et le billard à 3 bandes, on met un peu de sauce autour de la requête précédente) :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT UserNom
FROM   USERX AS x
       JOIN  
      (SELECT UserId
       FROM   USER_SPORT AS x JOIN SPORT AS y ON x.SportId = y.SportId 
       WHERE  y.SportNom = 'Tennis'      
       INTERSECT
       SELECT UserId
       FROM   USER_SPORT AS x JOIN SPORT AS y ON x.SportId = y.SportId 
       WHERE  y.SportNom = 'Billard 3 bandes'
      ) AS y      
      ON x.UserId = y.UserId ;

=>

Code :

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

1
2
3
4
UserNom
-------
Cézig
Philou

Pour présenter les noms des utilisateurs pratiquant le tennis ou le billard à 3 bandes :

Code SQL :

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

1
2
3
4
5
6
SELECT DISTINCT UserNom 
FROM   USERX AS x JOIN USER_SPORT AS y 
       ON x.UserId = y.UserId 
                  JOIN SPORT AS z 
       ON y.SportId = z.SportId 
WHERE  z.SportNom IN ('Tennis', 'Billard 3 bandes') ;

=>

Code :

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

1
2
3
4
5
6
UserNom
-------
Albert 
Cézig 
Mimile 
Philou

Etc.

Remarque :

On conservant les structures de tables que je vous ai proposées, le nombre d’index est réduit au strict minimum et ceux-ci sont exploités par le SGBD, d’où une performance assurée.

**rgomes** · 09/08/2011, 22h45

Fantastique ! Je vais revoir mon modèle et faire un premier poke. J'utilise Hibernate de façon intensive, j'espère m'en sortir sans trop transpirer...

Il va falloir que depuis mes filtres je puisse bien sur construire dynamiquement mes requêtes.

Merci pour ces riches explications !

PS : concernant la notion de range, dans ma première ébauche de mcd, elle me servait à définir l'unité d'une property_definition (exemple 1 pour une taille - représentant 1 cm ou 500 par exemple pour exprimer un revenu). Je construisais dynamiquement mes filtres au niveau de l'UI avec l'unité, le min et le max (exemple taille comprise entre ... et .... -> 2 listes déroulantes). Effectivement, cette notion d'unité, min et max ne concernent que certaines property_definition.

**fsmrel** · 13/08/2011, 01h38

Bonjour,

Une remarque :

L’intersection équivaut à un ET au sens de la logique. Mais s’il faut obtenir l’ensemble des utilisateurs qui pratiquent Le billard à 3 bandes, le tennis et le foot, il faudra utiliser une requête comportant un bloc INTERSECT supplémentaire. Si l’on part sur la base de N sports pratiqués, on devra coder N-1 blocs INTERSECT.

On peut procéder autrement que par intersection, en effectuant ce que l’on appelle une division relationnelle. L’opérateur DIVIDEBY de l’algèbre relationnelle ne fait pas partie du langage SQL, mais on sait le paraphraser.

Partons de l’énoncé :

Des trois sports : tennis, billard à 3 bandes et foot, le joueur x les pratique tous.

Pour l’exprimer en SQL (en utilisant EXISTS), on peut écrire de façon équivalente :

Des trois sports : tennis, billard à 3 bandes et foot, il n’en existe pas que le joueur x ne pratique pas.

Pour obtenir l’ensemble des joueurs tels que x et mettre en œuvre la double négation figurant dans ce 2e énoncé, on utilise en conséquence un double NOT EXISTS :

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
 
SELECT UserNom
FROM   USERX AS x
WHERE  NOT EXISTS
      (SELECT *
       FROM   SPORT AS y
       WHERE  y.SportNom IN ('Tennis', 'Billard 3 bandes', 'Foot')
       AND    NOT EXISTS
             (SELECT  *
              FROM    USER_SPORT AS z
              WHERE   x.UserId = z.UserId 
              AND     y.SportId = z.SportId
             )
      ) ;

Bien sûr, si la liste des sports change, il faut modifier la requête, mais c’est à un seul endroit et de façon légère.

Si cette liste figure dans une table à part, appelons-la LISTE_SPORTS, alors la requête n’a jamais à être modifiée :

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 UserNom
FROM   USERX AS x
WHERE  NOT EXISTS
      (SELECT *
       FROM   LISTE_SPORTS AS y
       WHERE  NOT EXISTS
             (SELECT  *
              FROM    USER_SPORT AS z
              WHERE   x.UserId = z.UserId 
              AND     y.SportId = z.SportId
             )
      ) ;

Conception d'un MPD - user->(n)property [MPD]

Schéma

Discussions similaires

Partager

Partager