SQL 哪些 mysql 数据库表和关系将支持带有条件问题的问答调查？

What mysql database tables and relationships would support a Q&A survey with conditional questions?

提问by kender

I'm working on a fairly simple survey system right now. The database schema is going to be simple: a Surveytable, in a one-to-many relation with Questiontable, which is in a one-to-many relation with the Answertable and with the PossibleAnswerstable.

我现在正在研究一个相当简单的调查系统。数据库模式将很简单：一个Survey表，与Question表存在一对多关系，与Answer表和PossibleAnswers表存在一对多关系。

Recently the customer realised she wants the ability to show certain questions only to people who gave one particular answer to some previous question (eg. Do you buy cigarettes?would be followed by What's your favourite cigarette brand?, there's no point of asking the second question to a non-smoker).

最近，客户意识到她希望能够仅向对之前的某个问题给出一个特定答案的人展示某些问题（例如，你买香烟吗？后面会是你最喜欢的香烟品牌？，没有必要问第二个向非吸烟者提问）。

Now I started to wonder what would be the best way to implement this conditionalquestions in terms of my database schema? If question Ahas 2 possible answers: A and B, and question Bshould only appear to a user ifthe answer was A?

现在我开始想知道根据我的数据库模式实现这个条件问题的最佳方法是什么？如果question A有2个可能的答案：A和B，以及question B应该只出现一个用户，如果答案是A？

Edit: What I'm looking for is a way to store those information about requirements in a database. The handling of the data will be probably done on application side, as my SQL skills suck ;)

编辑：我正在寻找的是一种将有关需求的信息存储在数据库中的方法。数据的处理可能会在应用程序端完成，因为我的 SQL 技能很烂；)

回答by Michael Durrant

Survey Database Design

调查数据库设计

Last Update: 5/3/2015
Diagram and SQL files now available at https://github.com/durrantm/survey

最后更新：2015 年 5 月 3 日
图表和 SQL 文件现在可从https://github.com/durrantm/survey 获得

If you use this (top) answer or any element, please add feedback on improvements !!!

如果您使用此（顶部）答案或任何元素，请添加有关改进的反馈！！！

This is a real classic, done by thousands. They always seems 'fairly simple' to start with but to be good it's actually pretty complex. To do this in Rails I would use the model shown in the attached diagram. I'm sure it seems way over complicated for some, but once you've built a few of these, over the years, you realize that most of the design decisions are very classic patterns, best addressed by a dynamic flexible data structure at the outset.
More details below:

这是一个真正的经典，由数千人完成。它们一开始似乎总是“相当简单”，但实际上它非常复杂。要在 Rails 中执行此操作，我将使用附图中显示的模型。我敢肯定，这对某些人来说似乎过于复杂，但是一旦您构建了其中的一些，多年来，您就会意识到大多数设计决策都是非常经典的模式，最好通过动态灵活的数据结构来解决一开始。
更多详情如下：

Table details for key tables

关键表的表详细信息

answers

答案

The answerstable is critical as it captures the actual responses by users. You'll notice that answers links to question_options, not questions. This is intentional.

该答案表是至关重要的，因为它抓住了用户的实际响应。您会注意到答案链接到question_options，而不是questions。这是故意的。

input_types

输入类型

input_typesare the types of questions. Each question can only be of 1 type, e.g. all radio dials, all text field(s), etc. Use additional questions for when there are (say) 5 radio-dials and 1 check box for an "include?" option or some such combination. Label the two questions in the users view as one but internally have two questions, one for the radio-dials, one for the check box. The checkbox will have a group of 1 in this case.

input_types是问题的类型。每个问题只能是 1 种类型，例如所有无线电拨号、所有文本字段等。如果有（例如）5 个无线电拨号和 1 个“包括？”复选框，请使用其他问题。选项或一些这样的组合。将用户视图中的两个问题标记为一个，但内部有两个问题，一个用于无线电拨号，一个用于复选框。在这种情况下，复选框将包含一组 1。

option_groups

option_groups

option_groupsand option_choiceslet you build 'common' groups. One example, in a real estate application there might be the question 'How old is the property?'. The answers might be desired in the ranges: 1-5 6-10 10-25 25-100 100+

option_groups和option_choices 可让您构建“通用”组。例如，在房地产应用程序中，可能会出现“该物业的年限是多少？”的问题。可能需要以下范围的答案：1-5 6-10 10-25 25-100 100+

