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:
Table details for key tables
answers
The answers table is critical as it captures the actual responses by users. You'll notice that answers links to question_options, not questions. This is intentional.
input_types
input_types are 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.
option_groups
option_groups and option_choices let 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+
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.
units_of_measure
units_of_measure is as it sounds. Whether it's inches, cups, pixels, bricks or whatever, you can define it once here.
FYI: Although generic in nature, one can create an application on top of this, and this schema is well-suited to the Ruby On Rails framework 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.
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)
Where A(x)=y means "Answer of question x is y" and C(x) means the condition of question x (default is true)...
The questions have an order field, and you would go through them one-by one, skipping questions where the condition is 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.
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)
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...)
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.
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.
发布评论
评论（4）
最后更新：2015 年 5 月 3 日
图表和 SQL 文件现可在 https://github.com/durrantm/survey
如果您使用此（顶部）答案或任何元素，请添加有关改进的反馈！！！
这是一个真正的经典，由数千人完成。 它们一开始似乎总是“相当简单”，但要做好，实际上却相当复杂。 为了在 Rails 中执行此操作，我将使用附图中所示的模型。 我确信对于某些人来说这似乎过于复杂，但是一旦您构建了其中一些，多年来，您会意识到大多数设计决策都是非常经典的模式，最好通过动态灵活的数据结构来解决一开始。
更多详情如下：
答案
的表格详细信息答案表格至关重要，因为它捕获用户的实际响应。
您会注意到答案链接到question_options，而不是问题。 这是故意的。
input_types
input_types 是问题的类型。 每个问题只能是 1 种类型，例如所有无线电拨号盘、所有文本字段等。当有（例如）5 个无线电拨号盘和 1 个用于“包含？”的复选框时，请使用附加问题。 选项或某种此类组合。 将用户视图中的两个问题标记为一个，但内部有两个问题，一个针对无线电拨号，一个针对复选框。 在这种情况下，复选框将有一组 1。
option_groups
option_groups 和 option_choices 让您可以构建“通用”组。
举一个例子，在房地产应用程序中可能会出现这样的问题：“该房产有多少年了？”。
可能需要的答案在以下范围内：
1-5
6-10
10-25日
25-100
100+
然后，例如，如果有关于相邻财产年龄的问题，那么调查将希望“重用”上述范围，以便使用相同的 option_group 和选项。
units_of_measure
units_of_measure 顾名思义。 无论是英寸、杯子、像素、砖块还是其他什么，您都可以在这里定义一次。
仅供参考：虽然本质上是通用的，但人们可以在此基础上创建一个应用程序，并且此模式非常适合 Ruby On Rails 框架，其约定例如每个主键的“id”桌子。 此外，这些关系都是简单的 one_to_many 关系，不需要 Many_to_many 或 has_many 遍历。 我可能会添加 has_many :throughs 和/或 :delegates 来轻松地从个人答案中获取诸如 Survey_name 之类的内容，而无需使用.multiple.chaining。
Last Update: 5/3/2015
Diagram and SQL files now available at 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:
answers
The answers table is critical as it captures the actual responses by users.
You'll notice that answers links to question_options, not questions. This is intentional.
input_types
input_types are 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.
option_groups
option_groups and option_choices let 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+
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.
units_of_measure
units_of_measure is as it sounds. Whether it's inches, cups, pixels, bricks or whatever, you can define it once here.
FYI: Although generic in nature, one can create an application on top of this, and this schema is well-suited to the Ruby On Rails framework 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.
您还可以考虑复杂的规则，并在您的问题表中有一个基于字符串的条件字段，接受/解析以下任何一个：
其中 A(x)=y 表示“问题 x 的答案是 y”，C (x) 表示问题 x 的条件（默认为 true）...
问题有一个顺序字段，您将一一浏览它们，跳过条件为 FALSE 的问题。
这应该允许您想要的任何复杂性的调查，您的 GUI 可以在“简单模式”下自动创建这些调查，并允许用户可以直接输入方程的“高级模式”。
You could also think about complex rules, and have a string based condition field in your Questions table, accepting/parsing any of these:
Where A(x)=y means "Answer of question x is y" and C(x) means the condition of question x (default is true)...
The questions have an order field, and you would go through them one-by one, skipping questions where the condition is 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.
一种方法是添加一个表“问题要求”，其中包含以下字段：
Question_id 在提出某个问题之前，请检查此表。
使用单独的表格，可以轻松添加所需的答案（为“有时”答案添加另一行等......）
one way is to add a table 'question requirements' with fields:
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...)
就我个人而言，在这种情况下，我会使用您描述的结构并将数据库用作哑存储机制。 我喜欢将这些复杂且依赖的约束放入应用程序层。
我认为，在不为每个问题都使用外键为其他问题构建新表的情况下强制执行这些约束的唯一方法是使用 T-SQL 内容或其他供应商特定的机制来构建数据库触发器来强制执行这些约束。
在应用程序级别，您有更多的可能性，并且更容易移植，所以我更喜欢这个选项。
我希望这将帮助您找到适合您的应用程序的策略。
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.
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.