CODEWITHSUNDEEP
mysqldatabase-design
handsome
handsome

Reputation: 2422

almost infinite columns

I want to create different sets of questions and store the answers

this is what i tried

table questions id, name

table fields id, label

table relations id, question_id, field_id

this works great but only for the display part of the questions but what about storing the answers? i can't store answers horizontally because i don't have a reference (col names), and vertically will be very bad for the database right?

im using mysql. what will be the best way to do this? thx!

Upvotes: 1

Views: 329

Answers (2)

Raptor
Raptor

Reputation: 54260

You can just add 1 more table to store answer :

question_id  PK   INT
user_id      PK   INT <-- or maybe anything that identifies the user
field_id     PK   INT
answer            VARCHAR <-- or TEXT , depends on your usage

To return all results ( assume the answer table is named as tbl_answer ):

SELECT `c.name` AS question, b.label, answer FROM tbl_answer a 
LEFT JOIN fields b ON a.field_id=b.id 
LEFT JOIN questions c ON a.question_id=c.id

To return the result of specific user:

SELECT `c.name` AS question, b.label, answer FROM tbl_answer a 
LEFT JOIN fields b ON a.field_id=b.id 
LEFT JOIN questions c ON a.question_id=c.id 
WHERE user_id={user id here}

To return the result of specific question:

SELECT `c.name` AS question, b.label, answer FROM tbl_answer a 
LEFT JOIN fields b ON a.field_id=b.id 
LEFT JOIN questions c ON a.question_id=c.id 
WHERE question_id={question id here}

Of course, you have to further process the result .

Upvotes: 2

stevecomrie
stevecomrie

Reputation: 2483

Simple.

You just need another table to store answers in:

Answers

id question_id field_id (answer)

You'll probably also need one more field to associate your "Answer" with something, like a person or a questionnaire being answered.

Upvotes: 1

Related Questions