Packs de services

Bonjour à tous,

J'ai un modèle de données que voici :

Pièce jointe 193405

Petites explications :
Un utilisateur peut choisir un pack.
Un pack contient un ou plusieurs services.
Ce que je veux, c'est pouvoir savoir si un utilisateur a consommé un service dont il à souscrit (il manque un champ booléen dans la relation User_Service).

Il me semble avoir vu quelque part qu'il faut éviter d'avoir plusieurs chemin pour retrouver une information.
Dans mon cas, il y aurait effectivement 2 chemins pour retrouver les services d'un utilisateur.
Est-ce vraiment un mauvaise chose.
Si oui, comment modéliser mon problème de façon propre?

Merci

Bonsoir Farid,

Citation:

---

Envoyé par FaridM

Un utilisateur peut choisir un pack.

---

Au plus un pack ? Je pars sur cette base.

Citation:

---

Envoyé par FaridM

Il me semble avoir vu quelque part qu'il faut éviter d'avoir plusieurs chemin pour retrouver une information.

---

Il arrive qu’on ne puisse pas éviter cela, auquel cas on est amené à mettre en œuvre des contraintes de chemin, comme par exemple ici.

Manifestement, vous êtes confronté à ce problème, mais votre modélisation n’est pas en cause. Cependant injecter des anomalies dans la base de données se fait sans aucune difficulté !

Illustrons en passant à l'acte :

CREATE TABLE SERVICE
(
        service_id              CHAR(3)       NOT NULL
      , service_label           VARCHAR(32)   NOT NULL
    , CONSTRAINT SERVICE_PK PRIMARY KEY (service_id)          
) ;

CREATE TABLE PACK
(
        pack_id                 CHAR(3)       NOT NULL
      , pack_label              VARCHAR(32)   NOT NULL
    , CONSTRAINT PACK_PK PRIMARY KEY (pack_id)          
) ;

CREATE TABLE PACK_SERVICE
(
        pack_id                 CHAR(3)       NOT NULL
      , service_id              CHAR(3)       NOT NULL
    , CONSTRAINT PACK_SERVICE_PK PRIMARY KEY (pack_id, service_id)
    , CONSTRAINT PACK_SERVICE_PACK_FK FOREIGN KEY (pack_id) REFERENCES PACK (pack_id)     
    , CONSTRAINT PACK_SERVICE_SERVICE_FK FOREIGN KEY (service_id) REFERENCES SERVICE (service_id)  
) ;

CREATE TABLE USER
(
        user_id                 CHAR(3)       NOT NULL
      , user_nom                VARCHAR(32)   NOT NULL
      , pack_id                 CHAR(3)       NOT NULL      
    , CONSTRAINT USER_PK PRIMARY KEY (user_id) 
    , CONSTRAINT USER_PACK_FK FOREIGN KEY (pack_id) REFERENCES PACK (pack_id)     
) ;

CREATE TABLE USER_SERVICE
(
        user_id                 CHAR(3)       NOT NULL
      , service_id              CHAR(3)       NOT NULL
    , CONSTRAINT USER_SERVICE_PK PRIMARY KEY (user_id, service_id) 
    , CONSTRAINT USER_SERVICE_USER_FK FOREIGN KEY (user_id) REFERENCES USER (user_id)     
    , CONSTRAINT USER_SERVICE_SERVICE_FK FOREIGN KEY (service_id) REFERENCES SERVICE (service_id)  
) ;

INSERT INTO SERVICE (service_id, service_label) VALUES
    ('s01', 'service 01'), ('s02', 'service 02'), ('s03', 'service 03'), ('s04', 'service 04'), ('s05', 'service 05') 
;    

INSERT INTO PACK (pack_id, pack_label) VALUES
    ('p01', 'pack 01'), ('p02', 'pack 02'), ('p03', 'pack 03'), ('p04', 'pack 04'), ('p05', 'pack 05') 
;

