MySQL structure :: Friendships

Question

Many people say that I should create a friendships table(userid , friendid).

My question is:

Won't there be a double record for every friendship ? like:
12 is friend with 5
+
5 is friend with 12

Is there a way to prevent this ?

| OR |

Should I just ignore that ?

Answer 1

There are at least two approaches you could use:

1. Ensure that the userid is always less than the friendid.
2. Store each relationship in both directions (i.e. store both a,b and b,a).

The second approach costs twice as much storage and is redundant but it does mean that some queries can be much simpler and will perform better. For example, consider fetching all the common friends of user 'a' and 'b' with each of the approaches:

Approach 1:

SELECT friendid
FROM
(
    SELECT friendid FROM friendships WHERE userid = 'a'
    UNION
    SELECT userid FROM friendships WHERE friendid = 'a'
) T1
JOIN
(
    SELECT friendid FROM friendships WHERE userid = 'b'
    UNION
    SELECT userid FROM friendships WHERE friendid = 'b'
) T2
ON T1.friendid = T2.friendid

Approach 2:

SELECT T1.friendid
FROM friendships T1 
JOIN friendships T2
ON T2.userid = 'b' AND T1.friendid = T2.friendid
WHERE T1.userid = 'a'

Answer 2

I would ask: Is it possible for someone to be friends with themselves? What does 'friendship' mean - is it bidirectional or unidirectional. If bidirectional then one record will do, right? If unidirectional, then two records are needed. If someone cannot be friends with themselves, then you implement a business rule in the data entryu page (liek in a web page this would be good for javascript) where friend1 id <> friend2 id Not sure I agree with 'less than' - they just can't be equal