mysqldatabasedatabase-design

critique of this DB design? retrieve all 2nd degree friends (friends of friends)?

I'm designing the database for a simple social site using MySQL. I only have a users table. I want users to be able to add friends and be able to list their friends. I'm thinking of creating a table friends (user_id, friend_id).

What is a critique of this DB design?

When someone adds a friend, it will create an entry.

How do I retrieve all 2nd degree friends (friends of friends)?

Solution

  • You can retrieve 2nd degree friends by joining the friends table to itself

    select t2.friend_id
from friends t1 
     inner join friends t2 on t1.friend_id = t2.user_id
where t1.user_id = ...

    One thing to consider is commutativity - if A is a friend of B, is B a friend of A ?