Apologies if this question has been asked before, but I can't seem to find anything. I have two tables:

Here's the result I'm going for:

In this scenario, question_id links the Questions and Responses table.

For the result:

If question_type = multiple_choice, it counts the number of responses corresponding to each multiple choice option and stores it in a JSON under mcq_response.

Meanwhile, if question type = text_input, it stores all the responses in a comma-separated string under text_response.

My work so far:

 SELECT Questions.*, IF(Questions.question_type = "text_input", GROUP_CONCAT(Responses.question_response), null) as text_response 
 FROM Questions  
 LEFT JOIN Responses
 ON (Questions.question_id = Responses.response_id)
 group by Questions.question_id

This yields the text response column perfectly, and I can use it in my application.

However, I can't begin to wrap my head around how to approach the mcq_response column, specifically the mapping between the mcq_choices and number of responses for each key in the JSON. Any help/direction here would be much appreciated.

The database design is flexible, so I can always change it to make the solution easier. Thanks in advance!

CodePudding user response：

You should separate the choices into a separate table. You can use the following columns:

question_id | option | value

In that way, you can simply save the option in the results table and query like this:

select 
        q.question_id, q.question, q.question_type,
        case when q.question_type == 'multiple_choice' then mcq.value
            else r.question_response end as response
from Questions q
    join Responses r on q.question_id = r.question_id
    left join MCQ mcq on mcq.question_id = r.question_id 
        and r.question_response = mcq.option