title: Bingo game database representation for scalability
body: <p>This is my first scalability-related question.</p>
  
  <p>In order to simplify the problem, I will use a bingo app idea:</p>
  
  <p>We have a bingo application. Each users has a ticket with 15 random numbers out of 90. Each week the bingo is held to find the winner. Numbers are drawn <strong>live</strong> until there is a winner. Ex:</p>
  
  <ul>
  <li>15th number is drawn -> search table -> no match</li>
  <li>16th number is drawn -> search table -> no match</li>
  <li>...</li>
  <li>30th number is drawn -> search table -> winner -> stop</li>
  </ul>
  
  <h1>Question 1:</h1>
  
  <p>Which way is better / faster for representing data in table and for searching that table? Table will have 10+ million rows</p>
  
  <h1>Idea 1:</h1>
  
  <p>Table Tickets</p>
  
  <pre><code>id           user_id          week    ticket                                                 created
  ====================================================================================================
  1            100022312        1       1,3,5,7,9,14,15,77,78,79,80,81,82,83,84            &lt;timestamp&gt;
  2            102232123        1       2,5,9,22,33,44,55,66,77,,78,79,80,88,89            &lt;timestamp&gt;
  3            201141028        1       7,8,9,11,22,33,34,35,37,39,51,55,58,63,66          &lt;timestamp&gt;
  ...
  9.000.000    126387125        1       8,18,28,38,48,58,68,78,79,80,81,82,83,84,85        &lt;timestamp&gt;
  10.000.000   126387126        1       1,4,14,24,34,45,56,66,67,68,79,80,81,82,83         &lt;timestamp&gt;
  </code></pre>
  
  <p>Php</p>
  
  <pre><code>$drawn_numbers = '1,2,3,4,5,6,7,89,10,11,12,13,14,15,16,17,18,19,20,21,22, ...';
  
  $result = query("SELECT * FROM Tickets WHERE sksf('$drawn_numbers')");
  </code></pre>
  
  <p>where <code>sksf</code> would be some kind of substring function / regex / LIKE done in MySql.</p>
  
  <h1>Idea 2:</h1>
  
  <p>Table Tickets</p>
  
  <pre><code>id           user_id          week    n1   n2   n3   ...   n15        created
  =============================================================================
  1            100022312        1       11   32   52   ...   76     &lt;timestamp&gt;
  2            102232123        1       22   52   55         78     &lt;timestamp&gt;
  3            201141028        1       77   82   83   ...   89     &lt;timestamp&gt;
  ...
  9.000.000    126387125        1       81   55   32   ...   10     &lt;timestamp&gt;
  10.000.000   126387126        1       12   42   13   ...   77     &lt;timestamp&gt;
  </code></pre>
  
  <p>Php</p>
  
  <pre><code>$drawn_numbers = '1,2,3,4,5,6,7,89,10,11,12,13,14,15,16,17,18,19,20,21,22, ...';
  
  $result = query("SELECT * FROM Tickets WHERE contidion1 AND condition2 AND ...");
  </code></pre>
  
  <p>Unfortunately, here I still don't have any idea about the conditions.</p>
  
  <h1>Idea 3:</h1>
  
  <p>I select all tickets, iterate though them by checking if the drawn numbers contain any ticket.</p>
  
  <pre><code>$drawn_numbers = '1,2,3,4,5,6,7,89,10,11,12,13,14,15,16,17,18,19,20,21,22, ...';
  $all_tickets = query("SELECT * FROM Tickets");
  
  foreach ($all_tickets as $ticket) {
      if $drawn_numbers.contains($ticket['ticket'])
          return $ticket['id'];
  }
  </code></pre>
  
  <h1>Question 2:</h1>
  
  <p>Will having sorted numbers help in anyway? (Those 15 numbers and the drawn numbers)</p>
  
  <h1>Question 3:</h1>
  
  <p>What happens when week 2 arrives? Should I use the same table, adding a condition <code>WHERE week=2</code>, or is it better to have only 1 week per table?</p>
  
  <h1>Update</h1>
  
  <p>In the original game, a ticket has 15 numbers in 3 rows, each row having 5 numbers. After each number is drawn live, they are able to also calculate the tickets which have one row found in the drawn numbers (also they know the tickets with 2 rows). A ticket having 3 rows found in the drawn numbers would mean winning ticket.</p>
  
  <p>This information makes me think that the representation looks something like:</p>
  
  <h1>Idea 4:</h1>
  
  <p>Table Tickets</p>
  
  <pre><code>id           user_id          week    row1                row2                 row3                     created
  ===============================================================================================================
  1            100022312         1      1, 3, 5, 7, 9       14,15,77,78,79,      80,81,82,83,84       &lt;timestamp&gt;
  2            102232123         1      2, 5, 9,22,33,      44,55,66,77,78,      79,80,87,88,89       &lt;timestamp&gt;
  3            201141028         1      7, 8, 9,11,22,      33,34,35,37,39,      51,55,58,63,66       &lt;timestamp&gt;
  ...
  9.000.000    126387125         1      8,18,28,38,48,      58,68,78,79,80,      81,82,83,84,85       &lt;timestamp&gt;
  10.000.000   126387126         1      1, 4,14,24,34,      45,56,66,67,68,      79,80,81,82,83       &lt;timestamp&gt;
  </code></pre>
  
  <h1>Example of ticket:</h1>
  
  <pre><code>__  13  __  33  40  __  __  70  __
   2  __  22  37  __  52  62  __  81
  __  19  23  __  44  __  63  __  89
  </code></pre>
  
  <h1>Example of drawn numbers (not necessarily in sorted order)</h1>
  
  <pre><code>1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 19, 23, 44, 63, 89
  -&gt; there is a winner, no more numbers are drawn.
  -&gt; Our ticket did not win jackpot, but we won one row [19, 23, 44, 63, 89] (free ticket)
  -&gt; One can win also 2 rows.
  </code></pre>