嘿，这个需求其实是典型的多对多关联场景，结合你现有的表结构，咱们可以通过新增一张关联表来实现用户与多角色的绑定，同时保留角色对应特定资源的特性，具体方案如下：

设计思路 #

用户和角色是多对多关系（一个用户可拥有多个角色，一个角色可分配给多个用户），所以需要一张中间表来存储两者的映射关系。你的Role表已经有ResourceType字段来标识角色对应的特定资源，这个字段可以直接用来区分不同资源类别的角色，无需额外修改原表结构。

具体表结构实现 #

首先保留你现有的User和Role表，然后新增一张UserRole关联表，SQL代码如下：

-- 保留原User表
CREATE TABLE [dbo].[User] ( 
    [Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, 
    [Username] NVARCHAR(200) NOT NULL, 
    [Domain] NVARCHAR(200) NOT NULL, 
    [IsAdministrator] BIT NOT NULL DEFAULT 'false', 
    [IsGroup] BIT NULL DEFAULT 'false'
) 

-- 保留原Role表
CREATE TABLE [dbo].[Role] ( 
    [Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, 
    [Name] NVARCHAR(500) NOT NULL, 
    [Description] NVARCHAR(1000) NOT NULL, 
    [ResourceType] NVARCHAR(100) NOT NULL
)

-- 新增用户-角色关联表
CREATE TABLE [dbo].[UserRole] (
    [UserId] UNIQUEIDENTIFIER NOT NULL,
    [RoleId] UNIQUEIDENTIFIER NOT NULL,
    -- 可选：添加分配时间等额外字段
    [AssignedAt] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
    -- 联合主键，避免同一用户重复分配同一角色
    PRIMARY KEY ([UserId], [RoleId]),
    -- 外键关联User表，删除用户时自动清理关联记录
    FOREIGN KEY ([UserId]) REFERENCES [dbo].[User]([Id]) ON DELETE CASCADE,
    -- 外键关联Role表，删除角色时自动清理关联记录
    FOREIGN KEY ([RoleId]) REFERENCES [dbo].[Role]([Id]) ON DELETE CASCADE
)

关键说明 #

• 联合主键：UserId和RoleId作为联合主键，确保一个用户不会被重复分配同一个角色，保证数据唯一性。
• 外键约束：通过外键关联User和Role表，确保关联的用户或角色真实存在；ON DELETE CASCADE规则能自动清理失效的关联记录，避免出现脏数据。
• 资源类型匹配：Role表的ResourceType字段已经明确了角色对应的特定资源（比如ResourceType为"Document"代表文档类资源权限），分配角色时直接选择对应资源类型的条目即可。

使用示例 #

比如给ID为12345678-1234-5678-1234-567812345678的用户分配ID为87654321-4321-8765-4321-876543210987的文档类角色：

INSERT INTO [dbo].[UserRole] ([UserId], [RoleId])
VALUES ('12345678-1234-5678-1234-567812345678', '87654321-4321-8765-4321-876543210987')

内容的提问来源于stack exchange，提问作者cyberpirate92