如何正确建模Workflows、Steps与Fields的互斥一对多关系？

1. 唯一性要求的实现 #

不建议将name移至关联表，因为name是Field本身的属性，而非关联关系的属性，放在Field表更符合实体属性的归属逻辑。

如果采用第一种方案（Field表含两个外键列），可以通过复合唯一索引实现父级范围内的名称唯一性：

• 创建唯一索引：UNIQUE(workflow_id, name)，确保同一Workflow下的Field名称不重复；
• 创建唯一索引：UNIQUE(step_id, name)，确保同一Step下的Field名称不重复；
• 同时添加检查约束（如CHECK ((workflow_id IS NOT NULL AND step_id IS NULL) OR (workflow_id IS NULL AND step_id IS NOT NULL))），强制Field只能归属其中一个父实体。

如果坚持用关联表方案，要实现父级范围内的名称唯一，需要在关联表中额外存储name并创建复合唯一约束（如WorkflowFields表的UNIQUE(workflow_id, name)），但这会导致name冗余，违反范式，不推荐。

2. 级联删除的实现 #

如果采用第一种方案，直接在外键上配置ON DELETE CASCADE即可实现级联删除：

• step_id外键设置ON DELETE CASCADE，删除Step时自动删除关联的Field；
• workflow_id外键设置ON DELETE CASCADE，删除Workflow时自动删除关联的Field。

如果采用关联表方案，仅靠外键无法直接删除Field（关联表的外键指向Field，级联删除只会删除关联表记录），此时有两种选择：

• 数据库触发器：在WorkflowFields、StepFields表上创建删除触发器，当关联记录被删除时自动删除对应的Field；同时在Workflows、Steps表上创建删除触发器，先删除关联表记录再删除Field；
• 应用层处理：删除Workflow/Step时，先查询关联的FieldID，再批量删除Field，最后删除关联表记录。但应用层处理存在一致性风险，不如触发器可靠。

3. 更优方案推荐 #

推荐使用改进版的第一种方案，解决原方案的扩展性问题：
在Field表中添加一个parent_type鉴别器列（如VARCHAR(20)，取值为'WORKFLOW'或'STEP'），配合两个外键列，同时添加检查约束：

Fields
- id
- name
- parent_type (NOT NULL, CHECK(parent_type IN ('WORKFLOW', 'STEP')))
- step_id (FK to steps, NULLABLE)
- workflow_id (FK to workflows, NULLABLE)
-- 检查约束：parent_type与外键列匹配，且仅一个外键非空
CHECK (
  (parent_type = 'WORKFLOW' AND workflow_id IS NOT NULL AND step_id IS NULL)
  OR
  (parent_type = 'STEP' AND step_id IS NOT NULL AND workflow_id IS NULL)
)

这种方案的优势：

• 保留了原方案的级联删除和唯一性约束能力；
• 通过parent_type明确标识父实体类型，避免空值带来的歧义；
• 未来新增父实体时，只需添加新的外键列、更新parent_type的检查约束取值，扩展性优于原方案。

如果追求极致的范式和扩展性，也可以考虑单表继承+外键约束：创建一个Parents表作为Workflows和Steps的父表，Workflows和Steps通过一对一关联指向Parents，然后Field表只需要一个parent_id外键指向Parents，再加parent_type列。但这种方案会增加表结构复杂度，适合父实体类型较多的场景，当前场景下改进版第一种方案足够简洁实用。

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