INSERT INTO PACK_SERVICE (pack_id, service_id) VALUES
    ('p01', 's01'), ('p01', 's02'), ('p01', 's03')
  , ('p02', 's02'), ('p02', 's03'), ('p02', 's04')
  , ('p03', 's03'), ('p03', 's04'), ('p03', 's05')  
;

INSERT INTO USER (user_id, user_nom, pack_id) VALUES
    ('u01', 'user 01', 'p01'), ('u02', 'user 02', 'p01'), ('u03', 'user 03', 'p03'), ('u04', 'user 04', 'p04')
;

INSERT INTO USER_SERVICE (user_id, service_id) VALUES
    ('u01', 's05'), ('u02', 's03'), ('u02', 's04'), ('u04', 's01')
;

Recherche des situations normales :

SELECT u.user_id, u.pack_id, us.service_id 
FROM   USER AS u JOIN USER_SERVICE AS us ON u.user_id = us.user_id
                 JOIN PACK_SERVICE AS ps ON us.service_id = ps.service_id
                 JOIN PACK AS p ON ps.pack_id = p.pack_id AND u.pack_id = p.pack_id
ORDER BY u.user_id, u.pack_id
;    

=>

user_id    pack_id    service_id
-------    -------    ----------
u02        p01        s03

Recherche des anomalies

SELECT DISTINCT u.user_id, u.pack_id, ps.pack_id AS pack_id_a_refuser
FROM   USER AS u JOIN USER_SERVICE AS us ON u.user_id = us.user_id
                 JOIN PACK_SERVICE AS ps ON us.service_id = ps.service_id 
WHERE NOT EXISTS (SELECT ''
                  FROM   PACK AS p
                  WHERE  u.pack_id = p.pack_id AND ps.pack_id = p.pack_id)
;                  

=>

user_id    pack_id    pack_id_a_refuser
-------    -------    -----------------
u01        p01        p03
u02        p01        p02
u02        p01        p03
u04        p04        p01

En conséquence, il faudra mettre en œuvre un trigger pour rejeter les ajouts provoquant des anomalies (même chose pour les modifications). La table à laquelle s’applique le trigger est la table USER_SERVICE, la règle à faire respecter étant la suivante :

Les services consommés par un utilisateur doivent tous être associés au pack choisi par cet utilisateur.

Autrement dit, en ce qui concerne les ajouts, pour l’utilisateur {user_id} ayant choisi un pack {pack_id} et un (ou plusieurs) service(s) {service_id}, chaque valeur de la paire {pack_id, service_id} doit être une valeur appartenant à la table PACK_SERVICE.

Le code du trigger dépend de votre SGBD.

D'autres triggers seront à prévoir concernant cette fois-ci la table PACK_SERVICE, au cas où on peut y supprimer ou modifier des lignes...

J'espère ne pas vous avoir mis le moral dans les chaussettes...

Bonsoir fsmrel, merci pour votre réponse.

C'est bien un pack par utilisateur.
Donc ma conception, il ne me reste plus qu'a blinder l' le SGBD pour éviter les erreurs.

Citation:

---

Envoyé par fsmrel

J'espère ne pas vous avoir mis le moral dans les chaussettes...

---

Pas du tout. ;)
Ma conception est bonne, c'est déjà.

Encore merci.

Il y a du changement.

Un utilisateur peut vouloir souscrire à un autre pack.

Après réflexion, j'obtiens ce schéma :

Pièce jointe 193862

Je me retrouve donc avec une relation ternaire et avec un sorte de double relation entre Pack et Service.
Du coup je suis pas très convaincu de ce schéma.
Je pense que je peux me passer de la relation entre Pack et User puisque je peux récupérer les packs en question en passant par Service.

Qu'en pensez-vous?

Bonsoir Farid,


Vous voici aux prises avec cette fois-ci une contrainte d’inclusion (cas particulier de la contrainte de chemin)...

Vous ne pouvez pas faire l’économie de l’association PACK_SERVICE, car elle représente un référentiel, un catalogue : cette association devra donc impérativement donner lieu à la table PACK_SERVICE que l’on connaît déjà.

