Facebook Style Friend Request Sent System Database Design

Posted By : Sri Kanth

Wednesday, 6 April 2016

In this articles i'm going to explain how to create Friend Request System Database Design with sample demo data, for this design we need some table like user table, friendlist table, status table.

• User Table :

  this table is used to store user information at the time of registration along with UserID

• FriendList Table :

  this table is used to store or maintain friend's relation information i.e
  if we sent Friend Request that request will stored in this table, Update Confirm Friend Request or Accept Friend Request status also updated in sames table.

• Status Table :

  this status table contain only 2 values i.e
  1. Pending means (Friend Request Status)
  2. Confirm or Accept (Friend Request Status

Create Users Table

CREATE TABLE [dbo].[users](
 [uid] [int] IDENTITY(1,1) NOT NULL,
 [username] [varchar](50) NULL,
 [password] [varchar](50) NULL,
 [name] [varchar](50) NULL,
 [image] [image] NULL,
 CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED 
(
 [uid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Create Friend List Table

CREATE TABLE [dbo].[friendlist](
 [uid1] [int] NOT NULL,
 [uid2] [int] NOT NULL,
 [statuID] [char](4) NOT NULL,
 CONSTRAINT [pk_uf_uid1_uid2] PRIMARY KEY CLUSTERED 
(
 [uid1] ASC,
 [uid2] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[friendlist]  WITH CHECK ADD  CONSTRAINT [FK_friendlist_friendlist] FOREIGN KEY([statuID])
REFERENCES [dbo].[status] ([ID])
GO

ALTER TABLE [dbo].[friendlist] CHECK CONSTRAINT [FK_friendlist_friendlist]
GO

Create Status Table :

CREATE TABLE [dbo].[status](
 [ID] [char](4) NOT NULL,
 [SName] [varchar](15) NULL,
 CONSTRAINT [PK_status] PRIMARY KEY CLUSTERED 
(
 [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Stored Procedure

Insert User Data Stored Procedure :

This Stored Procedure is used to insert users data if user data is already exist then this stored procedure will display a message i.e Username Already Exist

create procedure spInsertUserRecord
(@username varchar(50), @password varchar(50), @name varchar(50),@picture image)
as begin 
 if exists(Select * from users where username=@username)
   print 'Username Already Exist' 
  else
   Insert into users values(@username, @password, @name,@picture)
   
end

For Example :-

To insert data through Stored Procedure

  exec spInsertUserRecord 'kiran', '12345', 'Kiran Reddy',''

Again Insert Same Query then you get { Username Already Exist Message }

Sending Friend Request Stored Procedure :

• This stored procedure is used to send new request at the time only it will check whether you already sent friend request to this users or did you received friend request from this user or not.
• if you already sent friend request to user2 then it will give error message based on your status of request i.e {Your Status is Pending, You Both are Friends}

Create procedure [dbo].[spSendingFriendRequest]
(@user1 int, @user2 int)
as begin
Declare @checkuser1 varchar(20)
Declare @checkuser2 varchar(20)
set @checkuser1 = (Select statuID from friendlist where uid1=@user1 and uid2=@user2)
set @checkuser2 = (Select statuID from friendlist where uid1=@user2 and uid2=@user1)
if(@user1 = @user2)
  print 'You Cant add Yourself as Friend'
  else
if(@checkuser1 is null)
    if(@checkuser2 is null)
      Insert into friendlist values(@user1,@user2,'sts1')
     else     
      if(@checkuser2 = 'sts1')
        print ' Your Status is Pending'
      if(@checkuser2 = 'sts3')
        print 'You Both are Friends'
  else
  if(@checkuser1 = 'sts1')
    print ' Your Status is Pending'
    if(@checkuser1 = 'sts3')
    print 'You Both are Friends'
end

Accept Friend Request Stored Procedure :

this stored procedure is used to change the status of the friend request from Pending - to - Accept or Pending - to - Confirm

alter procedure spAcceptFriendRequest
(@uid1 int, @uid2 int)
as begin
Declare @checkuser1 char(4)
Declare @checkuser2 char(4)

set @checkuser1=(Select statuID from friendlist where uid1=@uid1 and uid2=@uid2)
set @checkuser2=(Select statuID from friendlist where uid1=@uid2 and uid2=@uid1)
 
if(@checkuser1 is null)
 begin
  if(@checkuser2 is not null)
   begin
    Update friendlist set statuID='sts3' where uid1=@uid2 and uid2=@uid1
   end
 end
else
 begin
  Update friendlist set statuID='sts3' where uid1=@uid1 and uid2=@uid2
 end
end

For Example :-

to accept friend request by using stored procedure

 exec spAcceptFriendRequest 1,3 (or) exec spAcceptFriendRequest 3,1

Delete Friend Request Stored Procedure :

this stored procedure is used to delete the friend record from the friend list

Create procedure spDeleteFriendRequest
(@uid1 int, @uid2 int)
as begin

Declare @checkuser1 char(4)
Declare @checkuser2 char(4)

set @checkuser1 =(Select statuID from friendlist where uid1=@uid1 and uid2=@uid2)
set @checkuser2 =(Select statuID from friendlist where uid1=@uid2 and uid2=@uid1)

if(@checkuser1 is not null)
  begin
   Delete friendlist where uid1=@uid1 and uid2=@uid2
  end
else
  begin
   if(@checkuser2 is not null)
     begin
      Delete friendlist where uid1=@uid2 and uid2=@uid1
     end 
   else
     begin
       print 'There is no users with this UserIDS'
     end             
  end
end

For Example :-

to delete friend request by using stored procedure

 exec spDeleteFriendRequest 1,3 (or) exec spDeleteFriendRequest 3,1

Tag : sql server