Discussion :

[punkoff]

Bonjour,


Je reviens sur un problème dont je n'arrive pas à trouver une solution convenable.

Lors d'un précédant projet j'ai été confronté au cas suivant, et la solution que nous avons mis en place ne me satisfait pas du tout.

Ci-joint le MCD (simplifié)

L'application sert à renseigner des CA pour différente filiale d'un groupe.
Une filiale est reconnue comme étant un couple (country/groupe).
Un groupe appartient à un cluster-segment.
Un cluster-segment appartient à un cluster.

Pour chaque filiale on a une table qui liste les CA (entité SALE) par activité (entité ACTIVITY) et par trimestre (entité QUARTER).

Chaque utilisateur (entité UTILISATEUR) possède 1 à n rôle (entité ROLE).
Un rôle possède un niveau de droit, implicite, qui peut être :
- niveau COUNTRY
- niveau FILLIALE
- niveau CLUSTER
- niveau administrateur (il peut tout voir)
- etc

Ce niveau détermine, pour certaine partie de l'application quelle donnée de l'entité SALE il pourra voir.

Par exemple un utilisateur qui à un rôle de niveau CLUSTER pourra voir toutes les données de SALE des filiales rattachées à ce cluster (le lien se fera via : filiale => groupe => cluster-segment => cluster)


La problématique :
Pouvoir faire une requête de statistique qui montrera à l'utilisateur le cumul des CA des filiales dont il a le droit de regard dessus.


Et là ou la modélisation pêche c'est au niveau des relations entre les rôles / utilisateurs / différents niveau de droit.

La modélisation actuelle est disons dénormalisée, puisque dans la relation R_USR_ROL nous avons 3 colonnes qui ne sont pas forcément rempli.
C-a-d que selon el niveau du rôle une / plusieurs des colonnes de cette relation seront alimenté ou non.

Ensuite il faut faire des requêtes assez complexe pour retomber sur nos pattes.
En effet il faut arriver à retrouver toutes les filiales qu'un utilisateur peut "voir" afin de générer la requête de statistique.


Personnellement je ne vois pas comment modéliser ceci d'une manière simple qui déchargerait le côté requétage.

J'avais pensez à une solution différée, c-a-d passer par une table de consolidation où l'on alimenterai toutes les filiales qu'un utilisateur peut "voir" puis de faire une simple jointure entre cette table et la table des SALE.

Et vous qu'en pensez-vous ?

[CinePhil]

D'après ce que je comprends, ta table R_USR_ROL est une table associative qui dit que tel utilisateur a tel(s) rôle(s). Par contre, je ne comprends pas la signification du nom des colonnes qui la composent.

Si je comprends la problématique, un utilisateur qui a le rôle "cluster" peut accéder aux informations de certains clusters, un utilisateur avec le rôle "groupe" peut voir certains groupes...

Le mieux serait je pense de faire une spécialisation des utilisateurs :

Je vais parler en Merise, c'est selon moi plus clair que le schéma E/R, en me limitant à deux spécialisations.

Règles de gestion :
1) un utilisateur cluster est un utilisateur et un utilisateur peut être un utilisateur cluster.
2) un utilisateur groupe est un utilisateur et un utilisateur peut être un utilisateur groupe.
...

MCD :
utilisateur_cluster -(1,1)----etre----0,1- utilisateur
utilisateur_gtoupe -(1,1)----etre----0,1---------|

Tables :
utilisateur (usr_id, usr_nom...)
utilisateur_cluster (ucl_id_utilisateur...)
utilisateur_groupe (ugr_id_utilisateur...)

Il suffit ensuite d'associer les entités spécialisées aux entités correspondant à leur rôle :
utilisateur_cluster -0,n----accéder----0,n- cluster
utilisateur_groupe -0,n----accéder----0,n- groupe

[fsmrel]

Bonjour,


Vous utilisez le verbe « appartenir » pour le rattachement des groupes aux cluster-segments et de ceux-ci aux clusters. Sémantiquement, on est en présence de poupées russes, de propriétés multivaluées. Vous pourriez donc identifier FILIALE relativement à GROUPE, GROUPE relativement à CLUSTER_SEGMENT et CLUSTER_SEGMENT relativement à CLUSTER et éviter ainsi des jointures copieuses. Ainsi, au niveau de la table FILIALE, vous connaitriez déjà le cluster auquel appartient chaque filiale (même chose au niveau de la table SALE). En plus, sur le plan des performances on y gagne.


