Which database query logic to choose (task)?

Question

MySQL

The problem is this: there is a kind of social network with users who create albums, add photos to the albums. The user can set the access level for his album for other users: 1.the album is visible only to me (the owner), 2.visible only to friends, 3.visible only to subscribers (+friends). The structure of the subscriber table will presumably consist of two columns: from (subscriber id) and to (subscriber id). The presence in the table of two reverse records (subscribed to each other) means that the users are friends.
The question is, according to what logic, to make a request to the database so that it gives out a list of albums of all users to which the client has access rights.
I see 2 options:
1:

• №1 Request a list of users on which the client is subscribed
  
• #2 Request a list of users who are friends with the client (use list #1)
  
• #3 Request a list of user albums from list #1
  
• Make a selection, give the result using 3 lists
  

IMHO a complex algorithm that will heavily load the system
2:
Form an additional two-column table in the database that stores the user id and the album id that he can view.
Changes are made to this table (a pair is added / removed) at the moment when any of the users:

• creates an album - adds the album id to all users who currently have the right to view it
  
• deletes the album - overwriting the album id for everyone where it was
  
• changes the access level of an existing album - 2 procedures above: delete + create
  
• user subscribes - get albums, add to the list
  
• user unsubscribes - overwrite the user's album id
  

Intuitively, it seems to me that this is a more correct option. However, there is a risk that some keys will be lost in the database due to crashes, and a table rebuild will be required.
What do you think, maybe there is another option?

Answer 1

Of course, duplication of information in the database is not a normal option. But a recursive pass for SQL systems is also an abnormal option. There is even an article about this on Wikipedia: https://en.wikipedia.org/wiki/Hierarchical_and_rec...
I solve this problem in one of two ways, just like you.
In the first variant, this is a join of the same table by the secondary key. Thus, each record has its own ancestor in the resulting table, which is convenient for subsequent non-SQL processing.
In the second option, I break the normal form by creating a link table, the update of which I hang up as a trigger on the tree-like table that I process. So with a little redundancy, you can get a big performance boost.
Other options:
I also implemented recurrent traversal through stored functions - this is a slow option.
When developing a social network, I would use the second option. I recommend implementing both and conducting load testing with large volumes. And then choose based on data analysis after testing.

Answer 2

What is the problem?
1) get the client status. The list of users does not need to be received, you just need to check for the presence of one client in subscriptions and friends.
2) get a list of albums.