Structure of database with questions and answers

I am designing a database that contains questions, answers and categories.

One question can belong to many categories, and one cate开发者_如何学JAVAgory can have many questions. So this relationship is a many-to-many.

One question table

One category table

One table containing primary keys of the two tables above

So now my question is, is it necessary or "better" to store answers (a, b, c, d, e) in a own table named answers? Then the answer table has a foreign key to the primary key in the question table.

Or should I just have 5 columns in the question table containing the answers as well with the correct answer?

After what I read it is not normal to have a one - to - one relationship.

Thanks in advance!

Make a separate answers table. There are two benefits:

1. It fits in relational paradigm -- one question linked to many answers.
2. You will have flexibility, in case, if options for answers goes beyond 5.

The answers table should look like this:

ANSWERS
  answer_id (PK)
  answer_text
  is_correct
  serial_number
  question_id (FK)

Now, you store one answer in one row... and for serial_number (a,b,c.. or 1,2,3.. or i, ii, iii,... whatever) you use serial_number field. is_correct points to correct answer/s.

You can select list of answers by

  select * from `answers` where question_id = 123 order by serial_number asc

If you know for certain that there will always be exactly give answers (neither more nor less), then it's reasonable to make them columns in the questions table. It'll simplify your look-up process and be more efficient, generally. However, if you sometimes have varying numbers of answers or might in the future, then your answers should have their own table.