Redundancy vs chained join I'm encountering issues checking ownership and accessibility of entities in my application.

Here is a simplified summary of my problem. Consider the following diagram (I don't have a precise DDL, this is a simplified example to illustrate my issue: access to an entity efficiently in my app depending on the properties of its parents [of parents ...]):




A writer can write many books, add chapters to it, then attach notes
to chapters (a book may have co-authors, but not at the the moment).
He is the owner of the book thus no other writer can update it, same
for its belonging chapters and notes.
He can archive his books, this imply can he no more edit books chapter
and notes.


I have an API that performs CRUD operations on books, chapters, and notes.

Foreach each operation I have to verify if the current user is the owner of that ressource and if the book it belongs to is not archived.

I'm using an ORM and currently on a "edit notes" api call, I populate objects by making a select that join the chapter then the book, then the owner.
Then i can perform the ownership check and the archiving check.


Is it wrong doing big queries with chaining join to prevent
subsequent database calls ?  I feel it makes these calls more and
more complex and heavy as the application grows.
Should I make more simplest request for each ressource?
Should I use another database design to make it easier/faster
checking ownership and parents (of parents ...) properties (e.g redundancy, something else...)?
No, I think you are doing the Right ThingTM.

Performing a bigger query to get all the data you need in one round trip almost always beats fetching the data points one by one (&ldquo;nested join implemented in the application&rdquo;).

Your normalized database design is perfect for a transactional application that performs data modifications and small queries. If that is your workload, keep it like that. Such a layout avoids storing information redundantly, which is good for consistency and saves space.

If you have a database for reporting or analytical processing, the many large joins required by the typical queries will often be too inefficient In that case judicious de-normalization (using fewer tables with potentially redundant data) is often more efficient.