sql-server - 如何在 SQL Server 数据库中设计用户/角色架构？

Question

我想设计一个用户/角色系统:

用户有名称和密码，然后用户可以具有多个角色，例如管理员。

为此，我创建了一个如下所示的架构:

用户:

CREATE TABLE [dbo].[Users]
(
    [id] [int] NOT NULL,
    [name] [nvarchar](50) NULL,
    [password] [nvarchar](50) NULL,

    CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ([id] ASC)
)

角色:

CREATE TABLE [dbo].[Roles]
(
    [id] [int] NOT NULL,
    [name] [nvarchar](50) NULL,

    CONSTRAINT [PK_Roles] PRIMARY KEY CLUSTERED ([id] ASC)
)

用户角色:

CREATE TABLE [dbo].[User_Roles]
(
    [id] [int] NOT NULL,
    [User_id] [int] NOT NULL,
    [Role_id] [int] NOT NULL,

    CONSTRAINT [PK_User_Roles] PRIMARY KEY CLUSTERED ([id] ASC)
)

我的问题是:我应该使用外键User_Roles.User_id -> User.Id

如果是，为什么？

Answer 1

不太清楚你的意思，但是......

• User_Roles 应该只有 2 列 User_id 和 Role_id
  这两者构成主键
• 您不需要额外的 id 列User_Roles
• User_id 是 Users.id 的外键
• Role_id 是 Roles.id
的外键

编辑:现在我明白了。是的，always use foreign keys

还有...

• 如果password是nvarchar(50)，这意味着纯文本。 This is bad.
• 如果Users 中有重复的name 值，您如何知道哪个用户是哪个？
  特别是如果他们有相同的密码(这种情况会发生，因为我们这些肉袋很愚蠢)

主键创建后评论后编辑...

CREATE TABLE [dbo].[User_Roles]
(
    [User_id] [int] NOT NULL,
    [Role_id] [int] NOT NULL,

    CONSTRAINT [PK_User_Roles] PRIMARY KEY CLUSTERED ([User_id], [Role_id]),
    CONSTRAINT [UQ_ReversePK] UNIQUE ([Role_id], [User_id])
)