Im trying to look for and find standard patterns (if any) for structuring user accounts, organisations, sub organisations and products.
It would need to handle: 1. Role based permissions 2. Roll up billing
My initial thoughts are: 1. A user account is unique but can be a member of 1 or more organisations. 2. An organisation could have one or more sub organisations. 3. An organisation (or sub organisation) would be subscribed to one or more products 4. A the product level, the user would have role based permissions to provide access to certain features.
How would you best design something like this from a rule / hierarchy perspective?
Would you apply role based permissions at every level for the user?
https://tdan.com/modeling-hierarchies/5400
Also,
you would need a user_org table for user org relation.
probably a subscription table and permission table between product and org(subscriptions) and prod and user(permissions)
you might also look at a graph database
If you do this you need to carefully separate attributes that belong to the individual person from attributes that have to do with the relationship between a person in the organization. Often we treat the e-mail address as an ‘attribute of the user’ but I might very well want my mail related to organization A to go to my personal account while organization B goes to my organization B email account…. And worse than that it might not entirely be my decision to make.