How to design a relationship database table to store friendship relationship?

I want to design a table to store friendship relationship in my web project It should satisfy at least the following 4 conditions:

who send the add-friend request e.g.(if A TO B then this column will be A) who receive the add-friend request e.g.(if A TO B then this column will be B) current status e.g.(0 denotes rejected whereas 1 denotes accepted or 2 denotes unprocessed our friend relationship is bilateral

If any of you are experienced with this , any suggestion is welcomed my current design (I think bad right now) is like this these are the columns

frienshipId fromUserId toUserId status requestTime 

Can I suggest using the code view (highlight your text and press ctrl-k or put four spaces before each line) and highlighting your DDL so that we can see how your data model is designed (or how you want to design it)

4 Answers 4

I'd create a table a lot like the one that you have. I'm using SQL Server data types and syntax, you may need to tweak depending on your platform.

CREATE TABLE FriendStatus (FriendStatusId BIGINT PRIMARY KEY IDENTITY(1,1), FromUserId BIGINT, ToUserId BIGINT, StatusId TINYINT, SentTime DATETIME2, ResponseTime DATETIME2); 

Indexing of the table will be critical as the table grows to the tens and hundreds of millions.

Fixed the duplicate name issue. The Clustered index should be on the FriendStatusId. The Primary key could be FriendStatusId or a combination of FromUserId and ToUserId.

Although if you allow multiple friend requests you'll want the PK on FromUserID, ToUserId, SentTime or the Clustered Index.

CREATE TABLE users ( users_id serial PRIMARY KEY, name text UNIQUE NOT NULL ); CREATE TABLE friends ( friends_id serial PRIMARY KEY, timestamp TIMESTAMPTZ default now(), user_a integer NOT NULL REFERENCES users, user_b integer NOT NULL REFERENCES users, status integer NOT NULL default 2 ) 

For listing friendships, a view:

CREATE VIEW friendships AS SELECT DISTINCT user_a, user_b FROM friends WHERE status = 1 UNION SELECT DISTINCT user_b, user_a FROM friends WHERE status = 1; 

You can use it like so:

INSERT INTO users ( name ) VALUES ( 'foo' ); INSERT INTO users ( name ) VALUES ( 'bar' ); INSERT INTO users ( name ) VALUES ( 'baz' ); SELECT * FROM users; users_id | name ----------+------ 1 | foo 2 | bar 3 | baz INSERT INTO FRIENDS ( user_a, user_b, status ) VALUES ( 1, 2, 1 ); INSERT INTO FRIENDS ( user_a, user_b, status ) VALUES ( 2, 1, 1 ); INSERT INTO FRIENDS ( user_a, user_b, status ) VALUES ( 1, 3, 1 ); SELECT * FROM friendships ORDER BY user_a, user_b; user_a | user_b --------+-------- 1 | 2 1 | 3 2 | 1 3 | 1 SELECT a.name, b.name FROM friendships JOIN users a ON a.users_id = user_a JOIN users b ON b.users_id = user_b ORDER BY a.name, b.name; name | name ------+------ bar | foo baz | foo foo | bar foo | baz 

What makes you think your current design is bad? Here is a create table for Oracle:

CREATE TABLE IVR.FRIEND ( FRIENDID NUMBER(7) NOT NULL , FROMUSERID NUMBER(7) NOT NULL , TOUSERID NUMBER(7) NOT NULL , STATUSID NUMBER(2) NOT NULL , REQUESTED DATE NOT NULL , CONSTRAINT FRIEND_PK PRIMARY KEY (FRIENDID) ENABLE ); CREATE SEQUENCE FRIENDIDSEQ; 

If the database is Oracle you might want to consider an indexed virtual column that will limit the data to entries needed for particular queries. For example, you could have a virtual column called AcceptedFromUserId that uses the function DECODE(StatusId,1,FromUserId,NULL). The index would only contain AcceptedUserIds and would therefore be smaller than an index for all UserIds. If you regularly clean out rejected requests an indexed virtual column on PendingToUserId might be more useful.

An alternative if you had partitioning would be to partition the table on the StatusId.

If you don't need multiple friend requests between the same users at the same time you could abandon the FriendId using FromUserId, ToUserId, and StatusId as your primary key. In this case you should also consider making the table an Index Organized Table.