DynamoDB单表设计：用户-角色-权限多对多关系查询方案咨询

要实现无冗余权限存储、一次调用获取用户-角色-权限关联数据的需求，你可以按照以下方式设计单表结构并使用PartiQL查询：

1. 单表Schema设计 #

在单表中定义5类数据项，通过复合主键（PK=分区键，SK=排序键）的前缀区分实体类型与关联关系：

• 用户实体：
  • PK: USER#<username>
  • SK: USER#<username>
  • 属性：username及其他用户业务字段
• 角色实体：
  • PK: ROLE#<roleName>
  • SK: ROLE#<roleName>
  • 属性：roleName及其他角色业务字段
• 权限实体：
  • PK: PERM#<permissionName>
  • SK: PERM#<permissionName>
  • 属性：permissionName及其他权限业务字段
• 用户-角色关联项：
  • PK: USER#<username>
  • SK: ROLE#<roleName>
  • 无额外属性，仅用于关联用户与角色
• 角色-权限关联项：
  • PK: ROLE#<roleName>
  • SK: PERM#<permissionName>
  • 无额外属性，仅用于关联角色与权限

这种设计严格遵循单表原则，权限变更时只需更新权限实体和对应的角色-权限关联项，无需修改任何用户相关数据，彻底避免冗余数据的维护问题。

2. 一次调用获取数据：PartiQL查询 #

使用DynamoDB支持的PartiQL（类SQL查询语言），通过一次调用关联所有相关数据项。假设你的表名为auth-system-table，要查询的用户名为User Name，查询语句如下：

SELECT 
  u.username,
  r.roleName,
  p.permissionName
FROM "auth-system-table" u
JOIN "auth-system-table" ur ON u.PK = ur.PK AND ur.SK LIKE 'ROLE#%'
JOIN "auth-system-table" r ON ur.SK = r.PK
JOIN "auth-system-table" rp ON r.PK = rp.PK AND rp.SK LIKE 'PERM#%'
JOIN "auth-system-table" p ON rp.SK = p.PK
WHERE u.PK = 'USER#User Name'

3. 客户端组装目标格式 #

查询返回的是扁平化结果列表，示例如下：

[
  {"username": "User Name", "roleName": "Role Name", "permissionName": "Permission Name"},
  {"username": "User Name", "roleName": "Role Name", "permissionName": "Edit Content"}
]

你只需在客户端将这些数据组装成目标结构，以JavaScript为例：

const rawQueryResult = [/* 上述扁平化结果 */];
const userAuthData = {
  username: rawQueryResult[0].username,
  roles: []
};

// 用Map去重并分组角色与权限
const roleMap = new Map();
rawQueryResult.forEach(item => {
  if (!roleMap.has(item.roleName)) {
    roleMap.set(item.roleName, {
      roleName: item.roleName,
      permissions: []
    });
  }
  roleMap.get(item.roleName).permissions.push({
    permissionName: item.permissionName
  });
});

userAuthData.roles = Array.from(roleMap.values());

4. 性能优化提示 #

• 确保所有JOIN条件都基于主键/排序键，避免触发全表扫描；
• 如果用户关联的角色或权限数量较多，可以通过LIMIT关键字分页，仍保持一次调用获取当前页数据；
• 无需额外创建索引，当前主键设计已满足查询需求。

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