Database design for friends relationships

Question

Hallo! Can you help me design database for friends social relations with 1000 requests per second. Bob->Mary John->Bob Serg->Francis Francis->Bob Result: Bob->Francis->Serg Thanks.

Answer 1

Hi Ilya,

It is not exactly clear what is your complete scenario.

Please take a look at the hierarchy data type in MS SQL Server:

• hierarchical-data-sql-server
• tutorial-using-the-hierarchyid-data-type

Answer 2

A "person" can have many friends, and the same "person" can be friends to multiple people.

So you need to have a "person" table and a "relationship" table which links fromPerson -> toPerson.

Answer 3

Hi IlyaMihaylov,

SQL Server offers Graph Database capabilities to model many-to-many relationships which is applied to SQL Server 2017 and later.
Graph processing with SQL Server and Azure SQL Database

Please refer one example in below link and check whether it is helpful to you:

DROP TABLE IF EXISTS Person;  
GO  
CREATE TABLE Person (  
  ID INT IDENTITY PRIMARY KEY,  
  Name VARCHAR(50) NOT NULL  
) AS NODE;  
INSERT INTO Person (Name) VALUES  
('Mary'),  
('Bob'),  
('John'),  
('Francis'),  
('Serg')  
  
DROP TABLE IF EXISTS Friendof;  
GO  
CREATE TABLE Friendof AS EDGE;  
INSERT INTO Friendof ($from_id, $to_id) VALUES (  
  (SELECT $node_id FROM Person WHERE ID = 1),   
  (SELECT $node_id FROM Person WHERE ID = 2));  
INSERT INTO Friendof ($from_id, $to_id) VALUES (  
  (SELECT $node_id FROM Person WHERE ID = 2),   
  (SELECT $node_id FROM Person WHERE ID = 3));  
INSERT INTO Friendof ($from_id, $to_id) VALUES (  
  (SELECT $node_id FROM Person WHERE ID = 5),   
  (SELECT $node_id FROM Person WHERE ID = 4));  
INSERT INTO Friendof ($from_id, $to_id) VALUES (  
  (SELECT $node_id FROM Person WHERE ID = 4),   
  (SELECT $node_id FROM Person WHERE ID = 2));  

SHORTEST_PATH (Transact-SQL)

Please find complete code in attached txt file.
20058-friend.txt

If the response helped, do "Accept Answer" and upvote it.

Best regards
Melissa