具有不同数据类型答案的调查的 SQL 设计

Question

我正在开展一项在线调查。大多数问题的答案等级为 1-5。如果我们需要向调查添加问题，我会使用一个简单的 Web 表单，该表单会插入到相应的表中，瞧！调查提出了新问题——没有新代码或对数据库结构进行更改。

我们被要求添加可以有不同数据类型答案的调查问题。规范是让调查“可配置”，以便在将来的任何时候，当有人说“我们需要一项新的调查来询问{文本答案问题}、{1-5 问题}、{真假问题} ，{以日期作为答案的问题}”，我们可以在不更改数据库结构的情况下做到这一点。

我试图想出存储这些答案的最佳方法，但我想出的每种方法似乎都有些老套。

有些问题可能有是/否或真/假答案，有些可能有整数答案（“过去一个月您使用过技术支持多少次？”），另一个答案可能有日期、字符串、倍数具有单个值的选择、具有多个值的多项选择等。或者有时，特定的答案值可能会提示一个子问题（“什么令您失望......？”）

简单的解决方案是将每个问题作为一个调查中的一栏、其答案作为调查中的一栏，以及是否将其作为调查中的一栏提问。这对我来说感觉就像是一团乱——这是一张大桌子；不是很“相关”。

集思广益，我能想到的“最佳”方法是为每种答案类型使用不同的表，但这感觉容易受到数据完整性问题的影响。换句话说，我会

CREATE TABLE `Questions` (...);
CREATE TABLE `TrueFalseAnswers` (...);
ALTER TABLE `TrueFalseAnswers`
  ADD CONSTRAINT `TrueFalseAnswers_ibfk_1` FOREIGN KEY (`question_id`)
REFERENCES `Questions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE TABLE `TextAnswers` (...);
ALTER TABLE `TextAnswers`
  ADD CONSTRAINT `TextAnswers_ibfk_1` FOREIGN KEY (`question_id`)
REFERENCES `Questions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

等等。

上述的一个问题是我不能保证对于 DDL 中的任何问题至少存在一个且只有一个答案。

另一种解决方案可能是在问题表中为答案设置一个二进制或字符串列，并将所有答案编码为某种字符串或二进制格式，并将它们存储在答案列中。这为我的每一个问题提供了至少一个且仅有一个答案，但随后我就无法访问 SQL 查询中的聚合功能。我觉得这不是一个非常“相关”的解决方案。

因此，我认为上述想法存在问题。有没有“最好”的方法来解决这个问题？

现在我已经花时间来表达问题和我的想法了，看来我提出的广泛问题是“我想存储任意类型的数据而不进行任何编码......”是这样的吗？绝望？

我正在使用 MySQL，因此我无法访问其他 RDBMS 可能访问的内容。

Answer 1

使用指定答案类型的列，但将答案存储为文本。您的应用程序或前端可以使用answer_type 列来确定向最终用户显示的内容（测试框、单选按钮、日期选择器）以及如何在将其发送回数据库之前对其进行验证。

Answer 2

您想要创建一个与 QuestionType 类相对应的 QuestionType 表。允许用户填写的保留答案为自由格式文本，并由 QuestionType 来确定答案的含义。

因此，如果正确/错误，答案可能是“T”或“F”。

如果是多项选择，答案可以是所选选项的索引。

如果是用户填写的文本框，请保存他们输入的文本。

Answer 3

我提出的是“我想存储任意类型的数据而不进行任何编码......”这是绝望的吗？

是的，几乎是这样。对于您提出的问题，没有“好的”解决方案。 “最好的”正如 Dave Swersky 和 ​​Larry Lustig 所描述的那样：

问题表，存储问题、可能的答案（如果是多项选择）和问题类型

答案表，存储问题的答案（FK 到问题）表），序列化为文本。 Varchar(4000) 或 TEXT 数据类型，除非绝对必要，否则最好是前者。

由您的应用程序逻辑根据为问题指定的类型来确定该值的含义。

Answer 4

您还可以使用 dataType 指定的表，例如整数、日期、字符串等的表。
从那里开始，有 1 个答案表，将问题（表）链接到正确的数据类型表 + 主键。

要概览所有问题及其答案，您可以在其之上创建一个视图，只需将所有数据类型转换为视图中的文本即可。