如何在 SQL Server 数据库中设计用户/角色架构? [英] How to design a user/role schema in a SQL Server database?

问题描述

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

I want to design a user/role system:

用户有名字和密码，然后用户可以有多个角色，比如Admin.

The users have a name and a password and then the user can have several roles like Admin.

为此，我创建了这样的架构:

For this I created a schema like this:

用户:

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

user_roles:

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 ->用户 ID

My question is: should I use foreign keys User_Roles.User_id -> User.Id

如果是为什么?

推荐答案

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

Not quite sure what you mean, but...

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

• User_Roles should have 2 columns only User_id and Role_id
  Both of these form the Primary Key
• You do not need an extra id column User_Roles
• User_id is a foreign key to Users.id
• Role_id is a foreign key to Roles.id

现在我明白了.是的，始终使用外键

还有...

• 如果 password 是 nvarchar(50)，这意味着纯文本.这糟糕.
• 如果您在 Users 中有重复的 name 值，您如何知道哪个用户是哪个?
  特别是如果他们有相同的密码(这会发生，因为我们的肉袋很蠢)

• if password is nvarchar(50), this implies plain text. This is bad.
• if you have duplicate name values in Users, how do you know which user is which?
  Especially if they have the same password (which will happen because we meatsacks are stupid)

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

Edit after comment after primary key creation...

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

这篇关于如何在 SQL Server 数据库中设计用户/角色架构?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！