How to model complex database role relationships 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.
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)**