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

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 !

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:

---

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.

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:

---

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:

---

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

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:

---

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:

---

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:

---

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:

---

1 2 3

UserNom UserTaille UserRevenus SportNom ------- ---------- ----------- -------- Cézig 175 1500 Tennis

---

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

Bonjour,

Citation:

---

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, ...)

Citation:

---

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:

---

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) ) ;

---

Citation:

---

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:

---

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:

---

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:

---

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:

---

1 2 3

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

---

=>

Code:

---

1 2 3 4 5

UserId ------ 1 3 4

---

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

Code:

---

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:

---

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:

---

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:

---

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:

---

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:

---

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:

---

1 2 3 4

UserNom ------- Cézig Philou

---

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

Code:

---

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:

---

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.

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...:calim2:

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.

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:

---

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:

---

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 ) ) ;

---