Survey database design: associate an answer to a user

database-design

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

The goal is store the answers given by users (it's 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?

Best Answer

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.

Related Question