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...)?