关系型数据库中如何为固定属性存储多文档引用？

我正在设计一个关系型数据库，其中的实体拥有一组固定属性，每个属性可能需要多个来自文档的“引用”或佐证其值的参考资料。例如，Person实体包含name、birthPlace和birthDate属性，我希望为每个属性存储一组确认其值的文档。

现有方案：为每个属性单独设置JSONB列 #

我考虑为每个属性使用JSON列，示例SQL如下：

CREATE TABLE person (
    id SERIAL PRIMARY KEY,
    name TEXT,
    name_quotes JSONB,
    birth_place TEXT,
    birth_place_quotes JSONB,
    birth_date DATE,
    birth_date_quotes JSONB
);

其中name_quotes、birth_place_quotes和birth_date_quotes为包含如下对象的数组：

[
    { "documentReference": "doc1.pdf", "quotedValue": "John" },
    { "documentReference": "doc2.pdf", "quotedValue": "Johnny" }
]

排除EAV模型的理由 #

我曾考虑使用EAV（Entity-Attribute-Value）模型来建立属性与引用的关联，但并不倾向于该方案，原因如下：

• 属性数量固定，并非动态可变
• 部分属性并非文本类型（例如birthDate）

核心问题 #

1. 在关系型数据库设计中，为每个属性单独存储引用到JSON列是否合理？
2. 是否存在更通用的方案，可避免为每个属性创建JSON列，同时支持不同数据类型？

我正在寻求为关系型数据库中固定属性存储多文档引用的最佳实践或设计模式。

问题解答 #

1. 为每个属性单独设置JSONB列是否合理？ #

这种方案合理但存在明显局限性：

• 优点：
  • 结构直观，和实体属性一一对应，查询特定属性的引用时无需复杂关联，上手成本低
  • JSONB支持索引，若需按documentReference或quotedValue查询，可创建GIN或BTREE索引满足需求
• 缺点：
  • 扩展性差：新增属性时必须修改表结构添加对应xxx_quotes列，属性越多维护成本越高
  • 数据冗余：每个JSONB列都存储重复的结构（documentReference和quotedValue），不符合数据库范式
  • 类型校验弱：JSONB中的quotedValue无法强制匹配原属性的数据类型（比如birth_date的引用值可能存为字符串而非日期），易出现数据不一致

2. 更通用的替代方案 #

推荐采用关联表+类型区分字段的方案，既规避EAV模型的灵活性弊端，又能统一管理所有属性的引用，同时支持不同数据类型：

方案设计

1. 保留原person主表，仅存储实体核心属性：

CREATE TABLE person (
    id SERIAL PRIMARY KEY,
    name TEXT,
    birth_place TEXT,
    birth_date DATE
);

2. 创建独立的person_attribute_references关联表，统一存储所有属性的引用：

CREATE TABLE person_attribute_references (
    id SERIAL PRIMARY KEY,
    person_id INT REFERENCES person(id) ON DELETE CASCADE,
    attribute_name VARCHAR(50) NOT NULL CHECK (attribute_name IN ('name', 'birth_place', 'birth_date')), -- 固定属性枚举，限制动态属性
    document_reference VARCHAR(255) NOT NULL,
    -- 按属性类型设置对应存储字段
    quoted_text TEXT,
    quoted_date DATE,
    -- 约束确保每个引用仅对应一个类型的字段有值
    CHECK (
        CASE attribute_name
            WHEN 'name' THEN quoted_text IS NOT NULL AND quoted_date IS NULL
            WHEN 'birth_place' THEN quoted_text IS NOT NULL AND quoted_date IS NULL
            WHEN 'birth_date' THEN quoted_date IS NOT NULL AND quoted_text IS NULL
        END
    )
);

方案优势

• 统一管理：无需为每个属性新增列，新增属性时仅需修改CHECK约束的枚举值，按需添加对应类型字段，维护更高效
• 强类型校验：通过CHECK约束确保每个属性的引用值匹配正确数据类型，避免类型混乱
• 符合范式：消除数据冗余，便于统计、关联查询（例如查询所有引用某文档的实体属性）
• 查询灵活：可便捷查询单个实体的所有属性引用，或某一属性的全部引用记录

额外优化建议

• 为person_id和attribute_name创建复合索引，提升关联查询性能
• 若需支持更多属性类型（如数值型），可新增quoted_number字段并更新CHECK约束

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