Survey database design: associate an answer to a user

Survey Database Schema.

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 need to make a distinction between the possible answers and the selected answers.

The Option table needs to be two tables. The Option table should be 1:M to Question and should include the possible answers for that question.

Then you need to make a new intersection entity, call it Selected_Option which sits between User and Option.

If your question gives the user an opportunity to fill in a value as an answer (i.e. "OTHER:...") then this value would be stored in the Selected_Option table. Otherwise the value chosen by the user would be the value found in Option.

---

EDIT:

Based on OP's clarification of requirements: What you need is not like a typical questionnaire model in the following ways:

• Your questions all have the same sets of answers (columns)
• Some of your answers (columns) are grouped together.
• Blocks of questions are grouped together.

Taking your form snapshot as a guide, I've divided up the elements of your form into entities which I've colour coded:

This could be accomodated by the following logical ERD:

Note that I've colour coded the entities in the ERD to correspond to the snapshot of your sample form to show the correlation.

One of the assumptions in this model is that each block has only one set of quesitons (i.e. one QUESTION_GROUP) which corresponds to the left-hand column in the block. This is a bit of a simplifying assumption.

Take a look at this general idea:

(Only the most essential fields are included in the model above, for brevity.)

This model has the following characteristics:

• A single question can be shared among multiple surveys (and single survey, of course, can contain multiple questions). The SURVEY_QUESTION is the "link" table that implements this M:N relationship.
• The order of questions in the survey is determines by SURVEY_QUESTION.QUESTION_NO. Since {SURVEY_NO, QUESTION_NO} is an (alternate) key, denoted by U1 in the diagram above, no two questions can occupy the same "slot" in the same survey. Different surveys can have same questions in a different order.
• Each question has a series of possible answers or "options". The visual order of options is determined by OPTION.OPTION_NO, and since it is in the PK, no two options can occupy the same "slot" under the same question.
• Different users can provide different answers to the same question (and, of course, one user can answer multiple questions). This M:N relationship is implemented through the "link" table ANSWER.
• A user answers the question by choosing at most one of its options. This is ensured by excluding the OPTION_NO from the ANSWER's PK. If you want to allow user to select multiple options, include OPTION_NO in the PK.

There is nothing in this data model that forces the user to answer all the questions - this is something you'll need to do at the application level. Nonetheless, this model should be a good start for what you need to do...