Then, for example, if there is a question about the adjoining property age, then the survey will want to 'reuse' the above ranges, so that same option_group and options get used.

然后，例如，如果有关于相邻房产年龄的问题，那么调查将希望“重用”上述范围，以便使用相同的 option_group 和 options。

units_of_measure

度量单位

units_of_measureis as it sounds. Whether it's inches, cups, pixels, bricks or whatever, you can define it once here.

units_of_measure就像听起来一样。无论是英寸、杯子、像素、砖块还是其他任何东西，您都可以在这里定义一次。

FYI: Although generic in nature, one can create an application on top of this, and this schema is well-suited to the Ruby On Railsframework with conventions such as "id" for the primary key for each table. Also the relationships are all simple one_to_many's with no many_to_many or has_many throughs needed. I would probably add has_many :throughs and/or :delegates though to get things like survey_name from an individual answer easily without.multiple.chaining.

仅供参考：虽然本质上是通用的，但可以在此基础上创建一个应用程序，并且此架构非常适合Ruby On Rails框架，其约定如每个表的主键为“id”。此外，关系都是简单的 one_to_many，不需要 many_to_many 或 has_many 直通。我可能会添加 has_many :throughs 和/或 :delegates 以便在没有.multiple.chaining 的情况下轻松地从单个答案中获取诸如survey_name 之类的东西。

回答by Osama Al-Maadeed

You could also think about complex rules, and have a string based condition field in your Questions table, accepting/parsing any of these:

您还可以考虑复杂的规则，并在您的问题表中有一个基于字符串的条件字段，接受/解析以下任何一项：

• A(1)=3
• ( (A(1)=3) and (A(2)=4) )
• A(3)>2
• (A(3)=1) and (A(17)!=2) and C(1)

• A(1)=3
• ( (A(1)=3) 和 (A(2)=4) )
• A(3)>2
• (A(3)=1) 和 (A(17)!=2) 和 C(1)

Where A(x)=y means "Answer of question x is y" and C(x) means the condition of question x (default is true)...

其中 A(x)=y 表示“问题 x 的答案是 y”，而 C(x) 表示问题 x 的条件（默认为真）...

The questions have an order field, and you would go through them one-by one, skipping questions where the condition is FALSE.

这些问题有一个 order 字段，您可以一一检查它们，跳过条件为 FALSE 的问题。

This should allow surveys of any complexity you want, your GUI could automatically create these in "Simple mode" and allow for and "Advanced mode" where a user can enter the equations directly.

这应该允许调查您想要的任何复杂性，您的 GUI 可以在“简单模式”下自动创建这些，并允许用户可以直接输入方程的“高级模式”。

回答by tehvan

one way is to add a table 'question requirements' with fields:

一种方法是添加一个带有字段的表“问题要求”：

• question_id (link to the "which brand?" question)
• required_question_id (link to the "do you smoke?" question)
• required_answer_id (link to the "yes" answer)

• question_id（链接到“哪个品牌？”问题）
• required_question_id（链接到“你抽烟吗？”问题）
• required_answer_id（链接到“是”答案）

In the application you check this table before you pose a certain question. With a seperate table, it's easy adding required answers (adding another row for the "sometimes" answer etc...)

在应用程序中，您在提出某个问题之前检查此表。使用单独的表格，可以轻松添加所需的答案（为“有时”答案等添加另一行...）

回答by TomHastjarjanto

Personally, in this case, I would use the structure you described and use the database as a dumb storage mechanism. I'm fan of putting these complex and dependend constraints into the application layer.

就个人而言，在这种情况下，我会使用您描述的结构并将数据库用作愚蠢的存储机制。我很喜欢将这些复杂且依赖的约束放入应用程序层。

I think the only way to enforce these constraints without building new tables for every question with foreign keys to others, is to use the T-SQL stuff or other vendor specific mechanisms to build database triggers to enforce these constraints.

我认为在不为每个带有外键的问题构建新表的情况下强制执行这些约束的唯一方法是使用 T-SQL 的东西或其他供应商特定的机制来构建数据库触发器来强制执行这些约束。

At an application level you got so much more possibilities and it is easier to port, so I would prefer that option.

在应用程序级别，您有更多的可能性并且更容易移植，所以我更喜欢那个选项。

I hope this will help you in finding a strategy for your app.

我希望这能帮助您为您的应用找到策略。