PostgreSQL中组对多文件权限控制的表设计咨询

需求说明 #

需定义符合以下要求的表结构：

• 使用RDBMS（PostgreSQL）
• 组表拥有对多个文件的操作权限
• 组的操作权限包含两种：读取（read）和编辑（edit）

现有思路 #

最初尝试在groups表中设置两个列，分别存储多个可读文件ID（列名：can_read_file_ids）和可编辑文件ID（列名：can_edit_file_ids）。

但考虑到单列不应存储多值（多对多关系），遂设计两个中间表及一个files表，对应的Mermaid ER图如下：

erDiagram
    groups ||--o{ can_read_group_files: ""
    groups ||--o{ can_edit_group_files: ""
    files ||--o{ can_read_group_files: ""
    files ||--o{ can_edit_group_files: ""
    groups {
        int id PK
        string name "Name of group"
    }
    can_read_group_files {
        int id PK
        int group_id FK
        int file_id FK "Readable file ID"
    }
    can_edit_group_files {
        int id PK
        int group_id FK
        int file_id FK "Editable file ID"
    }
    files {
        int id PK
        string name
    }

后续又考虑新增permissions表，仅用一个中间表并添加permission_id列，以便未来扩展更多权限（目前仅需读、编辑权限），对应的Mermaid ER图如下：

erDiagram
    groups ||--o{ group_files: ""
    permissions ||--o{ group_files: ""
    files ||--o{ group_files: ""
    groups {
        int id PK
        string name "Name of group"
    }
    group_files {
        int id PK
        int group_id FK
        int file_id FK "ID of file"
        int permission_id FK
    }
    permissions {
        int id PK
        string name
        string description
    }
    files {
        int id PK
        string name
    }

咨询问题 #

1. 中间表是否可以设置超过4列？（第二个ER图中已设置3个外键列）
2. 应采用哪种表设计方案？是否有更优的表设计方案？

1. 中间表列数问题 #

完全可以，PostgreSQL（以及所有主流RDBMS）没有限制中间表的列数——只要不超出数据库的最大列数上限（PostgreSQL默认支持最多1600列，远超出常规业务需求）。中间表根据业务需要添加字段是合理的，比如你当前的3个外键，甚至还可以追加created_at（创建时间）、updated_at（更新时间）这类审计字段，都没有问题。

2. 表设计方案选择及优化 #

方案对比

• 第一种双中间表方案：优点是查询读/编辑权限时直接对应表，SQL语句简单直接；缺点是扩展性极差——如果未来需要新增权限（比如删除、分享），必须新建对应的中间表，会导致表结构越来越臃肿，维护成本飙升。
• 第二种权限表+单中间表方案：这是更推荐的方案，扩展性极强，新增权限只需要在permissions表中插入一条记录，无需修改任何表结构，符合软件工程的开闭原则，同时能统一管理所有权限类型，维护更便捷。

更优优化方案

可以在第二种方案的基础上优化group_files表的主键设计：将group_id、file_id、permission_id设为复合主键，去掉自增的id列。这样能从数据库层面保证“同一个组对同一个文件的同一种权限唯一”，避免重复授权，更符合多对多关联的设计规范，同时节省不必要的自增ID字段。优化后的group_files结构如下：

group_files {
    int group_id PK,FK
    int file_id PK,FK
    int permission_id PK,FK
}

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