Friend Request System Database Design
In today's article, we delve into the intricacies of designing a Friend Request System database, complete with sample demo data. This system is a fundamental feature for social networking platforms, enabling users to connect with each other through friend requests. We'll cover the essential tables, such as the User Table, FriendList Table, and Status Table, along with relevant stored procedures.
Users Table
The User Table stores crucial information about users at the time of registration, including a unique UserID (uid). Below is the SQL script for creating the User 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
FriendList Table
The FriendList Table maintains information about friend relationships. It stores details such as whether a friend request has been sent, accepted, or is pending. The Status Table is referenced in this table to define the relationship status.
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
Status Table:
The Status Table contains only two values, representing the status of friend requests: 'Pending' and 'Confirm' (or 'Accept').
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
This stored procedure (spInsertUserRecord) is designed to insert user data. It checks for existing usernames to avoid duplication.
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 }
Stored Procedure: Sending Friend Request
The spSendingFriendRequest procedure sends a friend request, checking if a request has already been sent or received.
- This stored procedure is used to send a new request. At the time of execution, it checks whether you have already sent a friend request to this user or received a friend request from them.
- If you have already sent a friend request to user2, the procedure will generate an error message based on the status of your request, indicating whether your status is pending or you both are already friends.
CREATE PROCEDURE 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 a 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
Stored Procedure: Accept Friend Request
The spAcceptFriendRequest procedure changes the status of a friend request from 'Pending' to 'Accept' or '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
Stored Procedure: Delete Friend Request
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
0 Comments