User relationship table. How right?

Question

MySQL

There is a table of users, all users have a user_id (for example: 111, 222, 333, 444). It is required to add a table with friends:
user_id(111) is friends with user_id(333) and user_id(444),
user_id(222) is friends with user_id(444)
From google came to this table:
user_id | friend_id
222 | 444
444 | 222
_ for each pair of friends, two entries are created.
How to do better than given in the example?

Answer 1

Correct relationship table:
Just in case, I will note that there should not be any id (records themselves)! all selections for the keys below
user_id|friend_id|relation_id|date(timestamp)
222|444|1|ts
444|222|2 |ts
primary key composite (user_id, relation_id)
additional key (friend_id, relation_id) on MyISAM handles (we check with PHP tools). On innodb we make a transaction using the database. enter the line with the confirmation curent_user > friend_id > relid - 1 and update the main request to one. I repeat, if one of the commands does not work, everything must be rolled back. if refusal, then delete the main application from the database.
if a request is made, 1 record is created user_id>friend_id>0
check for incoming requests friend_id(current_user_id) | relation_id = 0
confirmation: (there must be a transaction!) that is, if one of the requests fails, everything must be returned as it was.
about additional relationships:
you can only send an application to a user who has a mutual value of 1 and check for the application
itself friend_id(current_user_id) | relation_id >(more) 1.
The rest of the algorithm is the same. at the same time, 1 user can have only one record with a ratio greater than 1 (preferably, this is an indicator of a personal relationship, it’s better to have a separate field for grouping friends)
Description from a real database of 30 million users.

Answer 2

Not exactly 2 entries are created for every pair of friends.
One entry is created per friend offer. It may not be mutual (unconfirmed proposals do not mean friendship).
I think the table layout is correct.

Answer 3

And it is sane not to do otherwise.

Answer 4

It is better to implement the storage of friends not through key relationships in another table - but through the storage of LIST (any other set) in XML | JSON format in the entry itself.
example
userid name friendlist requestfriendlist subscrybefriendlist
1111 Ivan {2222, 3333} {4444} {}
2222 Sergey {1111, 3333} {} {4444}
3333 Olga {1111, 2222} {4444} {}
4444 Max {} {2222} { 3333, 1111}
in friendlist - store confirmed friends.
in requestfriendlist - store friend requests.
in subscrybefriendlist - subscribers, etc.
in the code to make links based on lists.
It is convenient to serialize data and it is convenient to operate with them. Move from one list to another.