Avoid data redundancy in a friends table

Question

I am trying to design a very simple table that stores the data of friends in a community.

Therefor I store the userId of 2 friends respectively.

Goal

User wants to load his/her friends list.

t_friends Option 1:

Query

SELECT * FROM t_friend WHRE user_id = 10 

10 is the current userId of the user which is looking for his friends list and he has 1 friend userId(20)

This way userId (10) finds his friend (20) BUT what if userId(20) were looking for his friends? The query is coupled with userId.

That leads me to another design that contains redundant data:

t_friends option 2:

userId (10) loads now:

SELECT * FROM t_friend WHRE user_id=10

Similiar to that the query for userId(20) would then be:

SELECT * FROM t_friend WHRE user_id=20

But what about the redundancy? That leads me then to that query using table design option 1:

SELECT * FROM t_friend WHERE user_id=10 OR friend_id=10

I have a feeling that there is a smarter way to solve that. Do You have any experiences with that structure?

Thanks

Answer 1

Add field friendship_key:

ALTER TABLE t_friend ADD friendship_key decimal(22,11);

CREATE UNIQUE INDEX friendship_key_unique ON t_friend (friendship_key);

And php part:

$friends = [$userId, $friendId];
$key = min($friends).'.'.max($friends);

$q = "SELECT * FROM t_friend WHERE friendship_key = ".$key;

insert:

$friends = [$userId, $friendId];
$key = min($friends).'.'.max($friends);

$q = "INSERT INTO t_friend (friendship_key, userId, friendId) VALUES (".implode(',', [$key, $userId, $friendId]).")";

Instead of using VARCHAR for friendship key I've used decimal to minimize data for relation key.

To keep it simple just create functions:

function insertFriendship($user1, $user2) {
    $key = min([$user1, $user2]).'.'.max([$user1, $user2]);
    $q = "INSERT INTO t_friend (friendship_key, userId, friendId) VALUES (".implode(',', [$key, $user1, $user2]).")";
    mysql_query($q);
}

function getFriendsOf($user) {
    $q = "SELECT * FROM t_friends WHERE ".$user." IN (userId, friendId)";
    return mysql_query($q);
}

function areFriends($user1, $user2) {
    $key = min([$user1, $user2]).'.'.max([$user1, $user2]);
    $q = "SELECT 1 FROM t_friends WHERE friendship_key = ".$key." LIMIT 1";
    $q = mysql_query($q);
    return (mysql_num_rows($q)>0);
}