title: SQL Database Design: extra table vs extra primary key column
body: <p>I want to optimize my PostgreSQL database design.</p>
  
  <p>Currently I have one table with following columns:</p>
  
  <pre><code>name  - char(4)
  color - char(6)
  key   - text
  value - real[]
  </code></pre>
  
  <ul>
  <li>primary key consists of multiple fields (name, color, key)</li>
  <li>I have around 300 to 400 names.</li>
  <li>each name has 2000 different colors</li>
  <li>each color has 40000 different key-value pairs</li>
  <li>so each name has 2000 x 40000 = 80000000 rows</li>
  <li>I do not update rows</li>
  <li>I only insert new rows when I fill the database</li>
  </ul>
  
  <p>The query speed is important for me:</p>
  
  <pre><code>SELECT value FROM table WHERE name = x AND color = y AND key = z;
  </code></pre>
  
  <p>Should I create a new table for each name instead of putting all names into one table?</p>
  
  <p>Should I map the 2000 char(6) colors to 2000 smallint colors?</p>
  
  <p>What can I else do to improve the database design?</p>