Selon votre notation, un utilisateur peut jouer plusieurs rôles. Par exemple, vu du candide que je suis, Albert pourrait théoriquement jouer le rôle COUNTRY par rapport à un ou plusieurs pays, ainsi que le rôle FILIALE par rapport à une ou plusieurs filiales, et le rôle CLUSTER par rapport à un ou plusieurs clusters.

Qu’en est-il exactement ?

[punkoff]

Bonjour,


Vous utilisez le verbe « appartenir » pour le rattachement des groupes aux cluster-segments et de ceux-ci aux clusters. Sémantiquement, on est en présence de poupées russes, de propriétés multivaluées. Vous pourriez donc identifier FILIALE relativement à GROUPE, GROUPE relativement à CLUSTER_SEGMENT et CLUSTER_SEGMENT relativement à CLUSTER et éviter ainsi des jointures copieuses. Ainsi, au niveau de la table FILIALE, vous connaitriez déjà le cluster auquel appartient chaque filiale (même chose au niveau de la table SALE). En plus, sur le plan des performances on y gagne.

Hmm, oui je n'avais pas prit le problème dans ce sens là.

Et ca aurai effectivement permis un requête plus simple sur certain point..dont la requête sur la table SALE.

Selon votre notation, un utilisateur peut jouer plusieurs rôles. Par exemple, vu du candide que je suis, Albert pourrait théoriquement jouer le rôle COUNTRY par rapport à un ou plusieurs pays, ainsi que le rôle FILIALE par rapport à une ou plusieurs filiales, et le rôle CLUSTER par rapport à un ou plusieurs clusters.

Qu’en est-il exactement ?

Il en est qu'un utilisateur peut avoir de 1 à n rôle, donc oui Albert peut vraiment avoir une flopée de rôle différent.

Fonctionnellement, un utilisateur peut être manager de la france et il devra donc pouvoir accéder à toutes les données des filiales rattachée à la France.

Les données en question sont celles présentes dans la table SALE qui correspond au chiffre d'affaire de chaque filiale pour une activité / trimestre donné.

Bien sur cet utilisateur pourrai aussi être le manager mondial des savons (CLUSTER) ou le manager des savons en Belgique, etc.

[punkoff]

D'après ce que je comprends, ta table R_USR_ROL est une table associative qui dit que tel utilisateur a tel(s) rôle(s). Par contre, je ne comprends pas la signification du nom des colonnes qui la composent.

Pardon j'aurai du éclaircir ce point là.

Ces colonnes permettent de savoir, pour un rôle donné, à quoi il s'applique.

Par exemple, prenons l'utilisateur Albert, qui à un rôle de type CLUSTER, et le cluster en question sera le savon.

Dans cette table nous allons voir apparaitre une ligne :

Code :

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

1
2
3
4
url_usr_id      url_rol_id     url_cou_id    url_grp_id    url_clr_id
---------------------------------------------------------------
Albert             role_cluster   null           null             savon

Maintenant si Albert à aussi un rôle de type COUNTRY, qui concerne la France nous aurons alors une 2eme ligne dans cette table :

Code :

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

1
2
3
4
5
url_usr_id      url_rol_id     url_cou_id    url_grp_id    url_clr_id
---------------------------------------------------------------
Albert           role_cluster   null           null             savon
Albert           role_country  France      null             null

C'est moche et dénormalisé.

Si je comprends la problématique, un utilisateur qui a le rôle "cluster" peut accéder aux informations de certains clusters, un utilisateur avec le rôle "groupe" peut voir certains groupes...

Oui exactement, mais il faut bien gardez à l'esprit qu'un utilisateur peut avoir n rôle, et qu'au final il faut recouper tous ces rôles afin de pouvoir extraire les données de la table SALE, qui ne portera que sur des filiales.

Le mieux serait je pense de faire une spécialisation des utilisateurs :

Je vais parler en Merise, c'est selon moi plus clair que le schéma E/R, en me limitant à deux spécialisations.

Règles de gestion :
1) un utilisateur cluster est un utilisateur et un utilisateur peut être un utilisateur cluster.
2) un utilisateur groupe est un utilisateur et un utilisateur peut être un utilisateur groupe.
...

MCD :
utilisateur_cluster -(1,1)----etre----0,1- utilisateur
utilisateur_gtoupe -(1,1)----etre----0,1---------|

Tables :
utilisateur (usr_id, usr_nom...)
utilisateur_cluster (ucl_id_utilisateur...)
utilisateur_groupe (ugr_id_utilisateur...)

