Friend List logic.

[TheRealAndyCook]

A typical friendship in a friend list occupies 2 rows in a table.

id | user | friend
----------------
1  | 1     | 2
2  | 2     | 1

Entry number 1, which tells us user 1 is friends with user 2
Entry number 2, which tells us user 2 is friends with user 1

A slightly, large, query can then tell is if they are mutual friends. And, it takes up 2 units of space, per friendship.



Example 2)

id | user | friend | is_friend
------------------------------
1  | 1     | 2       | 0

This table is a little more complex, but easy to understand. Since we cant name columns the same thing we've chosen to have the user who attempted to make the friendship first "USER" and the person they want to be friends with is "FRIEND".

In this case, user 1 is trying to become friends with user 2.

When user 2 accepts the friendship, all that happens is the column is_friend gets changed to 1.


To figure out if two users are friends, its a very simple logic query.

SELECT is_friend FROM friendship
where (user = 1 or friend = 1) and (user = 2 or friend = 2)

You can then return the status of the friendship. And, even tho were only using ONE row we can still clearly identify who added whom first.

(note: only downside to this method is you can't allow users to add them-self as a friend.)

 

[silent1mezzo]

Why not just use the ID and remove the USER column in the initial table? You're just duplicating data.

EDIT: Nevermind I read the whole statement...

 

[TheRealAndyCook]

I always have an id table.

Tho your right to optimize for space, which this post was really about, i could remove the ID column.

I also didn't mention its a little trickier to put a friendship back into "pending" if the user who CREATED the initial friendship is the one that deleted the friendship.

 

[silent1mezzo]

Personally I wouldn't store a row of a user to another user if they're not friends.

With just two users you have 2 rows, 4 users you'd have 12 rows, 10 users you'd have 90 rows, 100 users you'd have 9900 rows

Instead I'd just store a row when you actually have a friendship

ie.

User 1 is friends with User 2 but User 2 isn't friends with User 1

id | user | friend
---------------------
1  | 1     | 2

User 1 is friends with User 2 and User 3, User 3 is friends with User 2, User 2 is friends with nobody

id | user | friend
---------------------
1  | 1     | 2 
2  | 1     | 3
3  | 3     | 2

This way you're saving on the space of the is_friend and you're only putting in the rows of people who are friends.

It's also easy to check if a friendship exists

select id from friend_table where user=user_id and friend=friend_id
If nothing is returned then they aren't friends.

 

[silent1mezzo]

For pending requests I'd have another table, same principle as the one above.

 

[TheRealAndyCook]

I don't create a new row every time a user is created.
The friends_list table contains Zero entries until a user creates a friend request.

 

[silent1mezzo]

I don't create a new row every time a user is created.
The friends_list table contains Zero entries until a user creates a friend request.

Ah ok, I understand a little better.

The other thing you could do is set it up like

id | user | friends | pending
---------------------------------
1  | 1     | 2     | 3  
2  | 2     |        |
3  | 3     | 2     |

Then you'd only have a row per user and in whatever language you have you'd just have to do:

select friends from friend_table where user = user_id
if friend_id in friends
    // they're friends
else
   // not friends

My co-worker and I were arguing whether this was quicker...because you'd only ever have x number of rows and you could throw an index on it. It'd come down to your programming language of choices speed.

 

[TheRealAndyCook]

you mean have the data in the fields CSV'd?

id | user | friends      | pending
---------------------------------
1  | 1     | 2,4,5,6     | 3,7,8,9

?

I think it'd be fairly time consuming to pull the data out.

 

[silent1mezzo]

you mean have the data in the fields CSV'd?

id | user | friends      | pending
---------------------------------
1  | 1     | 2,4,5,6     | 3,7,8,9

?

I think it'd be fairly time consuming to pull the data out.

I was arguing that it would be. I'll be running tests on the performance of that. Technically the initial query would be quicker, it comes down to whether the IN clause (in MySQL, or using IN with your given language of choice) would be quicker.

 

[TheRealAndyCook]

Yeah, mysql. using MyISAM.