What is the best way to organize the database structure to organize the friends module on the site?

Question

MySQL

There is a website with MySQL base. I am writing a module (section) "friends". Functions as always: adding, deleting, confirming, rejecting an application. What is the best way to organize the structure in order to reduce the load?

Answer 1

your id, friend_id friend
1 2
2 1
if there is a couple, then you are friends, if not, that is, only 1 2 or 2 1, then you are not waiting for confirmation.
Here is a query that returns a list of friends:
SELECT login, friends.id AS check_id, friends.friend_id AS check_friend_id,
(SELECT id FROM friends WHERE id=check_friend_id AND friend_id=check_id)AS checkout
FROM friends LEFT JOIN users ON (users.id= friends.friend_id)
WHERE friends.id=?i
HAVING checkout IS NOT NULL ORDER BY login
AND two indexes on id, friend_id and vice versa friend_id,
id and if so, update friendship=1. I hope this helps you.

Answer 2

many - to - many relationship
I advise you to get acquainted with such things and types of table connectivity
and also normalization, but if you suddenly do not know this.

Answer 3

Make a uid2uid friendly link label and queue (another label or 3rd party module) to make changes to the link label. The main thing is that the applications themselves are inserted into the queue of applications “with a whistle” (INSERT DELAYED) ...

Answer 4

Another option is a table with fields id1, id2, status. id1 - the one who offers friendship, id2 - who is offered, status - the state of the connection. Partially loses to the options listed above, but this one also has a life. For example, if you need more types of connections - besides a friend, for example, parents, etc.

Answer 5

Add a "Friends" table with foreign keys and statuses.
The optimal solution would be to use a non-symmetric data structure, like this:
uid - the user who owns the record
friend - friend user
status - one or more status fields. If you use several fields with boolean flags, the solution will turn out to be less resource-intensive, that is, more productive.
Viewing the list of user's friends - selection from the list by the uid key.
All other functions are reduced to setting/changing the statuses of records in this table.

Answer 6

For some reason, I thought that I could hang on Redis in the set. Quickly count how many friends will allow. True, I don’t know how to make selections by friends from the database)))

Answer 7

Not an expert in this matter, but what is better to do a subquery as suggested
SELECT login, friends.id AS check_id, friends.friend_id AS check_friend_id,
(SELECT id FROM friends WHERE id=check_friend_id AND friend_id=check_id)AS checkout
FROM friends LEFT JOIN users ON (users.id=friends.friend_id)
WHERE friends.id=?i
HAVING checkout IS NOT NULL ORDER BY login
OR do:
SELECT id,friend_id FROM subscription s1
INNER JOIN subscription s2 WHERE s1.uid = s2.fuid and s1. fuid=s2.uid;
what, in principle, will the intersection on itself give?!
It's a performance issue.