Db design consideration
I am going to be creating a survey with different types of answers (i.e. choices in the shape of drop down, check boxes etc) or a free text field.
currently I am thinking of doing something like:
questions (id, field_type,parent_id)
choices (question_id, choice_id, choice)
user_responses(user_id, question_id, choice_id, free_text_answer)
question 1: in user_responses should i have both question_id and choice_id? I can get one of them by querying
question 2: in user_responses would i have the free_text_answer like that or should i split that table into two? one table for when question has choices, another table for when the question has free style texts
question 3: some of the questions are based on "parent questions" i.e. if you choose yes in the parent question, you get different set of questions and if you choose no, you might get some other set. How do i depict that in the database? currently i am doing
questions (id, field_type,parent_id) however certain people have objected to this type before. There will most likely be one set of hierarchy.
q4: i want to have some sort of ordering to the questions, so certain questions will be at the top. This is easy but when the admin create the question how would I show the hierarchy?