DB Design – Assigning users to multiple sites with different roles

Posted on

Question :

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?

enter image description here

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?

enter image description here

Answer :

For the simplicity of maintaining it in the future (especially if it’s not you) I would go with your first design. It contains a table with information on the user (login, name,etc), a table for the site (location, category) and then a table linking a person to a site (Steve is the account admin at Site 3) The second design would make things more complicated than they really needed to be

Leave a Reply

Your email address will not be published. Required fields are marked *