I am working on a DB design that lets me manage roles and permissions for each user per site. These are my business rules:
- A user can manage multiple sites
- A user can only have one role per site
- A site can have multiple users
I already have two designs that I came up with, I just need validation which one would work better against my business rules.
First is using a ternary table
site_user_role that consists of a relationship between site, user and role table. But this already breaks the rule of a user having only one role per site because those 3 primary keys would be unique and I can create a combination of two roles for a user within 1 site. Any complications with this design?
The second idea is having two binary tables. The site_user and site_user_role table. This arrangement I am quite sure I will not duplicate a role for a user on a site. But Is it weird to have a primary key aside from the Foreign keys on the site_user binary table? Any complications with this design?