SQL - Best Practice for a Friendship Table

SQL - Best practice for a Friendship table

Making the primary key for the FRIENDSHIP table to be:

• userid
• friendid

...will ensure that you can't have duplicates in order. Meaning, it will stop you from adding duplicates of userid "123" and friendid "789". If you include the status column, that's no longer the case, because a different status value will allow for duplicates of the userid and friendid column.

Stopping Reverse Pairs

In order to stop reverse pairs -- userid "789" and friendid "123" -- you need to either include the logic to check if the pair already exists in the table in a stored procedure, function, or trigger. A CHECK constraint of userid < friendid would stop a valid attempt to add userid "789" and friendid "123" if the reverse doesn't already exist.

INSERT INTO FRIENDSHIP
SELECT @userid, @friendid, 1
  FROM FRIENDSHIP f
 WHERE NOT EXISTS(SELECT NULL
                    FROM FRIENDSHIP t
                   WHERE (t.userid = @friendid AND t.friendid = @userid)
                      OR (t.userid = @userid AND t.friendid = @friendid)

Database design: Best table structure for capturing the User/Friend relationship?

UserRelationship
====
RelatingUserID
RelatedUserID
Type[friend, block, etc]

Agree that mutuality doesn't belong as a column; breaks normalization.

Best way to store a friend's list of every user

When we say SQL Server then we are saying RDBMS.

Then there is normalization INF, 2NFand 3rdNF..., this is your question how to normalize a user and his friends data base.

My View, you need three tables

Individual (IndId (PK), Name ...)
User (UserId (PK), IndId (FK to Individual Table), login, password etc)
Friends(FID(PK), UserId (FK to User Table), IndId (FK to Individual Table))

Get all friends of a user

Select I.* from Friends F
     JOIN User U on F.UserId=U.UserId // Get all friends of all users
     JOIN Individual I on I.IndId =F.IndId // Get there names etc
   Where I.Name = 'MyFriend' // filter a friend

Designing friend relationship

Solution 1(Recommended)

- Table-persons with columns [ID, Name, LastName..]

- Table-Fiends with Columns [ID, P.ID]

Solution 2

- Table-persons with columns [ID, Name, LastName, FriendID(ID)] Self-Referencing column

MySQL friends table

Assuming all your friends are also in the user table you will need a friends table which defines a simple one-to-many relationship - linking the users table back to itself. So

User Table
UserID int identity not null
[other attribute fields]

Friends Table
UserIDLink1 int
UserIDLink2 int 
[other attribute field]

Where both UserIDLink1 and UserIDLink2 are foreign keys on the Users table.

So for instance if I have three users

1 Joe
2 Bill
3 Jane

and Joe and Jane are friends then the Friends table would contain a single row

1 3

The above implicitly assumes that if A is a friend of B then B is a friend of A - if this isn't the case you'd probably want to rename UserIDLink1 and UserIDLink2 to UserID and FriendID or similar - in which case you'd have up to double the records too.

Also for the bi-directional configuration (A is a friend of B if B is a friend of A) you should set up indexes on the Friends table for (UserIDLink1,UserIDLink2) and (UserIDLink2,UserIDLink1) to ensure access is always efficient if we were searching either for friends of joe or friends of jane (if you didn't set up the second index then the first query would be an efficient index lookup but the second would require a full table scan).

If your links were not bidirectional this wouldn't be necessary to find out who A's friends are, but you would still probably most require it as you'll likely also need to find out who B is a friend of.

Design a friends table for a social networking site

I assume that in your system friendship is symmetrical, i.e. if A is friends with B that implies that B is also friends with A. In which case, I suggest that you change your Friend table's columns to reflect the fact that the two people have equal status in the relationship e.g.

Friend

• @ User1_Id
• @ User2_Id

With that in place, you could do a query something like this

SELECT * FROM User WHERE User.UserId IN (
    (SELECT User1_Id FROM Friend WHERE User2_Id = MY_USER_ID)
    UNION
    (SELECT User2_Id FROM Friend WHERE User1_Id = MY_USER_ID)
)

This uses a sub-select rather than a join, plus a union to get a single list of friends' user ids depending on which way around the users are listed in the Friend table. Documentation of sub-query and union.

It's probably worth keeping it like this, rather than simplifying the code by having 2 rows in your Friends table for each relationship - (A, B) and (B, A). If you have N users the Friends table will have up to N*(N-1) rows if you duplicate and half that if you don't.

Query to find friends of friends SQL

It will be something like this:

select DISTINCT
    u1.user_id as user_id,u3.user_id as suggested_user_id
    from
    users as u1 
    inner join users as u2 on u1.user_id=u2.friend_id
    inner join users as u3 on u2.user_id=u3.friend_id
where u1.user_id<>u3.user_id

Database design - Friend activities

If you have database performance concern, you may redefine the friendship table as following:

friendshipid, userid, friendid, confirmed

When you query the latest 50 activities, the SQL would be:

SELECT act.*
FROM Activities AS act
    INNER JOIN
    Friendships AS fs
    ON fs.friendid = act.userid
        AND fs.user_id = 'logon_user_id'
        AND confirmed = TRUE
ORDER BY act.dated DESC
LIMIT 50;

And if there is a index on Friendships(userid) column, it would give the database the chance to optimize the query.

The friendship table redefined needs to create two tuples when a friendship occur, but it still obey the rule of business, and, has performance benefit when you need it.

---

Related Topics