Would this be the ideal/most optimal way to implement a one-way 'following' relationship?
I have 2 tables as follows
Table1: User_Info_Table
UserID Name JoinDate
Table2: User_Follower_Table
FollowerID FollowingID Status
FollowerID and FollowingID are FK constraints to the User_Info_Table UserID column
The following logic takes place:
•User A follows User B
WHERE NOT EXISTS (SELECT 1 FROM User_Follower_Table WHERE FollowerID = A and FollowingID = B and Status = 0)
BEGIN
INSERT INTO User_Follower_Table (FollowerID, FollowingID, Status)
VALUES (A, B, 1)
END
ELSE
UPDATE User_Follower_Table
SET Status = 1
WHERE FollowerID = A and FollowingID = B and Status = 0
•User A unfollows User B
UPDATE User_Follower_Table
SET Status = 0
WHERE FollowerID = A and FollowingID = B and Status = 1
•User B decides to follow User A back
WHERE NOT EXISTS (SELECT 1 FROM User_Follower_Table WHERE FollowerID = B and FollowingID = A and Status = 0)
BEGIN
INSERT INTO User_Follower_Table (FollowerID, FollowingID, Status)
VALUES (B, A, 1)
END
ELSE
UPDATE User_Follower_Table
SET Status = 1
WHERE FollowerID = B and FollowingID = A and Status = 0
•User B unfollows User A
UPDATE User_Follower_Table
SET Status = 0
WHERE FollowerID = B and FollowingID = A and Status = 1