title: Multi language database, with default fallback
body: <p>I have a question that, I know, has been widely discussed about, but in my opinion, there is one aspect that still needs clarification.</p>
  
  <p>I am creating a web-application with a multilanguage database, I already found some good-practices articles (such as  <a href="https://web.archive.org/web/20141015195853/https://www.codeproject.com/Articles/752301/Multilanguage-Database-Design-in-MySQL" rel="noreferrer">this</a>) and answers here in stack overflow like <a href="https://stackoverflow.com/questions/39562/how-do-you-build-a-multi-language-web-site">this</a>.</p>
  
  <p>So I decided to use a main table with the IDs of my items and another table with the translation for each item, let's say, for example</p>
  
  <pre><code>Content
  ContentTranslation
  </code></pre>
  
  <p>or</p>
  
  <pre><code>Category
  CategoryTranslation
  </code></pre>
  
  <p>and so on.</p>
  
  <p>Right now what I'm doing? I just get the items from the database with all the translations and then I iterate over each one to look for the correct translation based on the current user's local, and if I find the correct local I set into the main object that translation for the page to render, otherwise I just get the translation that is flagged as the "default" one.</p>
  
  <p>With large amounts of objects and translations, though, server response time might grow and even if the user might not notice, I don't want this.</p>
  
  <p>So, is there any good practice for this use case too? For example some specific queries that say "pick the translation with locale "it" but if you don't find it just get the one with the "default" flag set?</p>
  
  <p>Now for the technology I'm using Spring MVC with Hibernate and JPA (by means of JPARepository).</p>
  
  <p>My objects all extend a basic Translatable class that I made this way</p>
  
  <pre><code>@MappedSuperclass
  public abstract class Translatable&lt;T extends Translation&gt; extends BaseDTO {
  
      private static final long serialVersionUID = 562001309781752460L;
  
      private String title;
  
      @OneToMany(fetch=FetchType.EAGER, orphanRemoval=true, cascade=CascadeType.ALL)
      private Set&lt;T&gt; translations = new HashSet&lt;T&gt;();
  
      @Transient private T currentLocale;
  
      public void addLocale(T translation, boolean edit) {
          if (!edit)
              getTranslations().add(translation);
      }
  
      public void remLocale(String locale) {
          T tr = null;
          for (T candidate: getTranslations()) {
              if (candidate.getLocale().equals(locale))
                  tr = candidate;
          }
  
          getTranslations().remove(tr);
      }
  
      public T getLocaleFromString(String locale) {
          if (locale == null)
              return null;
          for (T trans: translations) {
              if (trans.getLocale().equals(locale))
                  return trans;
          }
          return null;
      }
  
      public T getDefaultLocale() {
          for (T tr: translations) {
              if (tr.isDefaultLocale())
                  return tr;
          }
          return null;
      }
  
      public Set&lt;T&gt; getTranslations() {
          return translations;
      }
  
      public void setTranslations(Set&lt;T&gt; translations) {
          this.translations = translations;
      }
  
      public T getCurrentLocale() {
          return currentLocale;
      }
  
      public void setCurrentLocale(T currentLocale) {
          this.currentLocale = currentLocale;
      }
  
      public String getTitle() {
          return title;
      }
  
      public void setTitle(String title) {
          this.title = title;
      }
  }
  </code></pre>
  
  <p>So in my controller I iterate over the translations, find the one with the right locale and populate the "currentLocale" property, in my page I just take that and the user gets the correct language as intended.</p>
  
  <p>I hope I've been clear and not messy, but if you need more informations I'll be glad to tell you more.</p>