CODEWITHSUNDEEP
mysql
Adam
Adam

Reputation: 1299

Is this good practices for questions and answers?

Im about to start a "quiz" project, with questions and answers. And before i start, I just want to check with you experts on how the database design should be.

I have googled, and searched SO and found this answer, where it should be two tables. One with questions, and one with answers.

TABLE questions
FIELDS: id, text

TABLE answers
FIELDS: id, question_id, text, correct

I think i will have about 100+ questions from different subject though.

So i was thinking to add a subject row so sorting, and displaying questions from a specific subject is possible, something like this:

enter image description here

The question table will look like this for example:

+----+-----------+---------------------------------------+
| id |  subject  |                 text                  |
+----+-----------+---------------------------------------+
|  0 | beer      | what is the best beer in the world?   |
|  1 | mountains | what is the world's highest mountain? |
+----+-----------+---------------------------------------+

And answers table, were 1 is the correct answer:

+----+------+---------------+---------+
| id | q_id |     text      | correct |
+----+------+---------------+---------+
|  0 |    0 | carlsberg     |       1 |
|  1 |    0 | heiniken      |       0 |
|  2 |    0 | root beer     |       0 |
|  3 |    0 | budweiser     |       0 |
|  4 |    1 | k2            |       0 |
|  5 |    1 | Kangchenjunga |       0 |
|  6 |    1 | Mount Everest |       1 |
|  7 |    1 | Makalu        |       0 |
+----+------+---------------+---------+

My question/s:

Upvotes: 1

Views: 59

Answers (1)

divix
divix

Reputation: 1364

It isn't the best solution..

I would suggest to drop column q_id from answers and create a linking table called like: questions_to_answers with columns: id (int autoincrement), question_id (int), answer_id (int) and correct_answer (tinyint) instead.

That way you could re-use answers to multiple questions (many-to-many relationship) or just have duplicated answers to the same question. It gives you move powerful and robust solution in my opinion.

Upvotes: 3

Related Questions