PostgreSQL中自定义字段、选项及选中项的存储方案咨询

首先明确核心需求的歧义点：你提到“每个自定义字段仅能有一个选中项，但可关联多个Task”，这里需要区分两种业务场景，不同场景对应不同的表设计方案：

场景1：自定义字段全局仅一个选中项（所有关联该字段的Task共用这个选中项） #

你的现有方案是可行的，但需要补充约束保证数据一致性：

• 两张表的关联关系是合理的：custom_field_options.custom_field_id标记选项所属的自定义字段，custom_field.selected_option_id标记该字段的选中项，这是双向关联而非冗余设计。
• 必须添加约束，确保custom_field.selected_option_id对应的选项确实属于当前字段，可以用PostgreSQL的CHECK约束实现：

  -- PostgreSQL 12+支持的CHECK约束示例
  ALTER TABLE custom_field
  ADD CONSTRAINT chk_selected_option_belongs_to_field
  CHECK (
    selected_option_id IS NULL OR
    EXISTS (
      SELECT 1 FROM custom_field_options
      WHERE id = selected_option_id AND custom_field_id = custom_field.id
    )
  );
  

• 另外，由于自定义字段与Task是多对多关系，还需要新增中间表task_custom_fields来关联两者：

  CREATE TABLE task_custom_fields (
    task_id INT REFERENCES tasks(id),
    custom_field_id INT REFERENCES custom_field(id),
    PRIMARY KEY (task_id, custom_field_id)
  );
  

场景2：每个Task对每个自定义字段有独立选中项（更符合常见业务场景） #

你的现有方案不可行，因为custom_field.selected_option_id是全局配置，无法区分不同Task的选中项。此时需要调整表结构，新增关联表存储任务的自定义字段选中值：

1. custom_fields：存储自定义字段定义

   CREATE TABLE custom_fields (
     id SERIAL PRIMARY KEY,
     name VARCHAR(255) NOT NULL UNIQUE
   );
   

2. custom_field_options：存储每个字段的可选选项

   CREATE TABLE custom_field_options (
     id SERIAL PRIMARY KEY,
     label VARCHAR(255) NOT NULL,
     custom_field_id INT NOT NULL REFERENCES custom_fields(id),
     UNIQUE (custom_field_id, label) -- 避免同字段下出现重复选项
   );
   

3. task_custom_field_values：存储每个Task对应每个自定义字段的选中项

   CREATE TABLE task_custom_field_values (
     id SERIAL PRIMARY KEY,
     task_id INT NOT NULL REFERENCES tasks(id),
     custom_field_id INT NOT NULL REFERENCES custom_fields(id),
     selected_option_id INT NOT NULL REFERENCES custom_field_options(id),
     UNIQUE (task_id, custom_field_id), -- 确保每个任务对每个字段仅一个选中项
     CHECK (
       EXISTS (
         SELECT 1 FROM custom_field_options
         WHERE id = selected_option_id AND custom_field_id = task_custom_field_values.custom_field_id
       )
     ) -- 确保选中项属于当前自定义字段
   );
   

总结 #

• 若需求是字段全局共享一个选中项：现有方案可行，补充约束和多对多中间表即可。
• 若需求是每个任务对字段有独立选中项：必须调整为新增任务-字段值关联表的方案，这是更灵活、适配多数业务场景的设计。

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