How to model complex database role relationships

Question

The purpose of my application is the creation, viewing and editing of articles.

The problem I have is that users will have different roles for an article or group of articles. In addition, the role a user has for a given article or group of articles can only be given to them from a user who has the necessary role (abilities) to make that possible for the article or articles in question.

I am struggling with how to model these relationships.

Answer 1

Here is another option.

Users

user_id (PK)
user_first_name
user_last_name
etc.

Articles

article_id (PK)
article_title
article_body
etc.

Groups

group_id (PK)
group_name

Article_Groups // this ties together the optional M:N articles and groups

group_id (PK)
article_id (PK)

Roles

role_id (PK)
role_description

User_Group_Permissions // users can have ability to view entire groups...

user_id (PK) (FK to Users.user_id) 
group_id (PK) (FK to Groups.group_id)
role_id (FK to Roles.role_id)

User_Article_Permissions // or users can be given ability to view single articles

user_id (PK) (FK to Users.user_id)
article_id (PK) (FK to Articles.article_id)
role_id (FK to Roles.role_id)

Minimum cardinalities Are all 0 on the child side, and 1 on the parent side.

Regarding the INSERTS into User_Group_Permissions and User_Article_Permissions: I would take care of your permissions logic ("the role a user has for a given article or group of articles can only be given to them from a user who has the necessary role (abilities) to make that possible for the article or articles in question") in your code, since that's a business rule.

But, you could use the roles table to keep track of the levels. For example, you could also do something like this, in order to set a minimum role requirement for that article or group and then handle the ASSURANCE of that in your business rules:

Articles

article_id (PK)
article_title
article_body
**minimum_role_to_add (FK to Roles.role_id)**
etc.

Groups

group_id (PK)
group_name
**minimum_role_to_add (FK to Roles.role_id)**