What are good practices for organizing “friends” and posts on the site?

Question

Design patterns

It is necessary to implement a system of "friends" on the site. At first I thought to make a friends field for each user in the database with an array of id's of their "friends". But then the task arose to make a list of the most active friends (by analogy with VK, those people with whom you communicate most often will be displayed higher). And the question arose of how to check this activity. And even if we have decided on this. For example, each time we will check the number of messages with each of the friends in the last week. And sort friends on this basis. Will this put too much load on the database. What are the practices of organizing such structures?

Answer 1

many-to-many relationship with additional attributes
, that is, a separate table
friends:
id_user, id_friend, activity
activity calculate and update based on your metrics of the number of messages, for example, and sort by this field when selecting. You can update it infrequently, for example, once a day.
the load at the same time is not very large, because you do not make any aggregating queries or complex joins.
But keep in mind that friendship is one-sided. that is, it is considered that people are subscribed to another (as done in VK), if the friendship is mutual, then there will be two entries, the second friend of the first and the first friend of the second, and their activity may be different.