database design - how to represent symmetric many to many relationship

Question

suppose i have a entity called USER and a relationship FRIENDSHIP exist between two USERs SO for that i have a table 'USER' and a relationship table 'FRIENDSHIP'

   

USER
id  firstName  LastName
1    taher     chhabrawala
2    john      Dsouza
3    rahul     singh
4    deepak    patel

Friendship
id    id
1     2
1     3
1     4
4     1
4     3

In the above table i am storing the same information twice i.e "taher is a friend of deepak and deepak is a friend of taher"

Is there any way to reduce this redundancy ?

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?