语言学习APP数据库设计：复合主键与单主键选型咨询

问题背景 #

我正在为一款语言学习APP设计数据库，需创建按章节（如语法测试、文本测试等）、主题和难度级别划分的测试。现有数据库表结构如下：

USER
id (pk, autoincrement)
name
last_name
email (unique)
level_id
password

LEVEL
id (pk)
description

SECTION_TYPE
id (pk, no autoinc [to avoid creating a field for level_num])
section_name

TOPIC
?id (pk)
  section_id (fk)
  level_id (fk)
name

TEST
?id (pk)
  topic_id (fk)
  num

QUESTION
?id (pk)
  test_id (fk)
  num
text
has_multi_ans

ANSWER
?id (pk)
  question_id (fk)
  num
text
is_correct

我了解到复合索引可提升搜索效率，而按级别和主题搜索测试是高频操作，且我的场景中该复合字段组合具有唯一性。我考虑将带缩进的字段设为复合主键，替代单主键id，但这样就必须创建复合外键，虽几乎可省去TEST表，但会导致结构混乱。参考过PostgreSQL中外键关联复合主键的案例，但该案例关联字段更多，单主键更合理，与我的情况不同，请问该如何处理？

优化方案 #

1. 保留单主键，规避复合外键的结构复杂度 #

单自增主键id的核心优势是简化表间关联逻辑，后续QUESTION、ANSWER等表的外键引用会更简洁，不会因多层复合外键导致维护成本陡增，彻底避免你担心的“结构混乱”问题。

2. 针对高频查询创建复合索引+唯一约束 #

既然按级别和主题搜索测试是高频操作，无需替换主键，直接通过复合索引覆盖该场景即可：

• 方案一：利用关联表字段创建函数索引（PostgreSQL支持）

  CREATE INDEX idx_test_topic_level ON TEST (topic_id, (SELECT level_id FROM TOPIC WHERE TOPIC.id = TEST.topic_id));
  

• 方案二：冗余level_id字段提升查询效率（推荐）
  给TEST表新增level_id字段并关联LEVEL表，然后创建带唯一约束的复合索引（利用你提到的组合唯一性）：

  -- 新增字段
  ALTER TABLE TEST ADD COLUMN level_id INT REFERENCES LEVEL(id);
  -- 创建唯一复合索引，保证同一主题、级别下测试序号唯一
  CREATE UNIQUE INDEX idx_test_topic_level_num ON TEST (topic_id, level_id, num);
  

3. 优化TOPIC表的约束与索引 #

TOPIC表中section_id+level_id+name应保证唯一（同一章节、级别下不能有重复主题），添加唯一约束：

ALTER TABLE TOPIC ADD CONSTRAINT uq_topic_section_level_name UNIQUE (section_id, level_id, name);

同时创建复合索引支持章节+级别的主题查询：

CREATE INDEX idx_topic_section_level ON TOPIC (section_id, level_id);

4. 可选：合并TEST表简化结构 #

如果TEST仅作为TOPIC下的序号标识（num），可将其逻辑合并到QUESTION表中，用topic_id+test_num+num区分不同测试的题目。此时建议保留QUESTION的单主键id，同时添加复合唯一约束保证数据一致性：

ALTER TABLE QUESTION ADD CONSTRAINT uq_question_topic_test_num UNIQUE (topic_id, test_num, num);

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