SQL friend function

Question

What would be the best way to structure a friend "function".
This is what im thinking: Lets say I have a usertable with the following columns: user_id, username, password... and a table by the name of friends with columns: friend1, friend2, accepted.

If user2 adds user1 I want user2 to be shown on user1's profile and user1 on user2's...:O

this seems a little messy...is there a smarter way?

Answer 1

CREATE TABLE (friend_id_a, friend_id_b, accepted)

Define a constraint: friend_id_a < friend_id_b.

Populate the table with your entities as follows:

INSERT
INTO friend_friend(friend_id_a, friend_id_b)
VALUES (LEAST(@id1, @id2), GREATEST(@id1, @id2))

To select all friends of a person:

SELECT friend_id_b
FROM friend_friend
WHERE friend_id_a = @id
 AND accepted
UNION ALL
SELECT friend_id_a
FROM friend_friend
WHERE friend_id_b = @id
 AND accepted

UNION ALL here lets you use indexes and avoid extra sorting for uniqueness.

All above is valid for "friendness" as a symmetric and anti-reflexive relationship. That means that:

• If a is friend to b, then b is friend to a

• a is never a friend to a

Answer 2

I think your design is good. The second table friends (primary key friend1, friend2), where a row f1, f2 is present if "friendship between f1 and f2 is about to be established", and accepted is True if it has actually been established, is the right way to represent a many-to-many relation.

(The only issue with the naive approach is when you must check that f1 is a friend of f2's: It may mean that the row f1, f2 is present, or that the row f2, f1 is. +1 to Quassnoi for overcoming this difficulty.)

Answer 3

I can't tell if you mean a massive table with a column for each friend, or two tables like below, but the latter is best.

[dbo].[Users]{
    User_ID int,
    UserName VarChar,
    Password ...
}

[dbo].[Friends]{
    User1_ID int,
    User2_ID int,
    Accepted bit
}