使用 Junction & 标准化外键查找表

Question

最近，我一直在摸索数据库规范化与连接表和查找表方面的外键之间的关系。

我目前有以下表格：Users、UserTypes、Roles、UsersInRoles 和 Permissions。 UserTypes 只是一个查找表，通过 Users 表中的外键提供类型名称和描述。角色是具有通过 UsersInRoles 表链接到每个用户的关联权限的各种角色。

我需要提出一个结构，允许我为每个用户提供多个角色，此外还为每个用户提供其所属的固定角色中可能未涵盖的特殊权限。

我有一个来自 Users 表的 UsersInRoles 表的外键，但认为它没有意义。相反，使用 Users 表到 UserTypes 表的外键似乎非常有意义。这是经验法则吗？连接表具有链接到其连接的表的主键的外键，而主表具有链接到关联查找表的主键的外键？

参数：

• 每个用户可以拥有一个或多个角色
• 每个角色都有一组固定的权限
• 每个用户可以拥有其角色未提供的附加权限

我怀疑我可能还需要一个 PermissionsInRoles 连接表以及一个对于用户权限？但这太荒谬了不是吗？一定有更好的方法。我完全相信我在这里失去了理智，哈哈。任何帮助将不胜感激。这让我头晕目眩：P

更新
这基本上是如何设置的吗？我可能会删除 UsersInRoles 表，以便每个用户只能担任一个角色，并且可以通过 SpecialPermissions 连接表添加其他权限。从用户界面的角度来看，我认为在向用户分配权限时可能会很好，选择“角色”只需选中与该角色关联的相应框，然后进行自定义并提交。这样我想我可能只需要用户和权限表之间的连接表？啊。这对于第一次数据库设计者来说是相当令人畏惧的哈哈。还记得你刚开始创业的时候吗？或者也许你们比我更有天才，哈哈。

架构图像链接（尚无法发布图像）

这是一篇简洁的学术文章（尽管已经有 10 岁了）关于查询驱动的数据库设计，标题为：“针对多种查询模式的稳健数据库设计”。结论部分有一个有趣的方法。

Answer 1

我怀疑我可能还需要 PermissionsInRoles 连接表
作为 PermissionsInUsers 之一？

好吧，您已经说过其中一个要求是“每个角色都有一组固定的权限”。因此，为了满足该要求，您需要存储适用于每个角色的权限。

Table: role_permissions
PK:    (Role, Permission)

Role    Permission
--
User    Create
User    Update
Admin   Create
Admin   Update
Admin   Delete

您不需要两个不同的表来实现该要求。

出于同样的原因，您已经说过“每个用户都可以拥有其角色未提供的附加权限”。为了满足该要求，您必须存储用户特定的权限。

Table: user_permissions
PK:    (username, permission)

username    permission
--
user1       Rename
user1       Leak to News of the World
user2       Randomly corrupt data

因此，再次强调，您不需要两个不同的表来实现该要求。这两个表都采用 5NF。

但这太荒谬了不是吗？

有什么可笑的呢？

• 您对权限有非常详细的要求吗？
• 您将业务数据（如权限）存储在表中吗？
• 是否需要多个表来模拟您的权限要求？
• 还有别的事吗？

如果您需要有关实际表的具体建议，请编辑您的问题并为表插入 DDL。

---

后来

我看了你的图表。并非每张桌子都需要 ID 号； id 数字与标准化无关。

如果我正在设计您的系统，我可能不会在 Roles、Permissions 和 UserTypes 表中使用 id 编号，直到我发现 id 编号可以修复的性能问题。 （在过去 30 年的大多数系统中，这意味着，几乎从来没有。）在使用 ID 号之前，我还会考虑并测试使用人类可读的代码。人类可读的代码通常不需要连接； id 号始终需要连接。

在大多数 SQL dbms 中，您可以在 CREATE DOMAIN 语句中组合数据类型和检查约束。在 PostgreSQL 中，您可以使用类似的方法来减少表的数量。

