Gestion des doublons sur une clef inversée [Résolu]

[Sve@r]

Bonsoir à tous

J'ai été confronté ce matin à un petit soucis d'organisation et je n'ai pas su décider laquelle était la meilleure

Je dois gérer une relation de voisinage entre deux items. J'ai donc créé la table principale

Code :

1
2
3
4
5
6
CREATE TABLE item (
    id_item integer NOT NULL,
    ..., ..., ...
);
 
ALTER TABLE item ADD constraint pkey_item primary_key (id_item);

Jusque là, pas de souci.

Puis, ma table voisinage

Code :

1
2
3
4
5
CREATE TABLE voisin (
    id_item1 integer NOT NULL,
    id_item2 integer NOT NULL
);
ALTER TABLE voisin ADD constraint pkey_item primary_key (id_item1, id_item2);

Puis les foreign keys. Jusque là, ça va encore.

Mais mon problème c'est quand je veux insérer un voisin style item 1 voisin avec item 5. Là, j'ai 2 choix possibles
1) soit je rentre systématiquement dans ma table le tuple (1, 5) puis le tuple (5, 1)

2) soit je ne rentre que le tuple (1, 5) en disant "si 1 est voisin avec 5, alors 5 est automatiquement voisin avec 1".
Mais dans ce cas, j'aimerais bloquer l'insertion du tuple (5, 1) si le tuple (1, 5) est déjà dans la table. Et là, j'ai pas trouvé comment faire
J'ai essayé de créer une contrainte d'unicité sur le groupe (id_item2, id_item1) mais ça n'a rien empêché du tout.

Et donc ma question c'est: vaut-il mieux choisir la solution 1 et avoir chaque relation de voisinage écrite 2 fois ou vaut-il mieux utiliser la solution 2 mais sans pouvoir empêcher une insertion existant déjà dans le sens inverse ? Et corollaire: y a-t-il moyen, dans Postgres, d'empêcher cette insertion si le tuple inverse est déjà présent dans la table ?

Merci à tous

[vmolines]

Les deux solutions sont possibles.

Ma préférence va à la 1ère car elle modélise la réalité. A savoir que votre lien est bidirectionnel donc les deux sens doivent être stockés.

La deuxième solution fonctionne aussi. Mais elle obligera a requêter dans les deux sens.

Pour les éléments techniques d'implémentation, vous pouvez utiliser les triggers et les vues. La première solution peut utiliser un trigger after insert qui insère le 2ème tuple dans le sens inverse du premier. La deuxième solution peut utiliser un trigger before insert qui vérifie si le tupe inverse n'est pas déjà présent et une vue qui reconstruit les deux sens :

Code :

1
2
3
4
5
6
CREATE VIEW ... AS 
SELECT ID_ITEM1 AS ID_ITEM1 , ID_ITEM2 AS ID_ITEM2
FROM VOISIN
UNION ALL
SELECT ID_ITEM2, ID_ITEM1
FROM VOISIN

[Sve@r]

Envoyé par vmolines

Les deux solutions sont possibles.

Ma préférence va à la 1ère car elle modélise la réalité. A savoir que votre lien est bidirectionnel donc les deux sens doivent être stockés.

Salut

C'est aussi ce que je pense. Parce que, plus tard, quand il faudra chercher les voisins de X faudra chercher "select item2 where item1=X union select item1 where item2=X". Et là, ça risque de devenir bien ch...

Envoyé par vmolines

Pour les éléments techniques d'implémentation, vous pouvez utiliser les triggers et les vues. La première solution peut utiliser un trigger after insert qui insère le 2ème tuple dans le sens inverse du premier. La deuxième solution peut utiliser un trigger before insert qui vérifie si le tupe inverse n'est pas déjà présent et une vue qui reconstruit les deux sens :

Code :

Je connaissais les trigger mais j'y suis pas encore habitué. J'avais bien pensé qu'un trigger me permettrait de checker l'inversion mais j'avais pas pensé aussi au trigger qui insère automatiquement le tuple inversé.

Toutefois, aujourd'hui j'ai trouvé une solution simple qui permet de garantir l'unicité sans se préoccuper du sens du tuple.
Il suffit de créer un index unique sur ((id_item1 + id_item2), (id_item1 * id_item2))
Etant donné que l'addition et multiplication sont commutatives, le produit et la somme seront égaux quel que soit le sens du tuple. Et deux tuples différents donneront alors un couple (somme, produit) différent.
C'est en pensant à l'algo de Diffie et Hellman, qui avaient aussi une problématique analogue, que j'ai eu cette idée...

Ceci dit, j'aime bien l'idée du trigger qui insère automatiquement le tuple inverse. Merci beaucoup