title: SQL: How to design best table for my data structure
body: <p>I am designing postgresql database for an online card game. I want to provide users with option to access their play history - user can see log of his games.</p>
  
  <p>The data I want to save has the following structure:</p>
  
  <pre><code>Player Ids | game_data
  -----------|--------------
  A,B,C,D    | ..game log..
  A,D,E,F    | ..game log..
  D,C,A      | ..game log..
  D,A        | ..game log..
  </code></pre>
  
  <p>Each game can have up to 22 participants, so Max number of players is 22, Min number of players is 2.</p>
  
  <p>So far I have about 100M records. Every day I add about 500K records. I have about 500K Players. Player-ID is a 32 byte string (MD5).</p>
  
  <p>I want players to be able to access their game_data, so I want player to be able to select last XX games_logs by Player-Id. I need to do it as fast as possible. What would be the best way to do it with Postgres? I would prefer to save all these data in a single table.</p>
  
  <p>So far I am considering two approaches:</p>
  
  <h2>Approach 1</h2>
  
  <p>Make a field of JSON type and save all Player's in in JSON-Array and query JSON in SELCT statment.</p>
  
  <h2>Approach 2</h2>
  
  <p>Make 22 fields in a table for each player (If there is no player field is NULL) and make ugly query over all fields.</p>
  
  <p>So far I don't like any of these approaches. If there is a better way to do it?</p>
  
  <h2>Added</h2>
  
  <p>Typical request would be: SELECT LAST XXX GAME FOR PLAYER_ID = 'A'</p>