Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

enter image description here

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.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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)** 
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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