title: Designing an efficient database for logging data
body: <p>I am designing a database for a project I am working on. I have 3 tasks (which the user can choose from)</p>
  
  <ul>
  <li>First task gives 10 questions</li>
  <li>Second task gives 20 questions</li>
  <li>Third task gives 30 questions </li>
  </ul>
  
  <p><em>n.b. the accuracy of the test result will increase as the number of questions go up</em>.</p>
  
  <p>For each question, there are going to be two answers. <code>Answer 1</code> and <code>Answer 2</code>. So if there are 10 questions, there are going to be total of 20 answers logged.</p>
  
  <p>What I have designed so far is a <code>user</code> table and a <code>test_result</code> table. The <code>test_result</code> table is the part I am having trouble designing.</p>
  
  <p>The <code>user</code> table has the following attributes</p>
  
  <ul>
  <li>user id</li>
  <li>name</li>
  <li>registration date</li>
  </ul>
  
  <p>The <code>test_result</code> table has the following attributes</p>
  
  <ul>
  <li>test id</li>
  <li>user id (references user ID in user)</li>
  <li>test taken time</li>
  </ul>
  
  <p>My problem is I do not know how I am going to efficiently log 10~30 question answers.</p>
  
  <p>I am considering using a <code>test_result</code> table</p>
  
  <p>The <code>test_result</code> table would have the following attributes</p>
  
  <ul>
  <li>test id</li>
  <li>user id </li>
  <li>test taken time</li>
  <li>answer 1a</li>
  <li>answer 1b</li>
  <li>answer 2a</li>
  <li>answer 2b</li>
  <li>... </li>
  <li>answer 60a</li>
  <li>answer 60b** </li>
  </ul>
  
  <p>but I think there should be a more efficient way.</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>To clarify...</p>
  
  <p>Each question will record 2 different answers. </p>
  
  <ul>
  <li>Question 1 will have Ans1 Ans2.</li>
  <li>Question 2 will have Ans1 Ans2.</li>
  <li>etc.. </li>
  </ul>
  
  <p>all the way up to 10~30 questions (the number of questions varies depending on the task you chose)</p>
  
  <p><strong>----::EDIT 2::</strong></p>
  
  <p>OK... SO I have come up with an improved design but I am still struggling.</p>
  
  <p>It looks like :</p>
  
  <p><code>User</code> (user_id, Name, reg_date)</p>
  
  <p><code>Test</code> (test_id, type [either 1,2,3], category [there are 5 different categories] )</p>
  
  <p><code>Response</code> (Response_id, user_id, test_id, response_date, response_1a, response_1b, response_2a, response_2b, ... , response_30a, response_30b)</p>
  
  <p>But again, I run in to the problem of having +120 columns for the <code>Response</code> table. Each responses will be numeric values from 0~1.0.</p>
  
  <p>Notice that if the <code>test_id</code> is 1, there would be only 10 questions, so the columns in <code>Response</code> table from <code>response_11a</code> to <code>response_30b</code> would be <code>null</code>.</p>
  
  <p>An example record would be:</p>
  
  <p><code>Response</code> (1, 1, 1, 2015/04/30, 0.5, 0.5, 0.7, 0.7, 0.8, 0.8, ... (a lot of records)...  , 1.0, 0.3, null, null, null, (all nulls because there are only 10 questions for test_id 1. ) )</p>
  
  <p>I cannot think of another way of doing this. :/</p>
  
  <p>Is this a good way of designing the database?</p>