
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 UserIDFriendList 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- Pending means (Friend Request Status)
- 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 procedureexec spDeleteFriendRequest 1,3 (or) exec spDeleteFriendRequest 3,1
0 Comments