CODEWITHSUNDEEP
mysqldatabase
userrandomuser
userrandomuser

Reputation: 103

Storing Sub-Questions in a database for a Survey/Questionnaire System

I am currently in the process of creating my tables for a survey/questionnaire system. As I got to creating the questions table, I think I came across a slight issue that could impact the whole application if I continue. Within my questions table, I have a column called "subBelongsToQuestion", which is an integer value for identifying which sub-questions belong to which parent questions (if any). Then in my answers table, I have a column called "responseRevealSubQuestion", which is an integer value for identifying which sub-questions to reveal if the trigger answer within the "responseRevealSubQuestion" column value matches with the "response" column value.

So for example, if a user answered yes to a question such as "Do you like cheese?", then a sub-question would appear saying "What do you like about cheese?".

I am wanting to convert this vision into a database format and I wasn't sure if I should continue with the approach I am using, or to change? It is so that if say a user deletes a question that contains sub-questions, then the application can run the required code to also delete the sub-questions and trigger answers as well.

Upvotes: 1

Views: 974

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

Usually for survey apps you dont use SubQuestions, you define flow conditions

Imagine you have this questions on your db

Q_ID   Question
 1     Do you like cheese?
 2     What do you like about cheese?
 3     Do you like meat?"
 4     What do you like about meat?
 5     ...

Then you have a flow table to validate after one answer.

Q_FROM   Q_VALUE Q_TO
  1        NO      3
  3        NO      5

In this case you only take detour for NO answer. otherwise you continue with the next question.

After your end each question you do 

SELECT Q_to 
FROM FlowTable 
WHERE Q_from = @CurrentQuestion 
  AND Q_value = @CurrentAnswer

Upvotes: 1

Related Questions