CODEWITHSUNDEEP
databaserestpermissionsbackend
Giancarlo Buenaflor
Giancarlo Buenaflor

Reputation: 11

Permissions on nested REST API resources that I decided to flatten

I'm currently trying to model a workout plan in the database and it consists of

My initial idea was to do something like this:

workout_plans/:id/workout_days/:id/exercise_routines/:id/sets/:id

I noticed that it has a really heavy nested structure so I decided to flatten it:

I've got a simple permission junction table that states which user, which permission (read, create, delete) has on which workout plan. I've decided that I currently don't need more granular permission level on the other entities and that having a certain permission on a workout plan should automatically be transitive to its nested children entities.

So if I have a read permission on a workout plan, I should be able to read its workout days and so on.

Now my problem with the "flat" rest api approach is that I don't have the convenience of knowing immediately which workout plan a certain exercise_routine or set is part of. Thus I need to "join" my way up to the parent until I know which workout plan an entity is in relation with and then I can finally check the permission.

So my question is if there is a better way of doing this? My first idea of solving this is by having a reference id to the parent workout_plan for every entity down the line but this approach seems kind of fishy to me. Or maybe I should just stick with the nested structure? Thanks for the help in advance!

Upvotes: 1

Views: 307

Answers (1)

Joseph Schorr
Joseph Schorr

Reputation: 36

You've hit the main issue with handling "inherited" permissions in a traditional database-backed access control system. In previous experience, I've found that joins are useful (and reasonably performant) if the nesting is only a single level deep and the table sizes are kept to a reasonable size: once you hit multiple levels, or extremely large tables, the only real "solution" within the database I've found is to denormalize the data as you suggested by including the other ID(s), but even this can become an issue as tables grow larger (and a massive pain if you need to add another nesting level at some point).

The unease you're feeling cuts to an issue with using databases for access control: Permissions are often inherited, inferred, or even sometimes recursive in nature, but traditional relational databases were not designed to allow for easy or performant resolution of these structures because permissions relationships are actually graphs. In your case, you have a tree of permissions relationships, from workout plan, to day, to routine and so forth. There are services (this is my own) dedicated to solving deeply nested relationships, which might be helpful once you've pushed denormalization to its limits.

Upvotes: 2

Related Questions