How to design a survey database schema that includes matrix questions?


I'm working on a complex survey form for a client and will be used only once, the survey is going to be anonymous so there is no users table involve, just an email of the person that fills the survey.

right now I'm trying to design the database schema, but I don't have the enough experience. The problem is the following

There are 3 types of questions:

 1. Simple - (input text, radio, dropdown select) (Question / answer)

 2. Multiple Choice Question - (checkbox) (Question / several answers)

 3. Matrix Question - Table with several rows and columns which holds radios as options

What I'm trying to achieve is the best way posible to design the schema in order to give my client the ability to query the data without much complexity

Questions Examples (3 images below for each type of question ):

Simple Question - Dropdown Select

Multiple Choice Question - Checkboxes

Matrix Question - Table with radio buttons

What I have so far:

DB Scheme Diagram

I can't solve how to store the matrix questions with their answers, thanks in advance for your time to take a look!


Answers:


Change your structure little bit think three entity (Question, Option and Answer) use first survey related table as it is, but change structure of question, option and answer table , it will definitely solve your problem.

enter image description here