多语言数据库，具有默认回退 [英] Multi language database, with default fallback

问题描述

我有一个问题，我已经广泛讨论过，但在我看来，还有一个方面需要澄清。

我创建一个具有多语言数据库的Web应用程序，我已经找到一些好的实践文章（例如 this ），并在堆栈溢出像这样回答这个。

所以我决定使用一个主表，我的项目的ID和另一个表与每个项目的翻译，让我们例如

 内容
 ContentTranslation 
  

或

 类别
 CategoryTranslation 
  
 
 
 
等等。
 
 
 
现在我在干什么？我只是从数据库中获取所有翻译的项目，然后迭代遍历每一个，以查找基于当前用户的本地的正确翻译，如果我找到正确的本地我设置到主要对象翻译的页面
 
 
 
但是，对于大量的对象和翻译，服务器响应时间可能会增加，并且即使用户可能不会注意到，我不想这样。
 
 
 
那么，这个用例有什么好的做法呢？例如一些特定的查询，说选择翻译与语言环境它，但如果你没有找到它只是得到一个与默认标志设置？
 
 
 
现在对于我的技术我使用Spring MVC与Hibernate和JPA（通过JPARepository）。
 
 
 
我的对象都扩展一个基本的Translatable类，我这样做
  @MappedSuperclass 
 public abstract class可翻译< T extends Translation> extends BaseDTO {
 
 private static final long serialVersionUID = 562001309781752460L; 
 
 private String title; 
 
 @OneToMany（fetch = FetchType.EAGER，orphanRemoval = true，cascade = CascadeType.ALL）
 private Set< T> translations = new HashSet< T>（）; 
 
 @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< T> getTranslations（）{
 return translations; 
} 
 
 public void setTranslations（Set< T> 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; 
} 
} 
  
 
 
因此在我的控制器中迭代翻译，find一个具有正确区域设置并填充currentLocale属性，在我的页面，我只是采取，用户获得正确的语言的预期。
 
 
 
 
解决方案
一些注释前期：
 
 
 
 
 
我的回答是对我的回答这个问题，你添加了一个注释，然后导致这个问题
 
 
在我的答案我使用C＃和MS SQL Server（和我'将忽略任何OR映射特定代码）
 
 
 
 
 
 
 
 
在我的应用程序中，两种不同的方法用于加载多语言数据，具体取决于用例：
 
 
 
管理/ CRUD 
 
 
 
其中用户正在输入数据或编辑现有数据（例如，一个产品及其翻译）我使用相同的方法，你在上面显示你的问题，例如：
  public class Product 
 {
 public int ID {get; set;} 
 public string SKU {get; set;} 
 public IList< ProductTranslation>翻译{get; set;} 
} 
 public class ProductTranslation 
 {
 public string Language {get; set;} 
 public bool IsDefaultLanguage {get; set;} 
 public string标题{get; set;} 
 public string描述{get; set;} 
} 
  
我将让OR-mapper加载产品实例，并附上所有的翻译。 
 
 
 
前端/只读
 
 
 
这种情况下，主要是前端代码，我通常只是显示信息给用户（最好是用户的语言），我使用不同的方法：
 
 
 
首先，我使用不支持/不知道多个翻译的概念的不同的数据模型。相反，它只是当前用户以最佳语言表示的产品：
  public class产品
 {
 public int ID {get; set;} 
 public string SKU {get; set;} 
 
 //特定于语言的属性
 public string标题{get; set;} 
 public string描述{get; set;} 
} 
  
要加载此数据，存储过程）。例如。要使用语言 @Language 加载ID为 @Id 的产品，我将使用以下查询： p> 
 
 
  SELECT 
 p.ID，
 p.SKU，
 - 从请求的翻译中获得标题，描述，
  - 或者如果没有找到，则返回默认值：
 ISNULL（tr.Title，def.Title）Title，
 ISNULL .Description，def.Description）说明
 FROM产品p 
  - 加入请求翻译（如果有）：
 LEFT OUTER JOIN ProductTranslations tr 
 ON p.ID = tr.ProductId AND tr.Language = @Language 
  - 加入产品的默认语言：
 LEFT OUTER JOIN ProductTranslations def 
 ON p.ID = def.ProductId AND def.IsDefaultLanguage = 1 
 WHERE p.ID = @Id 
  
如果翻译是以所请求的语言返回产品的标题和描述因为那种语言存在。如果没有翻译，则会返回默认语言的标题和说明。
 
I have a question that, I know, has been widely discussed about, but in my opinion, there is one aspect that still needs clarification.

I am creating a web-application with a multilanguage database, I already found some good-practices articles (such as  this) and answers here in stack overflow like this.

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
Content
ContentTranslation
or
Category
CategoryTranslation
and so on.

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.

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.

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?

Now for the technology I'm using Spring MVC with Hibernate and JPA (by means of JPARepository).

My objects all extend a basic Translatable class that I made this way
@MappedSuperclass
public abstract class Translatable<T extends Translation> extends BaseDTO {

    private static final long serialVersionUID = 562001309781752460L;

    private String title;

    @OneToMany(fetch=FetchType.EAGER, orphanRemoval=true, cascade=CascadeType.ALL)
    private Set<T> translations = new HashSet<T>();

    @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<T> getTranslations() {
        return translations;
    }

    public void setTranslations(Set<T> 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;
    }
}
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.

I hope I've been clear and not messy, but if you need more informations I'll be glad to tell you more.
解决方案
Some notes upfront:


my answer is more of an addition to my answer to this question, where you added a comment which then led to this question
in my answer I'm using C# and MS SQL Server (and I'll leave out any OR-mapping specific code)




In my applications, I use two different approaches for loading multilingual data, depending on the use case:

Administration / CRUD

In the case where the user is entering data or editing existing data (e.g. a product with its translations) I'm using the same approach as you have shown above in your question, e.g:
public class Product
{
    public int ID {get; set;}
    public string SKU {get; set;}
    public IList<ProductTranslation> Translations {get; set;}
}
public class ProductTranslation
{
    public string Language {get; set;}
    public bool IsDefaultLanguage {get; set;}
    public string Title {get; set;}
    public string Description {get; set;}
}
I.e. I'll let the OR-mapper load the product instance(s) with all their translations attached. I then iterate through the translations and pick the ones needed.

Front-end / read-only

In this case, which is mainly front-end code, where I usually just display information to the user (preferably in the user's language), I'm using a different approach:

First of all, I'm using a different data model which doesn't support/know the notion of multiple translations. Instead it is just the representation of a product in the "best" language for the current user:
public class Product
{
    public int ID {get; set;}
    public string SKU {get; set;}

    // language-specific properties
    public string Title {get; set;}
    public string Description {get; set;}
}
To load this data, I'm using different queries (or stored procedures). E.g. to load a product with ID @Id in the language @Language, I'd use the following query:
SELECT
    p.ID,
    p.SKU,
    -- get title, description from the requested translation,
    -- or fall back to the default if not found:
    ISNULL(tr.Title, def.Title) Title,
    ISNULL(tr.Description, def.Description) Description
  FROM Products p
  -- join requested translation, if available:
  LEFT OUTER JOIN ProductTranslations tr
    ON p.ID = tr.ProductId AND tr.Language = @Language
  -- join default language of the product:
  LEFT OUTER JOIN ProductTranslations def
    ON p.ID = def.ProductId AND def.IsDefaultLanguage = 1
  WHERE p.ID = @Id
This returns the product's title and description in the requested language if a translation for that language exists. If no translation exists, the title and description from the default language will be returned.

                        
这篇关于多语言数据库，具有默认回退的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！