共享外键场景下如何避免Inputs关联表重复存储条目

你当前遇到的重复存储问题，本质是把多对多关联场景错误设计成了一对多外键关联。
现有设计在Inputs表加outputsID外键的逻辑，默认前提是「一个输入项只能归属于一个输出项」，但实际业务规则是：一个输入项可以被多个输出项复用，一个输出项也可以绑定多个输入项。这种双向多对多的关系，不能通过在任意一张主表加外键实现，否则必然出现重复存储输入属性的问题。

按照数据库范式拆分三张表即可，从结构层面杜绝重复数据：

1. 抽离独立的输入定义表 #

把原Inputs表中存储输入自身属性的部分（也就是name字段，后续如果新增输入类型、校验规则、默认值这类输入本身的属性，也统一存在这张表），抽成独立的InputDef输入定义表，同属性的输入在这张表只存一次，从根源避免重复：

CREATE TABLE InputDef (
  ID INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL UNIQUE -- 加唯一约束，从数据库层面禁止重复创建同名输入
);

对应你给出的示例，这张表仅需存储3条无重复的记录：

• ID=0，name=A
• ID=1，name=B
• ID=2，name=C

2. 保留原有Outputs表结构 #

Outputs表不需要做任何调整，仅存储输出项自身的属性即可：

CREATE TABLE Outputs (
  ID INT PRIMARY KEY,
  value VARCHAR(255) NOT NULL
);

表内数据和你原有数据完全一致：

• ID=0，value=x
• ID=1，value=y
• ID=2，value=z

3. 新增多对多中间关联表 #

新建一张无业务属性的关联表OutputInputRel，仅用来维护输出项和输入项的绑定关系，不存储任何输入、输出自身的属性：

CREATE TABLE OutputInputRel (
  ID INT PRIMARY KEY,
  outputsID INT NOT NULL,
  inputDefID INT NOT NULL,
  -- 联合唯一约束，避免同一个输出重复绑定同一个输入
  UNIQUE KEY uk_output_input (outputsID, inputDefID),
  -- 外键约束保证关联数据合法性，避免关联不存在的输出/输入
  FOREIGN KEY (outputsID) REFERENCES Outputs(ID),
  FOREIGN KEY (inputDefID) REFERENCES InputDef(ID)
);

对应你原有的绑定关系，这张表仅需存储5条关联记录，完全不会重复存储输入的name属性：

• ID=0，outputsID=0，inputDefID=0（输出x关联输入A）
• ID=1，outputsID=1，inputDefID=1（输出y关联输入B）
• ID=2，outputsID=1，inputDefID=2（输出y关联输入C）
• ID=3，outputsID=2，inputDefID=1（输出z关联输入B）
• ID=4，outputsID=2，inputDefID=2（输出z关联输入C）

• 彻底消除冗余存储：相同属性的输入仅在InputDef表存1次，不管被多少输出项复用，都不需要重复存储name等输入属性
• 维护成本极低：后续如果需要修改某个输入的属性（比如把name=B调整为name=新B），仅需修改InputDef表中的1条记录，不需要批量更新所有关联该输入的重复行
• 数据一致性有保障：通过外键、唯一约束从数据库层面拦截脏数据，不会出现同名输入拼写不一致、关联不存在的记录这类问题

如果需要查询某个输出项绑定的所有输入，通过两张左连接即可实现：

SELECT 
  o.ID AS output_id,
  o.value AS output_value,
  i.name AS input_name
FROM Outputs o
LEFT JOIN OutputInputRel rel ON o.ID = rel.outputsID
LEFT JOIN InputDef i ON rel.inputDefID = i.ID
WHERE o.ID = 2;

上述查询会返回输出z绑定的B、C两个输入，完全匹配业务需求。

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