浅谈考试系统中题目表的结构设计
孙泽辉

这张题目表是考试系统的关键部分,题目表结构设计的好坏直接影响整个系统的稳定运行。本文参考《SQL反模式》一书,经对比各模式优劣后选择使用反向关联模式。

需求介绍

考试系统中的题目信息现在需要存储,题目目前有两种类型:选择题和填空题,这其中有相同的字段,包括(题干,题号,解析,该题分值…)剩下的都是不同的字段,选择题额外有(选项内容,选项编号,该选项是否是答案),填空题额外有(填空位置、答案内容、该空分值)。

下面介绍三个模式,最终我采用了第三个模式。

题目表一个普通字段代表外键

第一直觉就是把公共字段抽出来单独一张表,另外将两个表分开存储,然后和主表关联到一起。仅仅存储题目编号,而不关心题目类型。

此时你若是想给题目表添加一个关联到两张表(选择题、填空题)上,一个外键对应两个表主键?

1
2
3
4
5
CREATE TABLE Question (
# ... 公共字段
FOREIGN KEY (q_entity_id)
REFERENCES Choice(q_id) OR FillBlank(q_id)
)

上面写法是不对的,因为SQL不支持一个字段指向多张表的主键。

怎么办,只能把外键给关了,手动维护一个由q_type、q_entity_id的题目表,如这样:

1
2
3
4
5
CREATE TABLE Question (
# ... 公共字段
q_type VARCHAR(20), -- "choice" or "FillBlank"
q_entity_id BIGINT UNSIGNED NOT NULL
)

查询时只需根据q_type来区分题目类型,然后去对应表查询该题目的信息。

为了逃避外键报错,同时系统也失去了外键所带来的外键约束,因而就可能出现明明q_type是选择题时,q_entity_id是填空题的id,因此,没有任何保障数据完整性的手段来确保q_entity_id一定在q_type所对应的表中出现。

同样的,也没有原数据保证q_type中的值一定对应着数据库的某张表,该字段内容是任意的,而代码只能是死的。

题目表两个外键

那如果两个外键分别指向两个表不就好了吗,干嘛非要写在一个字段里?

1
2
3
4
5
6
CREATE TABLE Question (
# ... 公共字段
q_type VARCHAR(20), -- "choice" or "FillBlank"
FOREIGN KEY (choice_id) REFERENCES Choice(q_id),
FOREIGN KEY (fillblank_id) REFERENCES FillBlank(q_id)
)

不得不说,这种方法可以保证关系完整性,如果Choice.id不存在于Choice表,会报错。

对于查询,因为两个字段都在左侧,用两次LEFT JOIN会出现另一个外键字段是NULL,程序还要处理。

同样的仍没有解决q_type问题。

将所有类型字段统统写在题目表上,万一以后添加了其他题目类型,岂不是要改动题目表,为新添加的类型添加外键字段?

可维护性很差。

反向引用

当看清楚问题的根源时,解决方案变得异常简单。

题目表引用多张题目类型表,既然SQL无法一张表引用多张表,那便多张表引用一张表即可。

ChoiceFillBlank都添加指向题目表的外键,像这样

这个方案移除了对q_type的依赖,现在数据也保证了关系完整性。添加新题目类型时,只需要添加一个指向题目表的题目类型表即可。

查询所有题目时:

1
2
3
4
SELECT * FROM `Question` as `Q`
LEFT JOIN `Q`.id = `Choice`.`q_id`
LEFT JOIN `Q`.id = `FillBlank`.`q_id`
WHERE `Q`.id = 1;

但是这样也是有缺陷的,查询Q.id时选择题时,会把不该列出来的填空题字段也列出来。

《SQL反模式》一书中,最后给出了 共用超级表 的模式,应该可以解决这个问题!

照我的理解是,原本有(题目表、选择题表、填空题表)全部用一张超级表把他们串起来,超级表只需要有个共用的id即可。

没试,不过我有ORM,上面的已经够用了。

总结

《没有银弹》

其实有些ORM是支持多态关联的,例如

多态关联 | Sequelize 中文文档 | Sequelize 中文网

Eloquent: Relationships - Laravel - The PHP Framework For Web Artisans

我用的Typeorm,不支持,尽管有民间大神实现了多态关联,由于一些查询API不太完善,不采用。

  • Post title:浅谈考试系统中题目表的结构设计
  • Post author:孙泽辉
  • Create time:2022-08-06 19:50:55
  • Post link:https://hui.zone/post/8fc1d1d.html
  • Copyright Notice:All articles in this blog are licensed under BY-NC-SA unless stating additionally.