Confusion with data base model creation

I am creating data base design for the below scenario and struck with a problem.
I have Users, Projects, and Permissions{ex: read,Edit,delete} tables
Then,
Problem: I have to give permission to users over projects.

I Tried: http://s10.postimg.org/4kgq9uay1/Model.jpg
Please have a look on above link that I have created.
Problem with my Solution: In a user group(ex: some x group) having edit permission over project group(ex: some y project group).
note:user group may have many users, project group may have many projects
In the group, if i want "revoke permission for one user in the user group over one project in the project group having more projects"
How to handle this situation on this model if it needs to create any other mapping or else i am not getting. Please help on this with any suggestions.

example: user group id=1 mapped to u1,u2,u3 users, project group id=1 mapped to p1,p2,p3 projects. user group 1 mapped to project group 1. if user group id=1 having edit permissions, all user in the group will have the same edit permissions. but if i want for only u2 revoke edit permission for only p2 project what should i do. hope you understood now.

As you have figured out, you can't do what you want with your current model; you need to link permissions to users.

My approach:
http://44galena.org.za/sha_ik_001/UsersandGroups.jpg

Users belong to one or more groups. When creating a user, you link him/her to one or more groups. The group permissions will function as a template when a project is added to a user (see below).

When you assign a project to a group, you do not actually assign it to the group but to the users in that group.
http://44galena.org.za/sha_ik_001/UsersAndProjects.jpg

Your business logic will define what happens with the permissions; you can use the template permissions and copy them over to the UserAndProjectPermissions.

If you want to add or revoke a permission for a user on a project, you can remove the entry from the UsersAndProjectPermissions table.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Wim Sturkenboom11y ago
As you have figured out, you can't do what you want with your current model; you need to link permissions to users.

My approach:
http://44galena.org.za/sha_ik_001/UsersandGroups.jpg

Users belong to one or more groups. When creating a user, you link him/her to one or more groups. The group permissions will function as a template when a project is added to a user (see below).

When you assign a project to a group, you do not actually assign it to the group but to the users in that group.
http://44galena.org.za/sha_ik_001/UsersAndProjects.jpg

Your business logic will define what happens with the permissions; you can use the template permissions and copy them over to the UserAndProjectPermissions.

If you want to add or revoke a permission for a user on a project, you can remove the entry from the UsersAndProjectPermissions table.