title: Best practice in designing relational database for spoken language variant
body: <p>Well, here is a small problem.</p>
  
  <p>I am to design a web application database to use what is, basically, one spoken language. However this language has two alphabets (Latin &amp; Cyrillic) and three spoken variants that actually have only some minor differences (I think there is several hundred words that are slightly different). To make matters more difficult, database and administration interface will be used for two applications, one of which will use both scripts and two spoken variants for all articles (as well as interface) and other will use only one (Latin). </p>
  
  <p>I wrote an algorithm for latin-to-cyrillic-and-back "translation", that should make an input easier to user, but now I am facing some database/application normalization issues I am in doubt about.</p>
  
  <p>(1) I could treat this as a spoken language problem and create database accordingly.</p>
  
  <pre><code>| 'variant' | [id, name] #language variant
  | 'article' | [id, &lt;all variant non-dependent fields&gt;] # such as image, relations, etc
  | 'article_variant' | [id, fk_language_variant_id, fk_article_id, &lt;all variant dependent fields&gt;] #title, body, etc
  | 'article_has_variants' | [id, fk_article_id, fk_article_variant_id]
  </code></pre>
  
  <p>However due to fact that most of the database entries in 'article_variant' would contain same information it seems redundant.</p>
  
  <p>(2) I could do it using 'article' table and word pairs with "latin-to-cyrillic-and-back" algorithm in order to display it to user solving it on application level. Then my database would look something like this:</p>
  
  <pre><code>| 'variant' | [id, name] #language variant
  | 'article' | id, &lt;all fields&gt;
  | 'words' | id, word
  | 'word_pairs' | id, fk_word_id, fk_word_id
  </code></pre>
  
  <p>This solution looks perfect to me, but I am worried about impact it would have on application performance due to fact (a) it has to go trough an entire article every time it is displayed on language variant different than default for each word pair and (b) after that it has to convert each letter of the alphabet (+few more times due to complex letter conversion such are nj -> њ ) to it's pair.</p>
  
  <p>Any thoughts?</p>