在线测验应用填空题的SQL Schema设计方案选型咨询

两种现有方案的优劣势 #

方案一：继承策略（JPA继承映射） #

• 优势：
  • 模型结构贴合面向对象设计，通过Part父类统一管理不同类型的文本片段/空格，代码复用性强，逻辑清晰
  • 关联关系简单，BlankExercise直接关联Part列表，空格的顺序由列表顺序自然维护，无需额外处理位置标记
• 劣势：
  • 数据库层面若采用单表继承，会存在大量冗余字段（比如BlankOptionsPart的选项字段对其他子类无用）；若采用Joined继承，查询时需多表关联，数据量增大后性能会明显下降
  • 扩展性有限，新增填空题类型需新增子类，对现有代码侵入性强，后续维护成本高

方案二：多表拆分设计 #

• 优势：
  • 数据存储紧凑，每个表仅存储对应类型的数据，无冗余字段，空间利用率高
  • 针对不同类型的空格可单独优化查询逻辑，性能可控性强
  • 通过blank_index明确标记空格位置，逻辑直观，便于前端渲染和后端答案校验
• 劣势：
  • 表结构过多，组装完整题目时需多表关联查询，开发复杂度高，比如要拼接题目内容，需关联exercise_texts和各类空格表后按blank_index排序
  • 新增题型时需新增对应表和关联逻辑，数据库结构变更繁琐，维护成本高

更推荐的折中方案：单表+JSON字段存储可变内容 #

结合两种方案的优缺点，推荐一种兼顾灵活性、开发效率和存储效率的设计：

核心表结构 #

1. exercises表：存储测验基础信息

   字段名	类型	说明
   id	BIGINT	主键
   title	VARCHAR(255)	测验标题
   create_time	DATETIME	创建时间
   other_fields	...	其他业务所需字段

2. exercise_segments表：存储题目拆分后的片段（普通文本/空格）

   字段名	类型	说明
   id	BIGINT	主键
   exercise_id	BIGINT	关联exercises表id
   segment_type	TINYINT	片段类型：1=普通文本，2=填空（输入文本），3=填空（选择选项）
   content	VARCHAR(255)	普通文本内容/填空提示（可选）
   options	JSON	当segment_type=3时，存储选项列表，示例：["genes","cells","chromosomes"]
   sort_order	INT	片段排序号，用于组装完整题目
   correct_answer	VARCHAR(255)	正确答案：输入型填空存文本，选型填空存正确选项内容

方案优势 #

• 结构简洁，仅两张核心表，避免多表关联的繁琐逻辑
• 灵活性强，新增题型只需扩展segment_type枚举值，无需新增表或修改实体类结构
• 存储高效，JSON字段按需存储选项，无冗余数据
• 题目组装逻辑简单，只需按sort_order排序查询exercise_segments，即可拼接出完整题目内容

示例数据 #

输入型填空题示例

题目：Chemical energy produced by the ____ is stored in a small molecule called ____
对应exercise_segments数据：

• id:1, exercise_id:1, segment_type:1, content:"Chemical energy produced by the ", sort_order:1, correct_answer:null, options:null
• id:2, exercise_id:1, segment_type:2, content:"", sort_order:2, correct_answer:"mitochondria", options:null
• id:3, exercise_id:1, segment_type:1, content:" is stored in a small molecule called ", sort_order:3, correct_answer:null, options:null
• id:4, exercise_id:1, segment_type:2, content:"", sort_order:4, correct_answer:"ATP", options:null

选型填空题示例

题目：Mitochondria contain their own small (genes/cells/chromosomes)
对应exercise_segments数据：

• id:5, exercise_id:2, segment_type:1, content:"Mitochondria contain their own small (", sort_order:1, correct_answer:null, options:null
• id:6, exercise_id:2, segment_type:3, content:"", sort_order:2, correct_answer:"genes", options:"["genes","cells","chromosomes"]"
• id:7, exercise_id:2, segment_type:1, content:")", sort_order:3, correct_answer:null, options:null

总结 #

• 若项目规模小、后续题型扩展需求低，继承策略（JPA）开发速度快，代码结构清晰，可优先选择
• 若对数据存储效率要求高、项目规模较大，多表拆分方案更适合，但开发复杂度更高
• 推荐的单表+JSON字段方案平衡了灵活性、开发效率和存储效率，适合大多数在线测验应用场景，尤其适合需要后续扩展题型的情况

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