sql - 基于用户角色权限的数据库设计

Question

我想为 Java Web 应用程序设计数据库，其中 用户 可以有一个特定的 角色还有那个角色可以根据 获得访问特定选项卡的权限PERMISSION_CODE

所以，我创建了三个表，如:

 CREATE TABLE "PAWAN"."USERS_TABLE" 
    (   
    "ID" NUMBER(4,0) NOT NULL ENABLE, 
    "USER_NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
    "PASSWORD" VARCHAR2(100 BYTE) NOT NULL ENABLE, 
    "FIRST_NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
    "LAST_NAME" VARCHAR2(20 BYTE), 
    "CREATED_DATE" DATE DEFAULT sysdate, 
    "ROLE_ID_FK" NUMBER(4,0) NOT NULL ENABLE, 
    PRIMARY KEY ("ID") ENABLE,
    CONSTRAINT "FK_ROLE_ID" FOREIGN KEY ("ROLE_ID_FK")
    REFERENCES "PAWAN"."USER_ROLES" ("ID") ENABLE
    );     

  CREATE TABLE "PAWAN"."USER_ROLES" 
    (   
    "ID" NUMBER(4,0) NOT NULL ENABLE, 
    "ROLE_TYPE" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
    "ROLE_DESCRIPTION" VARCHAR2(100 BYTE) NOT NULL ENABLE, 
    "CREATED_DATE" DATE DEFAULT sysdate, 
    "PERMISSION_ID_FK" NUMBER(4,0) NOT NULL ENABLE, 
    PRIMARY KEY ("ID") ENABLE, 
    CONSTRAINT "PERMISSION_ID" FOREIGN KEY ("PERMISSION_ID_FK")
    REFERENCES "PAWAN"."PERMISSIONS_TABLES" ("ID") ENABLE
    );   


CREATE TABLE "PAWAN"."PERMISSIONS_TABLE" 
   (    
    "ID" NUMBER(4,0) NOT NULL ENABLE, 
    "PERMISSION_CODE" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
    "PERMISSION_DESC" VARCHAR2(100 BYTE) NOT NULL ENABLE, 
    "CREATED_DATE" DATE DEFAULT sysdate, 
     PRIMARY KEY ("ID") ENABLE
   );

现在，我需要第四个表，其中包含属于特定 的选项卡列表。 PERMISSION_CODE 的 权限表

我想到的是有一张 table ，上面有这样的列

PERMISSION_CODE

TAB1(是/否)

TAB2(是/否)等等..

但这不是一个好的设计，因为每次创建一个新选项卡时，我都必须多添加一列。
一种选择是具有如下结构:

PERMISSION_CODE

TAB_NAME

但是这样不行。行数会更多。

有没有办法将标签存储在类似结构的数组中？任何人都可以建议我一种更好的方法吗？

Answer 1

But this way no. of rows will be more.

数据库擅长管理大量行，百万行不是问题。

Is there any way to store tabs in a array like structure?

不不不 - 不要走那条路。
这打破了 Database normalization规则，尤其是 first normal form那说:

First normal form (1NF) is a property of a relation in a relational database. A relation is in first normal form if and only if the domain of each attribute contains only atomic (indivisible) values, and the value of each attribute contains only a single value from that domain.

这种方法(在一个列中存储许多值)乍一看似乎很有吸引力，但有一天当有人问你一个查询“给我所有有权访问选项卡 TAB-233 的用户”时，你会陷入困境。这种方法的其他一些缺点:

每个查询都必须解析数组

RDBMS 不能在多值列上使用索引，每个查询都像 SELECT ... WHERE tab='TAB-333'必须始终使用全表扫描并且不可扩展。

如何更新或删除？而不是简单 UPDATE ... WHERE或 DELETE .. WHERE ...每个更新命令必须解包数组，更改/删除一个值并将更改的数组打包并存储回列。

您不能在此列上使用任何参照完整性。

请看对 this question的回答更多细节。

在您的模型中，存在以下关系:

用户 有 角色

角色有 权限代码

权限码有 标签

但是有 n:n 关系，而不是 1:n，因为我可以看到:

角色有 多个用户(因为多个用户可以具有相同的角色)

权限码有 多个角色(因为多个角色可以拥有相同的权限代码)

标签 有 许多权限代码(因为多个权限代码可以具有相同的选项卡)

所以在这个模型中会有以下表格:

用户(ID、用户名、密码等)

角色(id、role_name 等)

PERMISSION_CODES(id、code_name 等)

TABS(id、tab_name 等)

用户角色(用户 ID，角色 ID)

ROLE_PERMISSIONS( user_id, perrmission_id )

PERMISSION_TABS(permission_id, tb_id)