哪些 mysql 数据库表和关系将支持带有条件问题的问答调查? [英] What mysql database tables and relationships would support a Q&A survey with conditional questions?

问题描述

我现在正在开发一个相当简单的调查系统.数据库模式将很简单:一个 Survey 表，与 Question 表是一对多关系，它与Answer 表和 PossibleAnswers 表.

最近，客户意识到她希望能够仅向对之前的问题给出特定答案的人显示某些问题(例如，您是否购买香烟?后面会显示什么是您最喜欢的香烟品牌?，没有必要向不吸烟者问第二个问题).

现在我开始想，就我的数据库架构而言，实现这个条件问题的最佳方法是什么?如果 question A 有 2 个可能的答案:A 和 B，并且 question B 应该只出现在用户 如果 答案是 A?

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

解决方案

调查数据库设计

上次更新:2015 年 5 月 3 日
图表和 SQL 文件现在可在 https://github.com/durrantm/survey

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

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

<块引用>

关键表的表详细信息

答案

answers 表很重要，因为它记录了用户的实际响应.您会注意到答案链接到question_options，而不是问题.这是故意的.

输入类型

input_types 是问题的类型.每个问题只能是一种类型，例如所有无线电拨号、所有文本字段等.对于何时有(例如)5 个无线电拨号和 1 个包括?"复选框，请使用附加问题.选项或一些这样的组合.将用户视图中的两个问题标记为一个，但内部有两个问题，一个用于无线电拨号，一个用于复选框.在这种情况下，复选框将包含一组 1.

option_groups

option_groups 和 option_choices 可让您构建通用"组.一个例子，在房地产申请中可能会出现这样的问题:房产的使用年限是多少?".可能需要以下范围的答案:1-56-1010-2525-100100+

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

units_of_measure

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

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

I'm working on a fairly simple survey system right now. The database schema is going to be simple: a Survey table, in a one-to-many relation with Question table, which is in a one-to-many relation with the Answer table and with the PossibleAnswers table.

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 conditional questions in terms of my database schema? If question A has 2 possible answers: A and B, and question B should only appear to a user if the answer was 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 ;)

解决方案

Survey Database Design

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:

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.

这篇关于哪些 mysql 数据库表和关系将支持带有条件问题的问答调查?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！