存在互联多对多关联的问卷系统数据库该如何规范化？

问题根源 #

你当前的设计不符合第三范式（3NF），核心问题是respondent_answer表的两个外键没有绑定统一的问卷维度，既存在隐式的数据冗余，也没有约束保障数据一致性。 #

规范化调整方案 #

1 调整中间关联表的主键设计

去掉中间表无意义的自增ID，改用复合主键明确绑定问卷维度：

-- 基础表保持不变
table questionnaire - id (主键)
table respondent - id (主键), name
table question - id (主键), text

-- 问卷-受访者关联表：复合主键保证同一份问卷下同一个受访者只有一条关联记录
table questionnaire_respondent - questionnaire_id, respondent_id, PRIMARY KEY (questionnaire_id, respondent_id)
-- 问卷-问题关联表：复合主键保证同一份问卷下同一个问题只有一条关联记录
table questionnaire_question - questionnaire_id, question_id, PRIMARY KEY (questionnaire_id, question_id)

2 重构答案表的约束逻辑

答案表新增questionnaire_id字段，通过双外键约束强制回答关联的受访者、问题都属于同一份问卷：

table respondent_answer 
- id (可选，可保留自增ID作为业务主键)
- questionnaire_id (非空)
- respondent_id (非空)
- question_id (非空)
- answer_text (非空)
-- 复合唯一约束/主键：同一个受访者在同一份问卷下的同一个问题只能有一个答案
- PRIMARY KEY (questionnaire_id, respondent_id, question_id)
-- 外键约束1：强制关联的受访者属于当前问卷
- FOREIGN KEY (questionnaire_id, respondent_id) REFERENCES questionnaire_respondent(questionnaire_id, respondent_id)
-- 外键约束2：强制关联的问题属于当前问卷
- FOREIGN KEY (questionnaire_id, question_id) REFERENCES questionnaire_question(questionnaire_id, question_id)

调整后收益 #

• 消除冗余：所有非主属性完全依赖主键，没有传递依赖，符合3NF要求，同时查询问卷维度的回答数据不需要多表关联推导问卷ID，查询效率更高
• 彻底避免脏数据：数据库原生外键约束直接拦截跨问卷的无效回答插入，不需要额外开发触发器，从架构层面解决数据一致性问题
• 扩展性更好：后续如果要增加问卷维度的回答统计、权限控制等逻辑，可直接基于questionnaire_id字段拓展

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