数据库设计：单表与多表存储多类型资源标识符哪个更优？

我对数据库设计原则了解不足，现在要创建一个包含约150种不同类型resource表的数据库。每种resource可以拥有多个标识符，标识符都由字符串类型的value和定义该值的system URL组成，示例如下：

"system": "urn:oid:0.1.2.3.4.5.6.7"
"value": "654321"

目前有两种标识符存储方案：

方案一：为每种resource创建单独的标识符表 #

比如针对Patient表，创建patientIdentifier表：
Patient表结构：

create table if not exists patient 
(
    id text primary key,
    versionid int not null,
    updatedat timestamp with time zone default timezone('utc'::text, now()) not null,
    resource jsonb not null,
);

patientIdentifier表结构：

create table if not exists patientIdentifier 
(
    id text primary key,
    resourceId text not null,
    value text not null,
    system text not null,
);

方案二：单张统一标识符表存储所有resource的标识符 #

create table if not exists resourceIdentifier 
(
    id text primary key,
    resourceType text not null,
    resourceId text not null,
    value text not null,
    system text not null,
);

哪种方案更受青睐？ #

在数据库设计中，方案二更适合你的场景，原因如下：

• 减少表数量，降低维护成本
  150种resource对应150个单独的标识符表，会让数据库表数量翻倍到300+，后续的Schema维护、索引管理、备份恢复都会变得异常繁琐。单表方案只需要维护一张标识符表，操作简洁得多。

• 统一查询逻辑，简化开发
  跨多种resource查询标识符时，单表方案只需要一次查询；分表方案则要联合查询几十甚至上百张表，SQL复杂度飙升，性能也会受影响。开发时也不需要为每种resource编写单独的标识符增删改查逻辑，代码复用性更高。

• 灵活扩展新resource类型
  新增一种resource时，单表方案不需要创建新表，只需要在业务逻辑中指定对应的resourceType即可；分表方案必须同步创建新的标识符表，增加了上线流程的复杂度。

• 索引优化更高效
  单表可以针对(resourceType, resourceId)、(system, value)等组合创建联合索引，满足不同查询场景需求；分表则要为每一张标识符表单独创建相同索引，重复工作多，还占用更多存储资源。

当然，单表方案需要注意几个细节：

• 给resourceType字段添加约束（比如枚举或检查约束），避免非法值存入；
• 针对高频查询场景优化索引，比如经常通过system和value查找resource，就创建(system, value, resourceType, resourceId)的联合索引；
• 若后续单表数据量过大（比如亿级以上），可再考虑按resourceType分表或分区，但这是后期优化步骤，初期单表方案完全够用。

方案一的优势仅存在于极端场景：某一种resource的标识符量极大，且查询只针对该类型。但对你150种resource的场景来说，这种极端收益远抵不过维护和开发的成本。

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