db schema for storing survey matrix questions and answers

Question

I'm trying to create a db schema to store survey questions and answers whereas I'm not able to think of the schema to hold the data.

Here's my sample question that I want to store in database:

Any pointers on how do I do that is highly appreciated!

PS: I've gone through these posts on SO but they didn't help:

Relational Database System with dynamic columns with dynamic rows

Storing a distance matrix in DB

SQL design for survey with answers of different data types

Edit 1

PS:

I want to create a schema to store the all the question types from SurveyMonkey.com and Qualtrics.com. Is there any way?

Answer 1

MySQL version

CREATE TABLE MatrixQuestion ( username VARCHAR(45) NOT NULL COMMENT 'ex Michael', meal VARCHAR(10) NOT NULL COMMENT 'ex breakfast, lunch, dinner', rate TINYINT NOT NULL COMMENT 'ex 1:poor 2:average 3:fair 4:good 5:awesome', PRIMARY KEY (meal, username, rate) );

Answer 2

You can create three table as

Table FoodType

FoodId - PK (TINYINT) -- 1,2,3
FoodName - varchar(50)-- breakfast, lunch, dinner

Table RatingType

RatingId - PK (TINYINT) --1,2,3,4,5
RatingName - varchar(50) --poor,average,fair,good, awesome

Table UserRating

UserName - varchar(100)
RatingId - FK (TINYINT)
FoodId - FK (TINYINT)

Now for every user Feedback make an entry in table UserRating

Answer 3

Google stackoverflow questions about sql and questionaires/surveys. Read about EAV and its problems. (Eg search my answers for EAV).

One choice is to use a base table per question with a column for vertical and a column for horizontal, where queries about multiple questions query the database metadata & with the DBMS managing question integrity. The other choice is EAV where every query about a question involves building the corresponding table from the previous sentence and integrity is managed by you via complex constraints.

The only disadvantage to the first option is that current DBMSes don't make DDL updates as efficient as DML updates. You should structure your software to be independent of the option you are currently choosing. To the extent that you use EAV you are programming your own DBMS.

You can find example survey data export and download formats for answers and surveys, eg SurveyMonkey.com and Qualtrics.com (data or surveys).