Frindship association

Databases
1

Greetings,

I’m going to be stating redevelopment of a particular website which has a social aspect where users can make mutual friendship associations. I’m wonder what a recommended pattern would be for recording these friendships?

Currently we have a ‘friendship’ table, which stores 2 records for every friend connection.

If I request friendship from you, it creates a friend record with these values

me=my_id, you=your_id, approved=0

If you accept my friendship, a new record is created for you with these values

me=your_id, you=my_id, approved=1

and my record is updated to be approved.

Is there a better method for recording this info? We haven’t had any problems with it, but I wonder how gracefully it would extent to say a million records? We could easily have that number of records soon as each person has a multitude of friend records.

When I designed the system I didn’t give it much thought, just wrote it out to work like that. It works simple enough when I need to access my friends (SELECT * FROM friends WHERE me=my_id AND approved=1).

I’m just curious what pattern the big guys use.

Thanks for any help.

Best regards,
Nathan

2

I just meant the former query has twice as many records. I’m doing some tests now.

3

Thanks for pointing that out. i wonder if having to do a table scan on one column would indeed be slower than the 50% overhead of the current pattern?

I don’t mind using a union either. I really only have one query that ever pulls in friends in my model.

How does mysql manage the index data? If I create a dummy table and populate it with a few million records and performance test the different methods would it be accurate, or does it take mysql a while to optimize the index table?

4

50% overhead of the current pattern?

go ahead and populate a table with test data and run some queries

don’t forget to do an EXPLAIN on the queries to see which indexes are being used

5

the problem with having only one row is the inefficiency of the query

WHERE friend1 = $my_id OR friend2 = $my_id

the problem here is that the database can use only one column index to optimize this retrieval, so there’s going to be a table scan even if both columns are indexed

the way around it is to write the query as a UNION

SELECT friend2 AS friend
  FROM ...
 WHERE friend1 = $my_id 
...
UNION
SELECT friend1 AS friend
  FROM ...
 WHERE friend2 = $my_id

but this is a lot clumsier, isn’t it

the other advantage of two rows is that you can easily indicate which person did the asking and which person accepted

6

yes, that one little record should be all they need to have in common. But then objectivity/subjectivity becomes complicated in the logic.

I would have to remove the me/you naming convention and change it to friend1/friend2 or something like that.

If I’m viewing a person’s profile, the code to get his friend’s ids into an array would be:

SELECT * from friends WHERE friend1 = my_id OR friend2 = my_id;

And then adding to the array

if(friend1 != my_id) { add friend1 val to array }
else { add friend2 val to array }

I rather like the subjectivity using the other method though.

Anyone have any better ideas for managing this? :slight_smile:

7

That seems fine to me, though if the friendships have to be mutual you shouldn’t need two records.

Friendship:
id1=1, id2=2, approved=1
id1=2, id2=1, approved=1