Answer the question
In order to leave comments, you need to log in
Which database query logic to choose (task)?
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:
Answer the question
In order to leave comments, you need to log in
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.
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.
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question