Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm doing the conceptual model for a survey database.

The goal is store the answers given by users (it going to be an Android app).

I have three entities: user, question and option.

A question will have one or more options (for example: How many employees do you have? 1-40, 40-1000, +1000).

Options will have a text (1-40) and a value (the value selected by user).

User will select one (or more) of these options.

My conceptual design is:

enter image description here

I don't know how to associate an answer with an user.

How can I represent that relation?
Do I have another entity to represent option value?

This model will store questions and pre-made answers (offered answers) and allows them to be re-used in different surveys.

I have to represent question like this one:

enter image description here

This question is related to this one: Survey database design: first version. Are there errors?

share|improve this question
1  
Looks like you will need another table to handle the many-to-many relationship between Users and Options. –  OliverAsmus Apr 3 '12 at 13:17
add comment

5 Answers

up vote 4 down vote accepted

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:

Colour Coded Form Example

This could be accomodated by the following logical ERD:

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.

share|improve this answer
    
I have updated my question with an image of a tipical survey question. –  VansFannel Apr 4 '12 at 6:58
1  
@VansFannel - I've updated my answer to reflect your edited question. –  Joel Brown Apr 4 '12 at 12:24
    
Thank you very much! You have helped me a lot. I have added my final design as a question here: dba.stackexchange.com/questions/16066/… You can check it if you wish. –  VansFannel Apr 4 '12 at 12:26
    
One question: What does it mean "sequence" and "value"? I haven't worked so much with ERD (sorry). –  VansFannel Apr 4 '12 at 12:54
    
@VansFannel - The attribute notes in the ERD are just non-trivial (or non-obvious) columns in the tables. I expect that you'd guess where to put IDs, FKs, descriptions, etc. For sequence I'm suggesting that you'll need/want to control the order in which items are displayed. For value I'm pointing out that a user-entered value (not just an option selection) may be appropriate. –  Joel Brown Apr 4 '12 at 19:30
add comment

[I can't comment yet, hence this as an answer]

For the solution VansFannel presented, I made a more complete model there.

Please, check it here.

share|improve this answer
add comment

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:

enter image description here

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.

share|improve this answer
    
Great! Thanks for your answer. I will learn a lot with it. Thank you. –  VansFannel Apr 21 '12 at 14:19
    
Great answer. Very useful since I'm fighting a similar design problem right now. :) –  Dr. Mike Nov 10 '13 at 21:04
add comment

Take a look at this general idea:

enter image description here

(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...

share|improve this answer
    
I have updated my question with an image of a tipical survey question. –  VansFannel Apr 4 '12 at 7:00
add comment

You will need another table to hold the answers of users.

user_answers
------------
  user_answer_id - unique primary key
  user_id - FK to User table
  chosen_option_id - FK to Option table
  question_id - FK to Question table

If you decide you want users to be able to select "Other" as an option and fill in their own value, I'd recommend a separate table for that:

user_alt_answers
----------------
  user_alt_answer_id - PK
  alt_answer_text - text that the user entered for an "other" option.
  user_answeR_id - FK to user_answers table
share|improve this answer
    
I have updated my question with an image of a tipical survey question. –  VansFannel Apr 4 '12 at 6:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.