How to structure a database with questions and answers?

I am going to开发者_Go百科 make a simple application that uses a database. I could need some guidance on how to structure it.

I shall make question program. What I have in mind is.

• One table with questions
• One table with the difficulity of the question
• One table with the category of the question

However, what do I do with the answers? Have them as seperate columns in the question-table? It sounds like a bad practice.(Also, where do I have the correct answer)

Each question will have 5 answers where only one of them is correct.

---

Answer table. Primary key can be (Question ID, Answer ID) where Answer ID can be, say, (a, b, c, d, e). Other fields as seen fit, and also include a correct_answer binary field which is 1 for the question that is correct.

---

Have a answers table with foreign key pointing to primary key of questions table

---

Why not keep all your question data in one table? Something like:
question_id | category_id | difficulty | correct_answer | question_text
Where category_id is a foreign key to a 'Category' table, and correct_answer is a foreign key to the questions table.

For answers, you can do something like
answer_id | question_id | answer_text | answer_order
Question id is a foreign key pointing to the questions table - answer order is a number you can use to sort the answers for each question