Survey database design: associate an answer to a user 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:



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:



This question is related to this one: Survey database design: first version. Are there errors?
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 &quot;include?&quot; 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 &quot;id&quot; 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.