how to represent symmetric many to many relationship

Answer 1

Well, you could certainly just assume that all friendships are symmetric and store that friendship only once, but that would mean that when you want to query for all of Taher's friends, you have to look for his ID in either column.

Alternately you could have a separate table of relationship ID's, and then a one-to-many table of relationship to user. This has the advantage that it would allow multi-person relationships if someday you want that, and would let you add meta-data about the relationship (when it started, who suggested it, whatever).

   User                
Id    Name          
1     Taher         
2     Deepak        

Relationship              
Id     StartDate           
1      2010-08-23

UserRelationship
RelationshipId UserId
1                1
1                2

On the other hand, on Facebook, for example, I can "friend" someone and they can decide not to friend me back. If you don't have the "redundant" approach that you're using now, how will you represent that not-yet-reciprocal friendship?

Answer 2

The obvious solution is to store just 1 record per each pair of friends. However, it makes harder to maintain uniqueness; all queries for getting/updating/removing friend relationship become more complex compared to "redundant" solution. Also, the solution with 2 records allows you maintain friends requests (E.g., UserA asks UserB to be a friend. UserB can confirm or reject this request). So I'd say redundancy is perfectly valid in this case.

Answer 3

Make up a rule like 'the first ID value is always lower than the second ID value,' so you can ensure there are no duplicates.

In that case, pair (4,1) is invalid, and pair (4,3) would be stored as (3,4).

Answer 4

If you did decide to use the symmetric design then it might be useful to ensure that every friendship is always bidirectional. You can try this:

CREATE TABLE Friendship
 (UserId1 INT NOT NULL REFERENCES Users (UserId),
  UserId2 INT NOT NULL,
  PRIMARY KEY (UserId1, UserId2),
  FOREIGN KEY (UserId2, UserId1) REFERENCES Friendship (UserId1, UserId2));

Answer 5

If you store the two rows, you will be required to enforce an integrity rule that ensures that friendships always come in pairs.

Store only one row in the table (say, FR), and create a view SYMFR as

SELECT x,y FROM FR UNION SELECT x as y, y as x FROM FR