title: Sqlite3 remove redundancy with extra reference table
body: <p>So let's say you inherited an SQLITE3 database that has just one table like so:</p>
  
  <pre><code>CREATE TABLE data (id integer, name text)
  </code></pre>
  
  <p>And it looks like so:</p>
  
  <pre><code>------- DATA -------
  89074352  red
  12344112  red
  47298489  blue
  34444333  blue
  23453245  red
  10000001  yellow
  ...
  --------------------
  </code></pre>
  
  <p>So the text elements and very redundant. And imagine that the text elements are huge instead of just one word. You would want to avoid that by making a new table that contains every text element only once. The final state of the database would have two tables that look like this:</p>
  
  <pre><code>CREATE TABLE text_keys ("key" integer primary key autoincrement, "name" text not null)
  ------- TEXT_KEYS -------
  1   red
  2   blue
  3   yellow
  ...
  -------------------------
  
  CREATE TABLE data ("id" integer, "key" integer references text_keys)
  ------- DATA -------
  89074352  1
  12344112  1
  47298489  2
  34444333  2
  23453245  1
  10000001  3
  ...
  --------------------
  </code></pre>
  
  <p>What commands would you type in SQLITE3 to go from the first state to the final state of the database ? I've made some research, but have not been able to solve this one.</p>