I'm working on a rights verification system. In order to verify that the user has access to the documents, I make a request to the database There are 2 cases when you can get access:
select count(*) > 0 from Document
left join Chat
left join ChatUser
left join User
left join Resource
...
where ...
select count(*) > 0 from Document
left join User
left join Resource
left join ...
...
where ...
I see 2 solutions
pros of such a solution -> 1 request to the database
cons of such a solution -> every time in 70% of cases I make an extra 5 joins, which can hit the performance of the query in the database
First make a request whether the document is in the chat.
If false -> then make 1 more request
Tell me what algorithm is used in such cases?
If you have one-to-one relation then join queries should be used.
Join queries with one-to-many relation will lead to more memory usage with redundant data.
If you don't have memory usage issue then also you should use join queries because in most of the cases, join queries are faster than multiple queries.