CODEWITHSUNDEEP
mysqldata-structures
mike16889
mike16889

Reputation: 352

How to store answers in tall and narrow MySQL table

I'm designing a database to store questions and answers. I am planning on having 5 tables: quiz, question, userAnswer and multipleChoice

quiz:

quizID          (int)(primary key) Auto Increment
quizName        (varchar)

question:

questionID      (int)(primary key) Auto Increment
quizID          (Foreign Key)
question        (varchar)
questionType    (enum) textbox, multiple choice, int, date, decimal, email, ...

users:

userID          (int)(primary key) Auto Increment
firstname       (varchar)
surname         (varchar)
...
...

userAnswers:

questionID    (Foreign Key)
userID        (Foreign Key)
answerText
answerDec
answerDate
answerInt

PRIMARY KEY(questionID, userID)

multipleChoice

choiceID      (int)(primary key) Auto Increment
questionID    (Foreign Key)
choiceText    (varchar)

My problem lies with the answer table, some answers will be free form text, some decimals, and some integers (foreign keys)

How do I approach this? Do I have 4 columns, 1 for each type? If so, how do I efficiently select the correct one of 3 when retrieving the answers, I would like to do as much of the "heavy lifting" in SQL as I can, without having to use PHP to generate dynamic queries.

Upvotes: 0

Views: 80

Answers (1)

Zoe Edwards
Zoe Edwards

Reputation: 13667

If you don’t need to search by the answers, then I’d recommend storing them in a JSON object or similar, and not bothering with seperate tables.

quiz | question   | answers     | multipleChoice
---- | ---------- | ----------- | --------------
1    | "Country?" | [           | true
     |            |   "France", |
     |            |   "Germany" |
     |            | ]           |

Upvotes: 0

Related Questions