1

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

Improve this question

1 Answer 1

Reset to default
0

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.