patternModerate
Survey database design: associate an answer to a user
Viewed 0 times
surveyanswerassociateuserdesigndatabase
Problem
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?
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?
Solution
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.
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.
Context
StackExchange Database Administrators Q#16002, answer score: 15
Revisions (0)
No revisions yet.