Confusion with data base model creation

Question

0.00/5 (No votes)

See more:

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 database model 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.

VB

PROJECTS
Id
Name

VB

PROJECT_GROUP
Id
Name

VB

USERS
Id
Name

VB

USER_GROUP
Id
Name

VB

PERMISSIONS
Id
Name

VB

USER_X_USER_GROUP
Id
User_Id
User_Group_Id

VB

PROJECT_X_Project_GROUP
Id
Project_Id
Project_Group_Id

VB

USER_GROUP_X_PROJECT_GROUP
Id
User_Group_Id
Project_Group_Id

VB

PERMISSIONS_X_USER_GROUP
Id
User_Group_Id
Permission_Id

Posted 4-Dec-14 7:07am

s#@!k

Updated 4-Dec-14 8:09am

v4

Add a Solution

Comments

ZurdoDev 4-Dec-14 13:08pm

I don't follow what you need.

s#@!k 4-Dec-14 13:25pm

Thanks for reply where you didn't understood. please have a look on the model diagram.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.

PIEBALDconsult 4-Dec-14 13:48pm

"please have a look on the model diagram"

Many here are hesitant to click an unknown link.

s#@!k 4-Dec-14 13:52pm

may be but i didn't get any idea to upload table relation diagram. or plz advise me how to show what i have worked.

s#@!k 4-Dec-14 14:01pm

i had updated the question with the tables but can't able to draw mapping line in the text field. please try to understand.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

syed shanu · Answer 1 · 2014-12-04T15:15:00

You can use Status column in both User and in Project Table.
In your case a user group can have more than one User and in a Project Group more than one Project can available.

If the permission (projected to user group or project group) is set to one project Group or User Group then all the users and projects have that permission.

But now what you want is for one or some user or project from that group not to set the permission.

For this you can use Status Field in Both user and in project Table.
By default you can set the status as "Y". Here "Y" means the user or project has permission. And also you will check for the Users or groups permission.
Now for one User or project under group if you want to revoke the permission set the status for that user to "N".

In condition you can check for both permission for that group for the user or for the Project and also you can check for the Status for the user and project where the status=’Y’.

For example your query might be like this

SQL

Select U.users ,P.permissionName
From 
permissionName P
left Outer Join  Users_Xgroup UG 
ON P.permissionID=UG.permissionID
Left Outer JOIN  users U
ON UG.UserID=U.UserID
Where
P.permissionID=1
and
u.Status='Y'

Jörgen Andersson · Answer 2 · 2014-12-15T10:46:00

I'm late for the party it seems.
Anyway, here's my take, use a simplified version of how Oracle have set up their own permission system.

User and groups belongs in the same self referencing table with just a field setting that defines if it's a group or a user.
You need to add logics that only allows a user or a group to have a group as a parent. But a group can have another group as it's parent.
You also need to add a check that you don't get a circular reference.

Same is valid for Permissions and Roles.

Projects can of course be a part of a larger project. Also here check for circular references.

You also need to add a possibility to Allow or Deny a Permission.

Here's a suggestion Relational_1_-_SubView_1.png (25.5 KB)

And the DDL:

SQL

CREATE TABLE PERMISSIONS
  (
    PERMISSIONID INTEGER NOT NULL ,
    PARENTID     INTEGER ,
    ISROLE       NUMBER (1) NOT NULL ,
    NAME NVARCHAR2 (64)
  ) ;
ALTER TABLE PERMISSIONS ADD CHECK ( ISROLE IN (0, 1)) ;
ALTER TABLE PERMISSIONS ADD CONSTRAINT PERMISSIONS_PK PRIMARY KEY ( PERMISSIONID ) ;
CREATE TABLE PERMISSIONTYPE
  (
    PERMISSIONTYPEID NUMBER (1) NOT NULL ,
    NAME             VARCHAR2 (5 CHAR) NOT NULL
  ) ;
ALTER TABLE PERMISSIONTYPE ADD CONSTRAINT PERMISSIONTYPE_PK PRIMARY KEY ( PERMISSIONTYPEID ) ;
CREATE TABLE PROJECTS
  (
    PROJECTID INTEGER NOT NULL ,
    PARENTID  INTEGER ,
    NAME NVARCHAR2 (64) NOT NULL
  ) ;
ALTER TABLE PROJECTS ADD CONSTRAINT PROJECTS_PK PRIMARY KEY ( PROJECTID ) ;
CREATE TABLE USERPROJECTPERMISSIONS
  (
    USERID           INTEGER CONSTRAINT NNC_UPP_USERS_USERID NOT NULL ,
    PERMISSIONID     INTEGER CONSTRAINT NNC_UPP_PERMISSIONS_ID NOT NULL ,
    PROJECTID        INTEGER CONSTRAINT NNC_UPP_PROJECTS_PROJECTID NOT NULL ,
    PERMISSIONTYPEID NUMBER (1) NOT NULL
  ) ;
ALTER TABLE USERPROJECTPERMISSIONS ADD CONSTRAINT USERPROJECTPERMISSIONS_PK PRIMARY KEY ( USERID, PERMISSIONID, PROJECTID ) ;
CREATE TABLE USERS
  (
    USERID   INTEGER CONSTRAINT NNC_USERS_USERID NOT NULL ,
    PARENTID INTEGER ,
    ISGROUP  NUMBER (1) CONSTRAINT NNC_USERS_ISUSERGROUP NOT NULL ,
    NAME NVARCHAR2 (64)
  ) ;
ALTER TABLE USERS ADD CHECK ( ISGROUP IN (0, 1)) ;
ALTER TABLE USERS ADD CONSTRAINT USERS_PK PRIMARY KEY ( USERID ) ;
ALTER TABLE PERMISSIONS ADD CONSTRAINT PERMISSIONS_PERMISSIONS_FK FOREIGN KEY ( PARENTID ) REFERENCES PERMISSIONS ( PERMISSIONID ) ;
ALTER TABLE PROJECTS ADD CONSTRAINT PROJECTS_PROJECTS_FK FOREIGN KEY ( PARENTID ) REFERENCES PROJECTS ( PROJECTID ) ;
ALTER TABLE USERPROJECTPERMISSIONS ADD CONSTRAINT UPP_PERMISSIONS_FK FOREIGN KEY ( PERMISSIONID ) REFERENCES PERMISSIONS ( PERMISSIONID ) ;
ALTER TABLE USERPROJECTPERMISSIONS ADD CONSTRAINT UPP_PERMISSIONTYPE_FK FOREIGN KEY ( PERMISSIONTYPEID ) REFERENCES PERMISSIONTYPE ( PERMISSIONTYPEID ) ;
ALTER TABLE USERPROJECTPERMISSIONS ADD CONSTRAINT UPP_PROJECTS_FK FOREIGN KEY ( PROJECTID ) REFERENCES PROJECTS ( PROJECTID ) ;
ALTER TABLE USERPROJECTPERMISSIONS ADD CONSTRAINT UPP_USERS_FK FOREIGN KEY ( USERID ) REFERENCES USERS ( USERID ) ;
ALTER TABLE USERS ADD CONSTRAINT USERS_USERS_FK FOREIGN KEY ( PARENTID ) REFERENCES USERS ( USERID ) ;

Confusion with data base model creation

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0