Il suffit ensuite d'associer les entités spécialisées aux entités correspondant à leur rôle :
utilisateur_cluster -0,n----accéder----0,n- cluster
utilisateur_groupe -0,n----accéder----0,n- groupe

Il me faut un peu de temps pour réfléchir à cette proposition.

Cependant j'avais modéliser quelque chose s'en approchant à l'époque et je n'était pas arrivé à une situation beaucoup plus simple pour la problématique donnée.

J'étais plus parti sur une décomposition des rôle par contre, donc c'était un peu différent.

Edit : Ah je ne l'ai pas mis dans le descriptif de base car je ne pensais pas que ça allait influer ... mais la notion de rôle est importante car il permet de gérer les droits sur les différents écran de l'application :

ROLE_TYPE-0,n---------1,1-ROLE-0,n---------ASSOCIER--------0,n-ECRAN

La relation ASSOCIER possède des attribut CRUD sur les écrans en question.

Ceci est voulu car une role de type filiale n'aura pas les droits sur certain écran de management ou de plus haut niveau (par exemple un écran récapitulatif accessible que par les rôle CLUSTER ou supérieur)

[punkoff]

Bonjour Cinephil,

Je reviens donc sur votre proposition.

Quand j'ai modélisé votre solution au niveau du MPD j'ai grandement hésité à dégager les tables d'associations entre les tables utilisateur_cluster et t_cluster car je ne vois pas trop ce que celà apporte à part de la redondance ?

Enfin, voici des scripts de création + alimentation basique des tables pour du PostgreSql

création :

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
29
30
31
32
 
create table t_utilisateur (usr_id serial primary key, usr_name varchar(32));
 
create table t_usr_groupe (ugr_usr_id integer references t_utilisateur(usr_id) primary key);
 
create table t_usr_cluster (ucl_usr_id integer references t_utilisateur(usr_id) primary key);
 
 
create table R_USR_GRP (usr_id integer references t_usr_groupe(ugr_usr_id), grp_id integer references t_groupe(grp_id), primary key (usr_id, grp_id));
create table R_USR_CLR (usr_id integer references t_usr_cluster(ucl_usr_id), clr_id integer references t_cluster(clr_id), primary key (usr_id, clr_id));
 
 
create table t_cluster (clr_id serial primary key, clr_name varchar(32));
 
create table t_clust_seg (cls_id serial primary key, cls_clr_id integer references t_cluster(clr_id), cls_name varchar(32));
 
create table t_country (cou_id serial primary key, cou_name varchar(32));
 
create table t_year (yea_id serial primary key, yea_year integer);
 
create table t_quarter (qua_id serial primary key, qua_yea_id integer references t_year(yea_id), qua_quarter char(2));
 
create table t_groupe (grp_id serial primary key, grp_name varchar(32), grp_cls_id integer references t_clust_seg(cls_id));
 
create table t_filiale (fil_grp_id integer references t_groupe(grp_id), fil_cou_id integer references t_country(cou_id), primary key(fil_grp_id, fil_cou_id));
 
create table t_activity (act_id serial primary key, act_name varchar(32));
 
create table t_sale (sal_grp_id integer, sal_cou_id integer, sal_qua_id integer references t_quarter(qua_id), sal_act_id integer references t_activity(act_id),
sal_amount decimal(10,2),
foreign key (sal_grp_id, sal_cou_id) references t_filiale(fil_grp_id, fil_cou_id),
primary key(sal_grp_id, sal_cou_id, sal_qua_id, sal_act_id));

Alimentation :

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
 
insert into t_country (cou_name) values ('France'), ('Belgique');
insert into t_cluster (clr_name) values ('Savon');
insert into t_clust_seg (cls_clr_id, cls_name) values (1, 'Lessive');
insert into t_groupe (grp_cls_id, grp_name) values (1, 'Ariel');
insert into t_groupe (grp_cls_id, grp_name) values (1, 'Omo');
insert into t_filiale values (1, 1);
insert into t_filiale values (2, 2);
insert into t_year (yea_year) values (2011);
insert into t_quarter(qua_yea_id, qua_quarter) values (1, 'Q1');
insert into t_activity (act_name) values ('Distribution');
insert into t_sale values (1, 1, 1, 1, 200000);
 
insert into t_utilisateur (usr_name) values ('Albert');
insert into t_usr_groupe values (1);
insert into t_usr_cluster values (1);
 
 
insert into r_usr_grp values (1, 1);
insert into r_usr_grp values (1, 2);
insert into r_usr_clr values (1, 1);