L’association ternaire est légitime, mais un utilisateur ne doit pouvoir utiliser que des services de packs présents dans PACK_SERVICE, d’où la contrainte d’inclusion (représentée ici avec l’AGL DB-MAIN) entre les associations PACK_SERVICE et USER_PACK_SERVICE :


http://www.fsmwarden.com/developpez_...m_ternaire.png



Au stade relationnel, la table USER_PACK_SERVICE sera dotée d’une clé étrangère référençant la table PACK_SERVICE, tandis que les clés étrangères référençant les tables PACK et SERVICE deviennent sans emploi et disparaissent donc.

Le code SQL devient le suivant :

CREATE TABLE SERVICE
(
        service_id              CHAR(3)       NOT NULL
      , service_label           VARCHAR(32)   NOT NULL
    , CONSTRAINT SERVICE_PK PRIMARY KEY (service_id)          
) ;

CREATE TABLE PACK
(
        pack_id                 CHAR(3)       NOT NULL
      , pack_label              VARCHAR(32)   NOT NULL
    , CONSTRAINT PACK_PK PRIMARY KEY (pack_id)          
) ;

CREATE TABLE PACK_SERVICE
(
        pack_id                 CHAR(3)       NOT NULL
      , service_id              CHAR(3)       NOT NULL
    , CONSTRAINT PACK_SERVICE_PK PRIMARY KEY (pack_id, service_id)
    , CONSTRAINT PACK_SERVICE_PACK_FK FOREIGN KEY (pack_id) REFERENCES PACK (pack_id)     
    , CONSTRAINT PACK_SERVICE_SERVICE_FK FOREIGN KEY (service_id) REFERENCES SERVICE (service_id)  
) ;

CREATE TABLE USER
(
        user_id                 CHAR(3)       NOT NULL
      , user_nom                VARCHAR(32)   NOT NULL     
    , CONSTRAINT USER_PK PRIMARY KEY (user_id)     
) ;

CREATE TABLE USER_PACK_SERVICE
(
        user_id                 CHAR(3)       NOT NULL
      , pack_id                 CHAR(3)       NOT NULL        
      , service_id              CHAR(3)       NOT NULL
      , consumed                INT           NOT NULL      
    , CONSTRAINT USER_SERVICE_PK PRIMARY KEY (user_id, pack_id, service_id) 
    , CONSTRAINT USER_SERVICE_USER_FK FOREIGN KEY (user_id) REFERENCES USER (user_id)     
    , CONSTRAINT USER_SERVICE_PACK_FK FOREIGN KEY (pack_id, service_id) REFERENCES PACK_SERVICE (pack_id, service_id)  
) ;

INSERT INTO SERVICE (service_id, service_label) VALUES
    ('s01', 'service 01'), ('s02', 'service 02'), ('s03', 'service 03'), ('s04', 'service 04'), ('s05', 'service 05') 
;    

INSERT INTO PACK (pack_id, pack_label) VALUES
    ('p01', 'pack 01'), ('p02', 'pack 02'), ('p03', 'pack 03'), ('p04', 'pack 04'), ('p05', 'pack 05') 
;

INSERT INTO PACK_SERVICE (pack_id, service_id) VALUES
    ('p01', 's01'), ('p01', 's02'), ('p01', 's03')
  , ('p02', 's02'), ('p02', 's03'), ('p02', 's04')
  , ('p03', 's03'), ('p03', 's04'), ('p03', 's05')  
;

INSERT INTO USER (user_id, user_nom) VALUES
     ('u01', 'user 01'), ('u02', 'user 02'), ('u03', 'user 03'), ('u04', 'user 04')
;

INSERT INTO USER_PACK_SERVICE (user_id, pack_id, service_id, consumed) VALUES
    ('u01', 'p03', 's05', 25), ('u02', 'p03', 's03', 10), ('u02', 'p03', 's04', 54), ('u04', 'p01', 's03', 20)
;

