调查数据库设计

Question

我需要创建一个调查表，其中答案将存储在数据库中。我想知道在数据库中实现这个的最佳方式是什么，特别是需要哪些表格。该调查包含不同类型的问题。例如：用于评论的文本字段、多项选择题以及可能包含多个答案的问题（即“全选”）。

我想到了两种可能的解决方案：

1. 创建一个巨大的表，其中包含每个调查提交的答案。每列都对应于来自调查的一个答案。例如 SurveyID，Answer1，Answer2，Answer3

   我认为这不是最好的方法，因为在此调查中有很多问题，如果调查发生变化，则似乎不太灵活。

2. 我想到的另一件事是创建一个Question表和一个Answer表。 问题表将包含调查的所有问题。 答案表将包含调查的单个答案，每行链接到一个问题。

   以下是简单示例：

   tblSurvey: SurveyID

   tblQuestion: QuestionID，SurveyID，QuestionType，Question

   tblAnswer: AnswerID，UserID，QuestionID，Answer

   tblUser: UserID，UserName

   我的问题是可能会有大量答案，这将使Answer表格非常庞大。 当涉及到性能时，我不确定这是否太好了。

我希望得到任何想法和建议。

- Michael

---

“相当大”是多少？请给我们一个估计，我们是在谈论百万还是千亿？ - Jorge Córdoba

SQL服务器实际上是为处理大量数据而设计的。你不应该在使用你所提到的方案时遇到太多麻烦。 - Chris

Answer 1

我认为你的第二个模型很不错，不过你可以看一下更复杂的模型，它会存储问题和预先制作的答案(提供的答案)，并允许在不同的调查中重复使用它们。

- 一个调查可以有很多问题；一个问题可以在很多调查中被(重新)使用。
- 一个(预先制作的)答案可以用于很多问题。一个问题可以提供很多答案。一个问题在不同的调查中可以提供不同的答案。一个答案也可以在不同的调查中提供给不同的问题。如果一个人选择其他，则有一个默认的“其他”答案，她的答案将被记录到Answer.OtherText中。
- 一个人可以参加很多调查，但在一个调查中只能回答特定问题一次。

- Damir Sudarevic

---

你用了什么工具来制作数据库模式呢？ - AndHeiberg

您也可以使用 https://www.draw.io/ 这个工具。它是免费的，不需要注册，而且易于使用。 - usr4896260

为什么我们需要Survey_Question_Answer和Answer？仅仅Answer不就够了吗？ - Abubakar Ahmad

Abubakar Ahmad，Survey_Question_Answer 用于让用户从多个预设答案中选择一个答案来回答一个问题。 - Henry Gabriel González Montejo

我认为“Answer”就足够了，“Survey_question_answer”是多余的。 - Batman

Answer 2

下面显示了我的设计。

最新的创建脚本在https://gist.github.com/durrantm/1e618164fd4acf91e372

该脚本和mysql workbench.mwb文件也可在以下位置找到：
https://github.com/durrantm/survey

- Michael Durrant

---

嗨，我喜欢你的设计。请问这些表格有没有数据样本（转储）？非常感谢。 - Emeka Mbah

你好！首先感谢你的工作，这太棒了！你考虑过在你的模板中加入层次结构吗？用户通常会提供关于他们的领导者的信息，而这些领导者又有关于他们的领导者的信息，依此类推。而且用户在不同的部门（人力资源，生产）中工作，这些部门也可以有一个层次结构。因此，在报告期间，经常需要区分这些组织级别。 - ruedi

@michael：非常有帮助。 你有Java使用Spring的参考资料或GitHub链接吗？ - Sagar Panda

我仍在努力找出option_groups和option_choices之间的区别以及使用情况。 - PHPnoob

@PHPnoob 我认为，正如其名称所示，它只是简单地分组选项。因此，如果您可以在1到5之间进行评分，那么如果我理解正确的话，option_groups应该允许您做到这一点。 - Stefan Falk

很有趣，你将答案表按数据类型分成了列。我也这样做过，但我经常想知道是否有更好的方法，因为这会给每个答案带来相当大的开销，并且需要SQL来寻址正确的字段。也许按数据类型分表会更高效。只是随口一说。 - artfulrobot

