title: Storing a multiple choice quiz in a database - deciding the schema
body: <p>I am trying to implement a multiple choice quiz and will want to store all my questions and answers in a SQLite database. I will have many questions, and for each question there will 2 or more possible answers to display.</p>
  
  <p>My question is, how should I store the questions and answers in a database? I have two ideas for a schema (primary key in bold)</p>
  
  <ol>
  <li>as (many to many)</li>
  </ol>
  
  <p>questions (<strong>questionID</strong>:int , questionString:String, correctAnswerID:int)</p>
  
  <p>answers (<strong>answerID</strong>:int , answerString:String)</p>
  
  <p>questions_and_answers (<strong>questionID</strong>, <strong>answerID</strong>)</p>
  
  <p>2.</p>
  
  <p>questions (<strong>questionID</strong>:int, questionString:String, correctAnswerID:int)</p>
  
  <p>answers (<strong>answerID</strong>:int, answerString:String, questionID:int <em>foreign key</em>)</p>
  
  <p>I'm not sure which one is better, or if there is another way? </p>
  
  <p>Maybe <code>questions_and_answers</code> would get very large and cause long retrieval times and memory problems? Then again, I assume <code>question_and_answers</code> would be indexed on the primary keys. In the second schema, <code>answers</code> would be indexed on <code>answerID</code> and not <code>questionID</code>? meaning the search times would go up as the whole table would have to be searched? </p>
  
  <p>There may be ~10,000 - 20,000 answers. (the quiz may be run on a mobile device and questions will need to be shown "instantly")</p>
  
  <p>Note: I don't expect there to much overlap of answers between questions. I wouldn't think the amount of overlap would mean less data being stored, considering the extra space required by the <code>questions_and_answers</code> table</p>