Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Schéma Discussion :

Modélisation de paires non ordonnées


Sujet :

Schéma

  1. #1
    Membre confirmé
    Modélisation de paires non ordonnées
    Bonjour,

    j'ai un soucis de modélisation.

    J'ai une liste d'options qui peuvent être compatibles ou incompatibles entre elles, 2 à 2. Je n'ai pas encore l'information de si je vais avoir une liste inclusive (que celles compatibles entre elles), exclusives (que les incompatibles entre elles), ou une description complète ; dans ce cas, si l'information n'apparait pas, c'est qu'il y a un trou dans la raquette et les traitements s'appuyant sur ces données doivent juste s'arrêter en erreur. Je vais arbitrairement partir sur la description complète, pour le moment.

    La modélisation {option1, option2, est_compatible} ne me plaît pas, car, déjà, elle ne respecte pas les règles de Codd. Je serais obligé d'avoir une ligne pour A et B et une ligne pour B et A : le risque d'incohérence est trop fort après une mise à jour.

    J'ai pensé à la modélisation suivante :
    groupe_option{option, grpid}
    groupe_compatible{grpid, est_compatible}

    Ainsi, si l'option A et B sont compatibles, mais A et C ne le sont pas, et on ne sait pas pour B et C, j'ai les données suivantes :
    groupe_option
    (A, 1)
    (B, 1)
    (C, 2)
    (A, 2)

    groupe_compatible
    (1, oui)
    (2, non)

    qu'en pensez-vous ?

    Bonne journée !

  2. #2
    Modérateur

    Bonjour,

    Règle de gestion :
    Une option peut être compatible avec une option.

    Nota : on a ici une règle de gestion réciproque, ce qui explique la requête plus bas.

    MCD :
    Option -0,n----est_compatible----0,n- Option

    Tables :
    te_option_opt (opt_id, opt_nom...)
    tj_opt_compatible_opt_oco (oco_id_option_1, oco_id_option_2)

    Pour savoir avec quelle(s) option(s) est compatible l'option d'identifiant 1 :
    Code SQL :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    SELECT o1.opt_id, o1.opt_nom
    FROM te_option_opt o1
    INNER JOIN tj_opt_compatible_opt_oco j1 ON j1.oco_id_option_2 = o1.opt_id
    WHERE j1.oco_option_1 = 1
     
    UNION
     
    SELECT o2.opt_id, o2.opt_nom
    FROM te_option_opt o2
    INNER JOIN tj_opt_compatible_opt_oco j2 ON j2.oco_id_option_1 = o2.opt_id
    WHERE j2.oco_option_2 = 1


    Ainsi, même s'il n'y a qu'une fois un couple {1, 2} ou {2,1}, L'option n° 2 sera donnée par la requête.

    À tester mais ça veut dire que la modélisation par couples est la bonne.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  3. #3
    Membre confirmé
    Bonjour,

    et merci pour votre réponse.

    Cependant, je pense que cette modélisation ne respecte pas la règle 2 de Codd
    Règle 2
    Garantie d'accès :
    Toutes les données doivent être accessibles sans ambiguïté. Cette règle est essentiellement un ajustement de la condition fondamentale pour des clefs primaires. Elle indique que chaque valeur scalaire individuelle dans la base de données doit être logiquement accessible en indiquant le nom de la table contenante, le nom de la colonne contenante et la valeur principale primaire de la rangée contenante.
    Dans l'exemple, si je cherche les options compatibles avec l'option 2, je ne trouve pas l'option 1, car je devrais effectuer ma recherche sur une autre colonne que pour la recherche pour les compatibilité de l'option 1 : il y a une ambiguïté.

  4. #4
    Modérateur

    Je n'ai toujours pas testé ma solution mais je pense que vous avez tort.

    Il s'agit d'enregistrer dans la table associative un couple. Que celui-ci soit exprimé sous la forme {1, 2} ou {2, 1}, c'est le même couple et pour trouver ce couple, quel que soit le sens de l'interrogation, il suffit a priori de faire ma requête UNION qui cherche le couple dans les deux sens.

    Donc si je cherche les options compatibles avec l'option 1, j'aurai l'option 2, que le couple soit enregistré dans un sens ou dans l'autre, avec une seule requête.
    Et comme l'UNION élimine les doublons, si le couple est enregistré dans les deux sens, la requête ne donnera pas deux fois l'option 2.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  5. #5
    Membre confirmé
    Bonjour,

    je ne dis pas que votre solution ne fonctionne pas, et je pense même qu'elle fonctionne. Mais je pense aussi que ce n'est pas relationnel...

  6. #6
    Modérateur

    Test...

    Création des 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
    CREATE TABLE `te_option_opt` (
     `opt_id` int(11) NOT NULL AUTO_INCREMENT,
     `opt_nom` varchar(20) NOT NULL,
     PRIMARY KEY (`opt_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;
     
    CREATE TABLE `tj_opt_compatible_opt_oco` (
     `oco_id_option_1` int(11) NOT NULL,
     `oco_id_option_2` int(11) NOT NULL,
     KEY `fk_oco_option_1` (`oco_id_option_1`),
     KEY `fk_oco_option_2` (`oco_id_option_2`),
     CONSTRAINT `fk_oco_option_1` FOREIGN KEY (`oco_id_option_1`) REFERENCES `te_option_opt` (`opt_id`) ON DELETE CASCADE ON UPDATE CASCADE,
     CONSTRAINT `fk_oco_option_2` FOREIGN KEY (`oco_id_option_2`) REFERENCES `te_option_opt` (`opt_id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


    Remplissage des tables :
    Code SQL :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    -- 4 options :
    INSERT INTO te_option_opt (opt_nom)
    VALUES ('Option 1'), ('Option 2'), ('Option 3'), ('Option 4');
     
    -- Couples {1, 2}, {3, 1}, {2, 3}, {2, 4}
    INSERT INTO tj_opt_compatible_opt_oco (oco_id_option_1, oco_id_option_2)
    VALUES (1, 2), (3, 1), (2, 3), (2, 4);


    Je cherche les options compatibles avec l'option 1 :
    Code SQL :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    SELECT o1.opt_id, o1.opt_nom
    FROM te_option_opt o1
    INNER JOIN tj_opt_compatible_opt_oco j1 ON j1.oco_id_option_2 = o1.opt_id
    WHERE j1.oco_id_option_1 = 1
     
    UNION
     
    SELECT o2.opt_id, o2.opt_nom
    FROM te_option_opt o2
    INNER JOIN tj_opt_compatible_opt_oco j2 ON j2.oco_id_option_1 = o2.opt_id
    WHERE j2.oco_id_option_2 = 1


    Résultat :
    opt_id opt_nom
    2 Option 2
    3 Option 3
    J'ajoute le couple réciproque {1, 3} et je relance la requête SELECT :
    INSERT INTO tj_opt_compatible_opt_oco (oco_id_option_1, oco_id_option_2)
    VALUES (1, 3);
    Résultat :
    opt_id opt_nom
    2 Option 2
    3 Option 3
    => Pas de doublon

    Si on veut empêcher l'enregistrement de couples réciproques, on peut faire un trigger qui l'empêche.
    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
    DELIMITER //
    CREATE TRIGGER tg_oco_couple_existant BEFORE INSERT ON tj_opt_compatible_opt_oco FOR EACH ROW
    BEGIN
    	SELECT COUNT(*) INTO @nb
    	FROM tj_opt_compatible_opt_oco
    	WHERE oco_id_option_1 = NEW.oco_id_option_2
        	AND oco_id_option_2 = NEW.oco_id_option_1;
     
        IF @nb > 0 THEN
        	SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Le couple réciproque existe déjà';
        END IF;
    END;
    //
    DELIMITER ;


    Je supprime le couple réciproque {3, 1} et j'essaie de le réinsérer :
    Code SQL :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    DELETE FROM tj_opt_compatible_opt_oco
    WHERE oco_id_option_1 = 3
        AND oco_id_option_2 = 1;
     
    INSERT INTO tj_opt_compatible_opt_oco (oco_id_option_1, oco_id_option_2)
    VALUES (3, 1);


    Résultat :
    MySQL a répondu : Documentation
    #1644 - Le couple réciproque existe déjà
    Je ne vois pas en quoi ma solution contreviendrait à la règle de Codd que vous citez.
    Citation Envoyé par Codd
    Toutes les données doivent être accessibles sans ambiguïté.
    => C'est le cas puisque j'ai bien toutes les options compatibles avec l'option 1 et sans doublon, que j'autorise les couples réciproques ou pas.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  7. #7
    Membre confirmé
    Citation Envoyé par CinePhil Voir le message
    => C'est le cas puisque j'ai bien toutes les options compatibles avec l'option 1 et sans doublon, que j'autorise les couples réciproques ou pas.
    Bonjour,

    pour accéder à une valeur sans ambiguïté dans le modèle relationnel, il faut utiliser le nombre de la table, la valeur de la clef primaire et le nom de la colonne contenant la valeur (c'est ce qui compose la relation, et c'est dans la définition de ce qu'est une base relationnelle).
    Or, là, on ne peut pas accéder aux options autorisée pour une option choisie en utilisant cette relation (tj_opt_compatible_opt_oco x oco_id_option_1 x oco_id_option_2). Parce que parfois on cherche sur une dépendance fonctionnelle de oco_id_option_1 vers oco_id_option_2, et parfois, on cherche dans l'autre sens.

    Je vois bien que "ça marche". Et je connais pas mal de cas de dénormalisation où "ça marche". Mais je cherche une solution normalisée.

  8. #8
    Modérateur

    pour accéder à une valeur sans ambiguïté dans le modèle relationnel, il faut utiliser le nombre de la table, la valeur de la clef primaire
    Comme la clé primaire de cette table associative est composée de deux colonnes, la clé primaire est le couple des deux options compatibles.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  9. #9
    Expert éminent sénior
    Bonsoir,

    Citation Envoyé par Monstros Velu Voir le message

    je pense que cette modélisation ne respecte pas la règle 2 de Codd

    Règle 2
    Garantie d'accès :
    Toutes les données doivent être accessibles sans ambiguïté. Cette règle est essentiellement un ajustement de la condition fondamentale pour des clefs primaires. Elle indique que chaque valeur scalaire individuelle dans la base de données doit être logiquement accessible en indiquant le nom de la table contenante, le nom de la colonne contenante et la valeur principale primaire de la rangée contenante.

    Votre définition de la règle n’est pas formellement celle de Codd, laquelle est en fait la suivante (Is your DBMS really relational? ComputerWorld, October 14, 1985) :

    Each and every datum (atomic value) in a relational database is guaranteed to be logically accessible by resorting to a combination of R-table name, primary key value, and column name.



    CinePhil propose les tables ;

    te_option_opt (opt_id, opt_nom...)
    tj_opt_compatible_opt_oco (oco_id_option_1, oco_id_option_2)

    Ces tables sont évidemment structurellement conformes au modèle relationnel de données de Codd.

    Une variante de la belle ouvrage de CinePhil ; pour simplifier et pour mon confort visuel, je laisse tomber la table te_option_opt et renomme tj_opt_compatible_opt_oco en PAIRE. Passons à SQL :

    CREATE TABLE PAIRE
    (
            optionCode_A    VARCHAR(4)   NOT NULL 
          , optionCode_B    VARCHAR(4)   NOT NULL 
        , CONSTRAINT Paire_PK PRIMARY KEY (optionCode_A, optionCode_B)
    ) ; 


    Comme la clé primaire {optionCode_A, optionCode_B} est un ensemble au sens de la théorie des ensembles, à laquelle Codd tient particulièrement, les valeurs <'a', 'b'> et <'b', 'a'> doublonnent, donc on empêchera cela au moyen d’une contrainte conforme à la norme SQL :  

    CREATE ASSERTION PAIRE_UNIQUE
        CHECK
            (NOT EXISTS 
                 SELECT a.optionCode_A, a.optionCode_B
                 FROM   PAIRE as a
                   JOIN PAIRE as b
                         ON a.optionCode_A = b.optionCode_B
                        AND a.optionCode_B = b.optionCode_A)
    ;


    Si le SGBD ne connaît pas l’instruction CREATE ASSERTION, on passera par un trigger (cf. CinePhil).

    Créons par exemple les paires

    <'a', 'b'>
    <'a', 'd'>
    <'c', 'e'>
    <'d', 'f'>


    Code SQL :

    insert into PAIRE values ('a', 'b') ;
    insert into PAIRE values ('a', 'd') ;
    insert into PAIRE values ('c', 'e') ;
    insert into PAIRE values ('d', 'f') ;
    

    Pour produire une table contenant l’ensemble des paires, <'a', 'b'>, <'b', 'a'>, etc. :

    CREATE TABLE RESULTAT
    (
            optionCode_A    VARCHAR(4)   NOT NULL 
          , optionCode_B    VARCHAR(4)   NOT NULL 
        , CONSTRAINT RESULTAT_PK PRIMARY KEY (optionCode_A, optionCode_B)
    ) ; 
    
    go
    
    WITH t1 as
    (SELECT DISTINCT x.optionCode_A, y.optionCode_B
     FROM   PAIRE as x JOIN PAIRE as y ON x.optionCode_A < y.optionCode_B)
    ,
    t2 as 
    (SELECT DISTINCT x.optionCode_A, x.optionCode_B
     FROM   t1 as x JOIN PAIRE as y 
            ON  x.optionCode_A = y.optionCode_A 
            AND x.optionCode_B = y.optionCode_B)
    ,
    t3 as 
    (SELECT DISTINCT x.optionCode_B, y.optionCode_A
     FROM   PAIRE as x JOIN PAIRE as y 
            ON x.optionCode_B > y.optionCode_A)
    , 
    t4 as 
    (SELECT DISTINCT x.optionCode_B, x.optionCode_A
     FROM   t3 as x JOIN PAIRE as y 
            ON  x.optionCode_A = y.optionCode_A 
            AND x.optionCode_B = y.optionCode_B)
    
    INSERT  INTO RESULTAT
        SELECT * FROM t2 UNION SELECT * FROM t4
    
    go
    
    SELECT * FROM RESULTAT ;
    


    =>

    optionCode_A   optionCode_B
    
    a              b
    a              d
    b              a
    c              e
    d              a
    d              f
    e              c
    f              d
    


    Pas de difficulté particulière pour sélectionner par exemple la paire <'a', 'b'> et/ou sa soeur <'b', 'a'>.


    Citation Envoyé par Monstros Velu Voir le message

    Je vois bien que "ça marche". Et je connais pas mal de cas de dénormalisation où "ça marche". Mais je cherche une solution normalisée.


    Les tables PAIRE et RESULTAT sont en 6NF.


    Citation Envoyé par CinePhil Voir le message
    Comme la clé primaire de cette table associative est composée de deux colonnes, la clé primaire est le couple des deux options compatibles.
    Dans ces conditions la table est en 6NF.
    &#9632;

    Pour tout savoir sur la normalisation :

    Faites simple, mais pas plus simple ! (A. Einstein)
    E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    Je ne réponds pas aux questions techniques par MP. Les forums sont là pout ça.
    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench

  10. #10
    Membre confirmé
    Merci à vous deux, et désolé CinePhil d'avoir été un peu long à comprendre.

    Je n'avais pas entendu la paire option_a, option_b comme un ensemble au sens de la théorie des ensemble dans la table, et qu'elle constituait donc la clef primaire et respecte les règles de Codd. Ca me parait évident maintenant

###raw>template_hook.ano_emploi###