Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

frienshipId fromUserId toUserId status requestTime these are the columns

share|improve this question
    
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

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.

share|improve this answer
    
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... –  Hi福气鱼 Mar 15 '11 at 3:31

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
share|improve this answer

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.

share|improve this answer

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
share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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