Answer 3

毫无疑问，选项#2是最好的选择。此外，我认为当前架构中可能存在一个疏漏，您可能需要另一张表：

+-----------+
| tblSurvey |
|-----------|
| SurveyId  |
+-----------+

+--------------+
| tblQuestion  |
|--------------|
| QuestionID   |
| SurveyID     |
| QuestionType |
| Question     |
+--------------+

+--------------+
| tblAnswer    |
|--------------|
| AnswerID     |
| QuestionID   |
| Answer       |
+--------------+

+------------------+
| tblUsersAnswer   |
|------------------|
| UserAnswerID     |
| AnswerID         |
| UserID           |
| Response         |
+------------------+

+-----------+
| tblUser   |
|-----------|
| UserID    |
| UserName  |
+-----------+

每个问题可能会有一定数量的答案供用户选择，然后实际响应将在另一个表中被跟踪。

数据库旨在存储大量数据，大多数都能很好地扩展。现在没有必要再使用较低的标准化形式为了节省空间。

- tplaner

---

嗨，我有一个问题。调查ID在答案表中也应该存在，或者至少应该有与调查版本时间匹配的时间戳吗？如果您在原始调查中插入了一个问题，则问题ID将更改，答案将变得无法识别。或者如果这是多余的，您能解释一下吗？ - Shubham

Answer 4

通常情况下，基于用户可能更改的内容（例如向调查问卷添加问题）修改模式应被视为相当糟糕的。在处理大量数据时可能存在适用情况，但在深入之前要了解你所涉及的内容。每个调查问卷仅具有一个“响应”表意味着添加或删除问题可能非常昂贵，并且很难以一种与问题无关的方式进行分析。

我认为你的第二种方法是最好的，但如果你确定会遇到许多规模问题，那么过去对我有效的一种混合方法是：

1. 创建详细的响应表以存储每个问题的响应，就像你在第2点中所描述的那样。这些数据通常不会直接从您的应用程序查询，而是用于生成报告表的摘要数据。您还可能需要实现某种形式的归档或清除此数据。 2. 如果必要，还可以创建第1步中的响应表。每当用户想要查看简单的结果表时，可以使用它。 3. 对于任何需要进行报告目的的分析，请安排作业根据第1步中的数据创建其他摘要数据。

这绝对需要更多的工作来实现，因此除非您确定此表将遇到大规模问题，否则我真的不建议这样做。

- Ryan Brunner

Answer 5

第二个看起来没问题。

对于只有4列的表，即使有好几百万行也不应该是问题，当然这取决于你使用的数据库是什么。如果是像SQL Server这样的数据库，那就没有问题。

您可能想在tblAnswer表的QuestionID字段上创建索引。

当然，您需要指定您使用的数据库以及估计的数据量。

- kevchadders

Answer 6

第二种方法是最好的。

如果您想进一步规范化，可以为问题类型创建一个表

要做的简单事情包括：

• 将数据库和日志放在自己的磁盘上，而不是全部放在默认的C盘上
• 创建所需大小的数据库，以便在数据库增长时不会出现暂停

我们在SQL Server表中有10多万行的日志表。

- Shiraz Bhaiji

Answer 7

你可以选择将整个表单存储为JSON字符串。

不确定你的需求，但这种方法在某些情况下是可行的。

- mriiiron

Answer 8

对于一个简单的调查来说，看起来相当完整。不要忘记添加一个“开放值”表格，客户可以通过文本框提供他的意见。将该表与您的答案外键链接，并为所有关系列放置索引以提高性能。

- Ben Fransen

---

我不能将注释放在答案表中的原因是什么？ - Michael

Answer 9

第二个选项是正确的。在没有检测到性能问题之前，请使用正确的设计方案。大多数关系型数据库管理系统不会对狭长但非常长的表产生问题。

- Larry Lustig

Answer 10

拥有一个大的Answer表本身并不是问题。只要索引和约束定义得很好，你应该没问题。你的第二个模式看起来很不错。

- Dave Swersky