Discussion :

[Monstros Velu]

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 !

[CinePhil]

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.

[Monstros Velu]

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é.

[CinePhil]

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.

[Monstros Velu]

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

[CinePhil]

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.

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.

[Monstros Velu]

=> 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.

[CinePhil]

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.

[fsmrel]

Bonsoir,

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'>.

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.

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.
■

Pour tout savoir sur la normalisation :

Database Design and Relational Theory: Normal Forms and All That Jazz (Second Edition), Apress 2019.

[Monstros Velu]

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