K
K
kajidooto2017-02-19 10:16:54
PostgreSQL
kajidooto, 2017-02-19 10:16:54

How to schema data for surveys?

I can't figure out how to create a database schema for polls. Questions can be of the following types:

Животные
------------------
1. Вы любите домашних животных (да, нет, не очень)
2. Вы любите диких животных (да, нет, не очень)
3. Вы любите бездомных животных (да, нет, не очень)


Любите ли вы свою работу?
------------------
оцените по шкале (1, 2, 3, 4 ... 10)


Хотите ли повышения зарплаты:
------------------
 - да
 - нет


Что бы вы сделали, если бы вы потеряли работу
------------------
 - ничего
 - стал бы паниковать
 - расслабился бы
 - радовался

This is simplified. I can't figure out how to make it so that there are both predefined answers (1, 2 ... 10, yes-no) and free ones, and groups of questions with a common scale, as in the 1st question.
And at the same time, so that it fits well into the database schema. No repetition and that there is data integrity.
There will be one table for predefined_options_answers , where it will be
да, нет, не очень, 1...10, да, нет
+ you still need to somehow figure out where to put the answer options for the 4th type of questions.
So, there will be a table with questions. The relationship will be many-to-many with a predefined_answer_options table. Well, what's next....?

Answer the question

In order to leave comments, you need to log in

2 answer(s)
A
Alexander Pushkarev, 2017-02-19
@AXP-dev

For questions
For answers

id (int) | user_id (key id user) | question_id (key id question) | answer_id (key id answer json)

F
Falseclock, 2017-02-19
@Falseclock

In general, in this case, three tables are required:
1. a table with the entity
question_id serial
question_type enum
question_date
question_foo and other parameters
2. A table with questions, where there can be 1 question or several
question_data_id serial
question_data_text text
question_id int - a link to the parent
3. Answer table
question_data_answer_id - serial
question_data_answer_text - txt
question_data_answer_score - int
question_data_id - link to the parent table
All... Depending on the question_type, you will create different types of questionnaire construction. Integrity is maintained.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question