1

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 ...]):

enter image description here

  1. 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).
  2. He is the owner of the book thus no other writer can update it, same for its belonging chapters and notes.
  3. 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...)?
1

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 (“nested join implemented in the application”).

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.