CODEWITHSUNDEEP
phpmysqljsondatabase-design
Student of Hogwarts
Student of Hogwarts

Reputation: 1138

Save quiz alternatives as JSON string or individual rows?

I'm making a quiz. Each question consists of a question, three alternatives, and one of them are correct. The server should output HTML (not JSON that JS is going to parse), and the user chooses an answer.

The reason why I'm in doubt is that the person who makes the quiz is sending the alternatives over JSON. And if you want to edit any of the alternatives later on, you just parse the JSON, do your changes and send it to the server. Then the server will just update the alternatives "cell".

However when the user takes the quiz, the server has to run through the array to find the right answer. If the answers are stored in their own separated row (many-to-many), the server could just query for the right answer.

In addition to this, JSON would result in saving the alternatives in a TEXT column compared to many small VARCHARS if one uses a many-to-many relationship.

What is the most efficient way to do it? (Speed) What is the most convenient way, and most accepted way to do it?

Upvotes: 0

Views: 318

Answers (1)

pho
pho

Reputation: 25490

Well, I would have say that your table should look like this:

+------+------------+--------+--------+--------+--------------+
|  ID  |  Question  |  Ans1  |  Ans2  |  Ans3  |  CorrectAns  |
+------+------------+--------+--------+--------+--------------+
| PKey |    text    |  text  |  text  |  text  |     text     |
+------+------------+--------+--------+--------+--------------+
|   1  |    π = ?   | 3.1415 | 2.2465 | 5.6598 |     3.1415   |
+------+------------+--------+--------+--------+--------------+

And your query would be:

SELECT (CorrectAns = %enteredanswer%) FROM QuizTable WHERE ID=%questionid%

Its pretty easy to convert json to this table, and vice-versa.

EDIT: For an undefined number of alternatives:

You could have a Question table, and an Options table like so:

Question:
+-------+------------+---------------+
|  QID  |  Question  |  CorrectAnsID |
+-------+------------+---------------+
|  PKey |   text     |  FKey,integer |
+-------+------------+---------------+
|   1   |   π = ?    |        2      |
+-------+------------+---------------+

Options:
+-------+--------------+--------------+
|  OID  |  OptionText  |  QuestionID  |
+-------+--------------+--------------+
|  PKey |    text      | FKey,Integer |
+-------+--------------+--------------+
|   1   |    3.5600    |      1       |
+-------+--------------+--------------+
|   2   |    3.1415    |      1       |
+-------+--------------+--------------+
|   3   |    3.4567    |      1       |
+-------+--------------+--------------+
|   4   |    3.7894    |      1       |
+-------+--------------+--------------+
|   5   |    3.9874    |      1       |
+-------+--------------+--------------+

This might be faster to work than the json method, but more difficult to implement (joins and stuff to write), so if its a quick, smaller scale project, I'd go with the json method.

Upvotes: 1

Related Questions