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

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

需求介绍

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

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

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

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

此时你若是想给题目表添加一个关联到两张表（选择题、填空题）上，一个外键对应两个表主键？

CREATE TABLE Question (
  # ... 公共字段
  FOREIGN KEY (q_entity_id)
  	REFERENCES Choice(q_id) OR FillBlank(q_id) 
)

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

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

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中的值一定对应着数据库的某张表，该字段内容是任意的，而代码只能是死的。

题目表两个外键

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

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无法一张表引用多张表，那便多张表引用一张表即可。

给Choice、FillBlank都添加指向题目表的外键，像这样

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

查询所有题目时：

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不太完善，不采用。