With database privacy roles, the first point to keep in mind is that you define them for each type of data in the app, not just on the user table.
Within this structure, a simple approach may be to 1) have flags on the user profile (ie. for company admin and site admin). And 2) on the respective tables (Company and Site, have a field where you save the reference of who the Company and Site Admins are. And 3) for each individual user to have relevant associations with the Company and Sites. (Ex. Company_Affiliated and Active_Sites).
For the three user types, thinking from the perspective of the user table:
- Company Admin: this user’s company admin is yes
- Site Admin: this user’s site admin is yes and this user’s site’s site admins contains current user
- Worker: This user is Current User
Then for a given Site record:
- Company Admin: current user’s company admin is yes
- Site Admin: current user’s site admin is yes and this site’s admins contains current user
- Worker: this site’s users contains current user.
Just some general thoughts. I may not have captured the nuances of your app, but hopefully that points you in the right direction.
The video below will give you a bit more background on database privacy roles.
Dan (creator of LearnTo - 115+ great Bubble tutorials and on-demand coaching)