9

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
4
  • 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)
    – jcolebrand
    Oct 22 '11 at 23:01
  • Also check the discussion here: stackoverflow.com/questions/10807900/…
    – Adam
    Nov 17 '15 at 18:59
  • Use a graph database. These are designed for just this circumstances. Nov 27 '15 at 3:43
  • quora.com/… Oct 5 '17 at 0:04
9

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.

4
  • What about a clustered index / primary key on StatusId ?
    – bernd_k
    Mar 11 '11 at 8:25
  • 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.
    – mrdenny
    Mar 11 '11 at 8:51
  • Although if you allow multiple friend requests you'll want the PK on FromUserID, ToUserId, SentTime or the Clustered Index.
    – mrdenny
    Mar 11 '11 at 8:57
  • your naming strategy is better... Mar 15 '11 at 3:31
8

On PostgreSQL:

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
3

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.

-2

Schema:

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
)

From PHP:

select * 
from friends 
where user_a=$myid or user_b=$myid
1
  • And what to do with mutuality? (And the friends table definition is suspect for syntax error.)
    – dezso
    Nov 3 '12 at 7:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.