0

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:

enter image description here

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?

Community
  • 1
  • 1
Sunny Sharma
  • 4,688
  • 5
  • 35
  • 73

3 Answers3

1

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).

philipxy
  • 14,867
  • 6
  • 39
  • 83
0

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) );

Michael Park
  • 26
  • 1
0

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

vijayp
  • 173
  • 10
  • thanks for your reply... but how would you get all the rating names for a FoodId? also... it's not a single question always! – Sunny Sharma Nov 20 '14 at 08:44
  • Try something like this `SELECT UR.UserName, RT.RatingName ,FT.FoodName FROM UserRating UR JOIN RatingType RT ON RT.RatingId = UR.RatingId JOIN FoodType FT ON FT.FoodId = UR.FoodId WHERE UR.FoodId = 1` – vijayp Nov 20 '14 at 08:52
  • @Sunny & vijayp: This is the essentially same as the answer with MatrixQuestion(username,meal,rate) but with ids poinlessly added. Each design should have appropriate type checks. – philipxy Nov 25 '14 at 08:11
  • @Sunny: We can't read your mind. Explain what "get all the rating names for a FoodId" and "it's not a single question always" mean. – philipxy Nov 25 '14 at 08:45
  • @philipxy thanks for your valuable inputs. by saying "get all the rating names for a FoodId" I meant, vijayp didn't mention any Foreign Key to the FoodType Table so how would you get all the ratings. I have edited my question for "it's not a single question always", PS. Thanks again for pointing me to EAV... gonna go look into this. thanks! – Sunny Sharma Nov 25 '14 at 10:59