Ceci étant fait, revenons au problèmes initial.

Notre utilisateur Albert veux donc connaitre le CA de chacune des filiales dont il est le manager.

Dans le jeux de donné fournit, nous avons donc :
cluster : Savon
cluster-segement : Lessive
Groupe : Ariel, Omo
Pays : France, Belgique

Filiale : {Ariel, France}, {Omo, Belgique}


Albert à un rôle Cluster sur le savon, et un rôle groupe sur Ariel (ok redondance des rôles mais ca n'est pas le problème).

Donc pour arriver à nos fin, il va falloir extraire les filiales dont Albert peut voir les CA :

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 a.* 
from t_filiale a
where exists (
	select 1 
	from r_usr_grp b 
	where b.usr_id = 1 and b.grp_id = a.fil_grp_id)
or exists (
	select 1 
	from r_usr_clr b
	inner join t_clust_seg c on b.clr_id = c.cls_clr_id
	inner join t_groupe d on c.cls_id = d.grp_cls_id
	where d.grp_id = a.fil_grp_id and b.usr_id = 1)
or exists ...

Et recouper ceci avec la table des SALE (ou directement tapper dans la table des SALE ca évitera des jointures)

Ici nous n'avons que 2 rôles différent alors qu'en réalité il y en a 6 à ce jour.

Donc cette méthode implique :
- Création et maintient d'une table relation par type de rôle différent + des traitements niveau applicatif afin d'insérer dans les bonnes tables les données (ou maintient de trigger)
- Requête non simplifié par rapport à la méthode original.
- Méthode carré, il n'y a plus de table "four-tout"

Le résultat reste, je trouve, compliqué que se soit au niveau du maintient de la solution que de son exploitation, il n'y a peut-être pas plus simple.

(la requête sur les sales peut se trouver ici en version original http://www.developpez.net/forums/d10...formance-cube/)

[CinePhil]

À partir de ton nouveau modèle, faisons une vue pour avoir toutes les filiales d'un cluster :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
CREATE VIEW v_filiales_cluster AS
SELECT clr.clr_id, clr.clr_name, 
	s.cls_id, s.cls_name,
	g.grp_id, g.grp_name,
	cou.cou_id, cou.cou_name
FROM t_cluster clr
INNER JOIN t_clust_seg s ON s.cls_clr_id = clr.clr_id
	INNER JOIN t_groupe g ON g.grp_cls_id = s.cls_id
		INNER JOIN t_filiale f ON f.fil_grp_id = g.grp_id
			INNER JOIN t_country cou ON cou.cou_id = f.fil_cou_id;

Puis utilisons cette vue pour connaître le CA des filiales des clusters supervisés par Albert :

Code SQL :

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

1
2
3
4
5
6
7
8
SELECT vfc.grp_name, vfc.cou_name, s.sal_amount
FROM t_sale s
INNER JOIN v_filiales_cluster vfc
	ON vfc.grp_id = s.sal_grp_id
	AND vfc.cou_id = s.sal_cou_id
	INNER JOIN r_usr_clr ruc ON ruc.clr_id = vfc.clr_id
		INNER JOIN t_utilisateur u ON u.usr_id = ruc.usr_id
WHERE u.usr_name = 'Albert'

Tu peux ainsi faire des vues pour connaître les filiales de chaque rôle et une requête UNION pour connaître toutes les filiales de tous les rôles d'un utilisateur.

Je constate quand même effectivement que je ne me sers pas de la table spécialisée t_usr_cluster dans ma requête.

Tu pourrais donc peut-être plutôt conserver ton schéma pour l'attribution des rôles aux utilisateurs mais en simplifiant la table associative :
UTILISATEUR -1,n----avoir----0,n-ROLE

t_utilisateur (usr_id, usr_name...)
t_role (rol_id, rol_name)
r_usr_role (rur_id_usr, rur_id_role)

Ensuite tu conserves les tables associatives r_usr_clr, r_usr_grp... du nouveau schéma :
UTILISATEUR -0,n----superviser----0,n- CLUSTER
|-------------------0,n----superviser----0,n- GROUPE

Avec une contrainte CHECK sur chaque table associative pour vérifier que l'utilisateur à bien le rôle nécessaire pour accéder à la table associée (cluster, groupe...).

Ça me semble à la fois plus rigoureux (plus de bonshommes NULL) et pas plus compliqué.

Bon courage !

[punkoff]

Oui c'est effectivement plus rigoureux

merci.