与数据库模型创建混淆 [英] Confusion with data base model creation

问题描述

我正在为以下场景创建数据库设计并遇到问题。

我有用户，项目和权限{ex：read，Edit，delete} tables

然后，

问题：我必须向用户授予项目许可。



我试过： http://s10.postimg.org/4kgq9uay1/Model.jpg [ ^ ]

请有一个查看我创建的上述数据库模型。

我的解决方案问题：在具有项目组编辑权限的用户组（例如：某些x组）中（例如：某些y项目组）。

注意：用户组可能有很多用户，项目组可能有很多项目

在组中，如果我想要撤销一个用户的权限在项目组中的一个项目的用户组中有更多项目

如何在此模式下处理这种情况l如果它需要创建任何其他映射，否则我没有得到。如有任何建议，请提供帮助。

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.

PROJECTS
Id
Name

PROJECT_GROUP
Id
Name

USERS
Id
Name

USER_GROUP
Id
Name

PERMISSIONS
Id
Name

USER_X_USER_GROUP
Id
User_Id
User_Group_Id

< br $>

PROJECT_X_Project_GROUP
Id
Project_Id
Project_Group_Id

USER_GROUP_X_PROJECT_GROUP
Id
User_Group_Id
Project_Group_Id

PERMISSIONS_X_USER_GROUP
Id
User_Group_Id
Permission_Id

推荐答案

您可以在用户和项目表中使用状态列。

在您的情况下，用户组p可以有多个用户，在项目组中可以有多个项目可用。



如果权限（预计用户组或项目组）设置为一个项目组或用户组然后所有用户和项目都拥有该权限。



但是现在你想要的是那个组中的一个或一些用户或项目不要设置权限。



为此，您可以在用户和项目表中使用状态字段。

默认情况下，您可以设置状态作为Y。这里Y表示用户或项目有权限。此外，您还将检查用户或组的权限。

现在，对于组下的一个用户或项目，如果要撤消权限，请将该用户的状态设置为N。



在条件下，您可以为用户或项目检查该组的权限，还可以检查状态='Y'的用户和项目的状态。



例如你的查询可能是这样的

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

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'

我似乎迟到了派对。

无论如何，这是我的看法，使用Oracle的简化版本建立了自己的许可制度。



用户和群组属于同一个自引用表，只有一个字段设置，用于定义是组还是用户。

你需要添加仅允许用户或组将组作为父组的逻辑。但是一个团体可以拥有另一个团体，因为它是父母。

你还需要添加一个你没有得到循环参考的支票。



同样适用于权限和角色。



项目当然可以成为更大项目的一部分。此外，还要检查循环引用。



您还需要添加允许或拒绝权限的可能性。



这是一个建议 Relational_1 _-_ SubView_1.png（25.5 KB）



和DDL：

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:

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 ) ;

这篇关于与数据库模型创建混淆的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！