CREATE DOMAIN role AS VARCHAR(7) NOT NULL
  CHECK (VALUE in ('Admin', 'User', 'Guest'));

其次，

CREATE TABLE user_roles (
  user_id integer not null references users (id),
  role_name role 
);

当行数稳定且相对较小时，使用 CREATE DOMAIN 语句替换表最为有用。就我个人而言，我更喜欢有桌子。

如果您坚持使用 ID 号，则还需要对 Roles.RoleName、Permissions.Description 和 UserTypes.UserType 进行 UNIQUE 约束。

否则，你似乎做得很好。

Answer 2

如果您的安全对象只是一个产品表（或一组表），并且您正在使用 SSMS (SQL-Server Management Studio)，那么您不应该从头开始发明自己的安全模式。

我建议您在 SSMS 中设置用户和角色 - 展开数据库，然后 ->安全性-->用户等。右键单击用户，查找安全对象，然后您可以将用户分配给角色，也可以直接分配对象（表等）。或者右键单击角色，您会看到类似的选项。无论你做什么，如果可以的话，都不要创建自己的安全模式。

如果您需要您的网络应用程序能够访问数据库，请查看“实用程序帐户”（这些帐户就像用户，在服务器级别而不是数据库级别创建，但您可以从那里将它们带入数据库。） ;或者，如果您能够在用户登录数据库时从您的内部网络传递用户的信用信息，请考虑模拟。可以将实用程序帐户或用户分配给角色，或者在没有角色的情况下授予对数据库对象的直接访问权限 - 无论您需要什么。

Answer 3

我之前在类似的场景中做过一件事：

将角色和用户保留在同一个表中。

有一个对象表（您将授予权限的表/查询/表单/等）

有一个权限表 - 这是您将角色/用户链接到对象的地方（即，John 可以在表 1 上进行 SELECT）

最后，有一个继承表 - 这是您将角色/用户相互链接的地方（即，John 有权执行 Role1 可以执行的任何操作）

例如 - 像这样的结构：

例如：

Users Table:
UserID -- User -- UserTypeID
1 ------- John Smith --- 1
2 ------- Sally Fort --- 1
3 ------- Public Role -- 2
4 ------- Special Role - 2

UserType Table:
UserTypeID -- Description
1 ----------- Human Being
2 ----------- Role

Objects Table:
1 -- Data-Entry Form 1
2 -- Data-Entry Form 2
3 -- Query 1
4 -- Table 1

Permissions Table
UserID -- ObjectID -- Permission
1      -- 1        -- Update (This says John can Update Data-Entry Form 1 (via direct permission))
3      -- 1        -- Update (This says that the Public Role can Update Data-Entry Form 1)
3      -- 2        -- Update (...as well as Data-Entry Form 2)
4      -- 3        -- Select (This says that the special role can Select from Query1)
4      -- 4        -- Insert (...the special role can Insert into Table1)

Permission Inheritance Table
UserID -- UserID_ToInheritFrom
1      -- 3 (this says John can do whatever the Public Role can do)
1      -- 4 (this says John can do whatever the Special Role can do)
2      -- 3 (this says Sally can do whatever the Public Role can do)

那么如果您想查询, “约翰能做什么？”,你会做这样的事情：

SELECT
   ObjectID,
   Permission
FROM
   PermissionsTable
WHERE
   UserID = 1 -- John has direct permission
   OR UserID IN (SELECT UserID_ToInheritFrom FROM PermissionInheritanceTable WHERE UserID = 1)
   -- John has indirect permission via the Permission Inheritance Table (or you can call it the "role
   -- membership table" if that's easier for you to think of that way.)

这个实现效果很好。如果您想查看类似的实现，请查看 SQL-Server 的实现（或者更好的是，使用它，而不是从头开始重新创建轮子。）