[Solved] Avoid data redundancy in a friends table

by


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);
}

1

solved Avoid data redundancy in a friends table