RDBMS中OneOf关系场景下三类管理员权限表最优设计咨询

原设计的核心问题 #

你最初的方案不符合第三范式，存在两个明显缺陷：

• 大量冗余空字段：服务管理员的行不需要存储项目ID、团队ID，造成空间浪费，后续新增管理员类型还要加新字段，扩展性极差
• 无数据库级一致性校验：很容易出现isProjectAdmin=true但admProjectId为NULL的无效数据，完全依赖业务代码校验风险很高

可行设计方案（两种，可根据业务场景选择） #

方案1：单表通用权限结构（适合小型、迭代快的项目）

通过枚举区分管理员类型，加校验约束保证数据合法性，结构最简单：

-- MySQL语法示例，其他数据库可调整枚举和约束写法
CREATE TABLE AdminInfo (
  admin_id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '权限记录主键',
  userid BIGINT NOT NULL COMMENT '关联用户ID',
  admin_type ENUM('ServiceAdmin', 'ProjectAdmin', 'TeamAdmin') NOT NULL COMMENT '管理员类型',
  related_resource_id BIGINT DEFAULT NULL COMMENT '关联资源ID：项目ID/团队ID，服务管理员为NULL',
  -- 外键约束关联用户表
  CONSTRAINT `fk_admin_user` FOREIGN KEY (userid) REFERENCES UserInfo(userid) ON DELETE CASCADE ON UPDATE CASCADE,
  -- 唯一约束避免重复授权
  UNIQUE KEY `uk_user_type_resource` (userid, admin_type, related_resource_id),
  -- 校验约束保证不同类型的资源ID规则符合要求
  CONSTRAINT `chk_admin_resource` CHECK (
    (admin_type = 'ServiceAdmin' AND related_resource_id IS NULL)
    OR (admin_type IN ('ProjectAdmin', 'TeamAdmin') AND related_resource_id IS NOT NULL)
  )
) COMMENT '管理员权限表';

使用示例：

• 服务管理员：userid=1, admin_type='ServiceAdmin', related_resource_id=NULL
• 项目1000管理员：userid=2, admin_type='ProjectAdmin', related_resource_id=1000
• 团队100管理员：userid=3, admin_type='TeamAdmin', related_resource_id=100
  优点：仅一张表，查询简单，新增管理员类型只需加枚举值无需修改表结构
  缺点：related_resource_id无法同时关联项目表和团队表的外键，资源ID合法性需要业务代码校验

方案2：主表+子表分类型结构（适合中大型、对数据一致性要求高的项目）

完全符合RDBMS设计规范，通过主表存通用属性，子表存各类型管理员的差异化关联属性，所有关联都支持外键约束：

-- 管理员主表：存所有管理员通用属性
CREATE TABLE AdminInfo (
  admin_id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '管理员记录主键',
  userid BIGINT NOT NULL COMMENT '关联用户ID',
  admin_type ENUM('ServiceAdmin', 'ProjectAdmin', 'TeamAdmin') NOT NULL COMMENT '管理员类型',
  CONSTRAINT `fk_admin_user` FOREIGN KEY (userid) REFERENCES UserInfo(userid) ON DELETE CASCADE ON UPDATE CASCADE,
  UNIQUE KEY `uk_user_type` (userid, admin_type)
) COMMENT '管理员主表';

-- 服务管理员子表：无额外关联资源
CREATE TABLE ServiceAdmin (
  admin_id BIGINT PRIMARY KEY COMMENT '关联主表ID',
  CONSTRAINT `fk_service_admin_main` FOREIGN KEY (admin_id) REFERENCES AdminInfo(admin_id) ON DELETE CASCADE ON UPDATE CASCADE
) COMMENT '服务管理员子表';

-- 项目管理员子表：关联项目表
CREATE TABLE ProjectAdmin (
  admin_id BIGINT PRIMARY KEY COMMENT '关联主表ID',
  projectid BIGINT NOT NULL COMMENT '关联项目ID',
  CONSTRAINT `fk_project_admin_main` FOREIGN KEY (admin_id) REFERENCES AdminInfo(admin_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_project_admin_project` FOREIGN KEY (projectid) REFERENCES ProjectInfo(projectid) ON DELETE CASCADE ON UPDATE CASCADE
) COMMENT '项目管理员子表';

-- 团队管理员子表：关联团队表
CREATE TABLE TeamAdmin (
  admin_id BIGINT PRIMARY KEY COMMENT '关联主表ID',
  teamid BIGINT NOT NULL COMMENT '关联团队ID',
  CONSTRAINT `fk_team_admin_main` FOREIGN KEY (admin_id) REFERENCES AdminInfo(admin_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_team_admin_team` FOREIGN KEY (teamid) REFERENCES ProjectTeamInfo(teamid) ON DELETE CASCADE ON UPDATE CASCADE
) COMMENT '团队管理员子表';

优点：所有关联关系都有数据库级外键约束，不会出现无效数据，各类型管理员的扩展属性可以单独在对应子表新增，互不影响
缺点：新增权限需要先插主表再插对应子表，查询全量权限需要关联多表，操作复杂度略高

选型建议 #

不需要完全拆成三张独立的表，加统一主表即可解决分表带来的查询麻烦。如果是小型工具类项目选方案1足够，中大型业务系统优先选方案2。

内容的提问来源于stack exchange，提问作者Gipyo.Choi