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

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

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

我想到的是创建一个具有以下列的表格：

• PERMISSION_CODE
• TAB1（是/否）
• TAB2（是/否）等等..

但这不是一个好的设计，因为每次创建新的选项卡时，都必须添加一列。另一种选择是采用以下结构：

• PERMISSION_CODE
• TAB_NAME

但这样行数会更多。

是否有一种像数组一样的结构来存储选项卡？有人能建议我更好的方法吗？

- Joginder Pawan

---

但这样做会增加行数。- 为什么这是个问题？ - symcbean

Answer 1

但是这样行数会更多。

数据库可以很好地管理大量的行，百万级别的行也不是问题。

有没有一种方法可以将制表符存储在类似数组的结构中？

不要这样做。
这违反了数据库规范化的规则，特别是第一范式，它指出：

如果关系模式R（即一个表）是满足以下条件的关系模式：每个属性都是原子值（不可分解），即属性不能再分成更小的值。

这种方法（在一个列中存储多个值）乍一看似乎很有吸引力，但总有一天，当有人要求你查询“给我所有可以访问选项卡TAB-233的用户”时，你就会遇到麻烦。
此方法的其他缺点包括：

1. 每个查询必须解析数组
2. RDBMS无法使用多值列上的索引，每个查询（如SELECT ... WHERE tab='TAB-333'）必须始终使用全表扫描，并且不可伸缩。
3. 如何更新或删除？每个更新命令必须解包数组，更改/删除一个值，然后重新打包和存储更改后的数组到列中，而不能像简单的UPDATE ... WHERE或DELETE .. WHERE ...那样执行。
4. 您无法在此列上使用任何引用完整性。

有关详细信息，请参见此问题的答案。

---

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

1. 用户拥有角色
2. 角色拥有权限代码
3. 权限代码拥有选项卡

---

但这些是相当n:n的关系，而不是1:n，因为我可以看到：

1. 角色拥有多个用户（因为多个用户可以拥有相同的角色）
2. 权限代码拥有多个角色（因为多个角色可以拥有相同的权限代码）
3. 选项卡拥有多个权限代码（因为多个权限代码可以拥有相同的选项卡）

---

因此，在此模型中将会有以下表：

• 用户（id、用户名、密码等）
• 角色（id、角色名等）
• 权限代码（id、代码名等）
• 选项卡（id、选项卡名等）
• 用户角色（用户id、角色id）
• 角色权限（角色id、权限id）
• 权限选项卡（权限id、选项卡id）

- krokodilko

---

“ROLE_PERMISSIONS( user_id, permission_id )” 是不是应该改为 “( role_id, permission_id )”？ - Nick Bull