title: How to design a database for translation dictionary?
body: <p>I have database with words and phrases from for exp. English to 15 other languages, and also for every language in that list to other 15. For one pair they are sort for now in one table like this (en -> de):</p>
  
  <ul>
  <li>id_pair</li>
  <li>word_en </li>
  <li>word_de</li>
  </ul>
  
  <p>What is the best way to create database for that huge list of words and phrases?
  I know that I must separate every primary language from others, and was thinking maybe like this:</p>
  
  <pre><code>ENGLISH
  ID | WORD
  1  | 'dictionary'
  
  GERMAN
  ID | WORD
  1  | 'lexikon'
  2  | 'wörterbuch'
  
  TRANSLATION_EN_DE
  ID_EN | ID_DE
  1     | 1
  1     | 2
  </code></pre>
  
  <p>Is this the best way to normalize DB? But what is with phrases, I need also if someone enter word "dictionay" that this returns also "This dictionary is good" and translation for that. (I know this can find in first table with sql query, is that best way?)</p>
  
  <p>Also need it alphabetically all time, I will have lot of new entry daily, so I can print couple words before and after the word/phases someone looking for translate.</p>
  
  <p>I'm stuck and cant decide what is the best way to optimize it. These db have all together more than 15gb just text based translation, and around 100k daily req, so every ms worth. :)
  Any help will be appreciate, thx!</p>