SELECT * FROM USER_PACK_SERVICE ;

The good news : Adieu les triggers !

Encore merci pour votre retour.

Par contre, ici, un utilisateur ne peux pas choisir 2 fois le même pack.
Il faudrait donc que je change la clé primaire de la table USER_PACK_SERVICE en rajoutant un identifiant unique (entier auto-incrémenté ?).

Je me rends compte qu'utiliser un ORM, c'est pas forcément un avantage...

Bonsoir Farid,


Qu’entendez-vous par « ici » ? Dans ce que je vous ai proposé, un utilisateur peut choisir deux fois le même pack, puisque c’est le but de la manœuvre. Voyez dans le dernier code SQL le cas de l’utilisateur u02 : il a bien choisi deux fois le pack p03 (pour les services s03 et s04).

Je voulais dire le même pack avec les mêmes services.

Par exemple, le 15/01/2015, l'utilisateur u01 achètes le pack p01 qui lui même comprend les services s01, s02, s03.
Nous avons donc :

Code:

---

1 2 3

INSERT INTO USER_PACK_SERVICE (user_id, pack_id, service_id, consumed) VALUES ('u01', 'p01', 's01', false), ('u01', 'p01, 's02', false), ('u01', 'p01', 's03', false);

---

Le 15/01/2016, u01 à consommé les services qu'il avait acheté, il souhaite donc recommander le même pack avec les mêmes services est donc la requête précédente est réexecutée.
On se retrouve donc avec une violation de contrainte sur la clé primaire : CONSTRAINT USER_SERVICE_PK PRIMARY KEY (user_id, pack_id, service_id)

Bonsoir Farid,


Si un utilisateur peut acheter plusieurs fois la même paire {Pack, Service}, pas de problème, on intègre la date d’achat dans la clé :


http://www.fsmwarden.com/developpez_...naire_date.png



Au stade SQL :

CREATE TABLE USER_PACK_SERVICE
(
        user_id                 CHAR(3)       NOT NULL
      , pack_id                 CHAR(3)       NOT NULL        
      , service_id              CHAR(3)       NOT NULL
      , consumed                INT           NOT NULL
      , date_achat              DATE          NOT NULL      
    , CONSTRAINT USER_SERVICE_PK PRIMARY KEY (user_id, pack_id, service_id, date_achat) 
    , CONSTRAINT USER_SERVICE_USER_FK FOREIGN KEY (user_id) REFERENCES USER (user_id)     
    , CONSTRAINT USER_SERVICE_PACK_FK FOREIGN KEY (pack_id, service_id) REFERENCES PACK_SERVICE (pack_id, service_id)  
) ;


INSERT INTO USER_PACK_SERVICE (user_id, pack_id, service_id, consumed, date_achat) VALUES
    ('u01', 'p03', 's05', 25, '2015-01-01'), ('u02', 'p03', 's03', 10, '2015-01-01'), ('u02', 'p03', 's04', 54, '2015-01-01'), ('u04', 'p01', 's03', 20, '2015-01-01')
;

INSERT INTO USER_PACK_SERVICE (user_id, pack_id, service_id, consumed, date_achat) VALUES
    ('u01', 'p03', 's05', 25, '2015-01-02'), ('u02', 'p03', 's03', 10, '2015-01-02'), ('u02', 'p03', 's04', 54, '2015-01-02'), ('u04', 'p01', 's03', 20, '2015-01-02')
;

Si un utilisateur peut acheter plus d’une fois le même pack dans la journée, on fera participer l’heure d’achat à la clé. Si l’utilisateur peut acheter le même pack au même moment, on fera participer la quantité à la clé, ou bien on fera de l’incrémentation relative...

Qu’en est-il ? Un utilisateur peut-il acheter plus d’une fois le même pack dans la journée ?

Un utilisateur peut acheter qu'un pack par jour, donc la date devrait suffire.

Donc tout est pour le mieux :D

En passant, n'hésitez pas à voter pour les messages qui vous ont été utiles.

Bonne route !