Modéliser un lien (n,m) d'une table vers elle-même

[vince3147]

Bonjour,
Je voudrais savoir quelle est la meilleure méthode pour modéliser un lien (n,m) d'une table vers elle-même.
Exemple: si facebook avait une table "Utilisateurs", comment représenteriez-vous un lien d'amitié ?
Si Pierre est ami avec Paul et Jacques (Paul et Jacques ne sont pas amis)

- soit je stocke dans ma table d'association un seul lien (arbitrairement)
(ID_Paul, ID_Pierre)
(ID_Pierre, ID_Jacques)
=> Dans ce cas là, toutes les requêtes de l'application doivent être dédoublées : pour les amis de Pierre : select champ2 from Utilisateurs where champ1=ID_Pierre UNION select champ1 from Utilisateurs where champ2=ID_Pierre

- soit je stocke tout en double
(ID_Paul, ID_Pierre)
(ID_Pierre, ID_Paul)
(ID_Pierre, ID_Jacques)
(ID_Jacques, ID_Pierre)
ce qui permet d'avoir une requête propre (pour les amis de Pierre select champ2 from Utilisateurs where champ1=ID_Pierre), mais l'information est dupliquée.

D'autres solutions? Qu'est-ce qui est recommandé?
Merci

[lerieure]

soit avec cette méthode

les CTE

soit avec celle-ci

arborescence intervallaire

après tout dépend de la profondeur et de ce que vous allez concrètement faire

[SQLpro]

voici les tables adéquates

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE T_PERSONNE_PRS
(PRS_ID                INT NOT NULL PRIMARY KEY,
 PRS_NOM               ...);
 
CREATE TABLE T_AMI
(AMI_ID                INT NOT NULL PRIMARY KEY,
 AMI_DEMANDE           DATE NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRS_ID_DEMANDEUR      INT NOT NULL 
    FOREIGN KEY REFERENCES T_PERSONNE_PRS (PRS_ID),
 PRS_ID_DESTINATAIRE   INT NOT NULL 
    FOREIGN KEY REFERENCES T_PERSONNE_PRS (PRS_ID),
 AMI_REPONSE           DATE NOT NULL DEFAULT CURRENT_TIMESTAMP,    
 AMI_OK                BOOLEAN NOT NULL DEFAULT 0);

mais si vous ne voulez pas de doublosn d'amis (pierre ami avec paul et paul avec pierre, il faut rajouter une contrainte sous forme d'assertion, du genre :

Code :

1
2
3
4
5
6
7
8
CREATE ASSERTION ASS_AMI_NON_RECIPROQUE
CHECK NOT EXISTS (SELECT *
                  FROM   T_AMI AS T1
                         INNER JOIN T_AMI AS T2
                               ON T1.PRS_ID_DEMANDEUR = T2.PRS_ID_DESTINATAIRE
                                  AND T1.PRS_ID_DESTINATAIRE = T2.PRS_ID_DEMANDEUR
                  WHERE  T1.AMI_OK = true
                    AND  T2.AMI_OK = true);

A +