What MySQL Database Tables and Relationships Would Support a Q&A Survey With Conditional Questions

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

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.

How do I model results of a simple survey?

With a one to many relationship you can connect question and answer by putting the primary key (id) of the question into the answer table as a foreign key.

This way you can access all of the answers to a given question by querying all answers with a certain question ID.

As for incorporating users: You say that a user can answer each question once. A user can answer many questions and a question can be answered by many users. In this case, you should have a linking table between the two that has the id of the question and the id of the user. Through this association you can see what a specific user answered on each question by traversing your other relationship.

Database Design for Conditional Questionnaire

A table has an associated fill-in-the-(named-)blanks statement aka predicate. Rows that make it into a true statement go in the table. Rows that make it into a false statement stay out. That's how we interpret a table (base, view or query) and update a base. Each table represents an application relationship.

(So your predicate-style quote for 2 is how to give a table's meaning. Because then JOIN's meaning is the AND of argument meanings, and UNION the OR, EXCEPT is the AND NOT, etc.)

1. How can I modify my schema to "link" answers to multiple choice
   questions? (ie "the following answers are available for question X.")

// question [question_id] has available answer [answer_id]
question_answers(question_id, answerid)

2. How should the answers drive the next question?
   (ie. "for question #1, if answer A is chosen, then GOTO question 5")

// for question [this_id] if answer [answer_id] is chosen then go to question [next_id]
next_question(this_id, answer_id, next_id)

PS

There are many different ways of representing graphs (nodes with edges between them) via tables. Here the nodes are questions and the edges are this-next question pairs. Different tables support different kinds of graphs and different patterns of reading and update. (I chose one reflecting your application, but framed my answer to help you find your best representation via proper design yourself.)

PPS

If different user traces through questions can mean that which question follows another is context-dependent:

// in context [this_id] if answer [answer_id] is chosen then go to context[next_id]
next_context(this_id, answer_id, next_id)

What a "context" is depends on aspects of your application that you have not given. What you have given suggests that your only notion of context is the current question. Also, depending on what a context contains, this table may need normalization. You might want independent notions of current context vs current question. (Topic: finite state machines.)

Core Data Model For Test Taking iOS App

You should be able to remove the TEST_QUESTIONS and USER_ANSWERS tables. There is currently a one-to-one mapping between TEST_QUESTIONS and TEST, and USER_ANSWERS and USER that really serves no purpose.

In theory, you could also combine TEST and USER since they are also one-to-one, but one could argue that they are separate concerns and should each have a table.

Also, while the design does conform to your assumption of a test having a single user, are you sure that is what you want? That means that a test could only be associated to a single user.

If that's the case, you could make another simplification. You could add a field to the ANSWER table such as "wasSelectedByUser" and remove the relationship between USER and ANSWERS since you would then be able to navigate from USER to TEST to QUESTION to ANSWER and determine what the user selected.

Simple survey database design

I would probably model the event of a user taking a survey, perhaps a table called "User_Answer_Session", which has links to the survey and the user; and then "User_Answers", which are tied to the session and the question and include the actual blob of the answer. How exactly I modeled the answers would depend on a few things (mainly how robustly I wanted to be able to look them up). For instance, do I want to be able to index multiple-choice answers for extremely rapid reporting? If so, then you need to model for that. This may include creating a "Question_Options" table, which is a one-to-many between a question and the available options...

This should get you thinking along a good path. :-)

customer application mysql design system

I don't know php so can't provide a full answer, but based on my experience in other areas and languages I would recommend calling form_options form_questions instead.

Then have a form_answers table that has the form_id and the question_id and the answer.

You'll also want to rename 'form' to something like application_form.

So with that you could have tables:

application_form (id,name)
application_question (id,application_form_id)
application_answer (id,application_form_id, application_question_id)

I would actually DRY that up into:

application_form (id,name)  
question (id, application_form_id)  
answer (id, question_id, application_form_id)

Careful with questioms and answers though.

Once you realize the full complexity and flexibility required you can easily end up needing to have a What mysql database tables and relationships would support a Q&A survey with conditional questions?

how should I design a questionnaire database?

In a relational schema, you would represent question dependency through a self-joining foreign key; you don't need to go back to the junction table, since the relationship between the two questions is independent from each question's relationship to the questionnaire.

However, as you've probably noticed, representing a branching set of questions in a relational schema is more than a little awkward. If the questionnaire is most of what you're storing, you might want to look into alternatives like MongoDB (or using JSONB fields in Postgres), which would let you represent a questionnaire as a document containing nested questions. A single user's responses to a questionnaire comprise a second (type of) document; all collected questionnaire results are easily searchable by user or by questionnaire, and you can slice and dig into individual question responses using the aggregation tools. The only thing it doesn't make easier is question reuse -- but odds are that's less than critical.

Treating questionnaires and questions as documents also gives you some much more flexible tools for representing dependency: instead of a hard link between "do you smoke?" and "how much?", you might apply a validator to a conditions object, if present, and only ask the question if any conditions are met:

[..., {
  "name": "smoker",
  "text": "Do you smoke?",
  "values": [true, false]
}, {
  "name": "how-much",
  "text": "How much do you smoke?",
  "conditions": {
    "smoker": true
  },
  "values": ["Socially", "< 1 pack/day", "1 pack/day", "2 packs/day", ...]
}, ...]

MySQL: Updating Lookup Tables

See my edit for my solution:

INSERT INTO s_question_options (questions_id, option_choices_id)
SELECT sq.id, so.id FROM s_questions sq JOIN s_option_choices so
ON sq.option_groups_id = so.option_groups_id
WHERE sq.id NOT IN (SELECT questions_id FROM s_question_options)
OR so.id NOT IN (SELECT option_choices_id FROM s_question_options)

---

Related Topics