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

本文是小编为大家收集整理的关于多语言数据库,有默认回退功能的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我有一个问题,我知道,已经广泛讨论过,但是我认为,仍然需要澄清一个方面.

我正在使用多语言数据库创建一个Web应用程序,我已经找到了一些良好的practices文章(例如

有了大量的对象和翻译,服务器响应时间可能会增长,即使用户可能不注意,我也不想要.

那么,这种用例也有什么好习惯吗?例如,一些特定的查询说"选择" it it it it it it it"的翻译",但是如果您没有找到它的"默认"标志集?

现在,对于我正在使用Hibernate和JPA的Spring MVC的技术(通过JParepository).

我的对象都扩展了我这样做的基本可翻​​译类

@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;
    }
}

因此,在我的控制器中,我迭代翻译,找到正确的语言环境并填充" CurrentLocale"属性,在我的页面中,我只是采用它,用户获得了正确的语言.

我希望我很清楚而不是混乱,但是如果您需要更多信息,我会很高兴告诉您更多.

推荐答案

一些注释前期:


在我的应用程序中,我使用两种不同的方法来加载多语言数据,具体取决于用例:

管理/crud

在用户输入数据或编辑现有数据的情况下(例如带有翻译的产品),我使用的方法与您在问题中所示的相同方法,例如:

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;}
}

即.我将让OR-MAPPER加载产品实例及其所有翻译.然后,我迭代翻译并选择所需的翻译.

前端/仅读取

在这种情况下,这主要是前端代码,我通常只向用户显示信息(最好使用用户的语言),我使用其他方法:

首先,我正在使用不同的数据模型,该模型不支持/知道多个翻译的概念.相反,它只是当前用户的"最佳"语言中产品的表示:

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;}
}

要加载此数据,我正在使用不同的查询(或存储过程).例如.要在语言中加载具有ID @Id的产品,我将使用以下查询:

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

如果存在该语言的翻译,则以请求的语言返回产品的标题和描述.如果不存在翻译,则将返回默认语言的标题和描述.

其他推荐答案

使用共享表用于所有表的所有可翻译字段

在上面的方法中,翻译表是父表的扩展.因此,ProductTranslation具有所有可翻译的产品字段.这是一种整洁而快速的方法,也是一个很好的方法.

但是有一个缺点(不确定是否可以称为缺点).如果更多的桌子需要可翻译的字段,则需要许多新的桌子.根据我的经验,我们采取了不同的方法.我们创建了一个用于翻译的通用表和一个链接表,以链接转换到父表的可翻译字段.

因此,我将使用上一个产品的示例,该示例具有两个字段标题和描述,这些字段和描述可翻译以解释我们的方法.还要考虑另一个带有字段名称和描述的表productsategory,也需要翻译.

Product
(
   ID: Integer
   SKU: String
   titleID: Integer // ID of LocalizableText record corresponding title
   descriptionID: Integer // ID of LocalizableText record corresponding description
)

ProductCategory
(
   ID: Integer
   nameID: Integer // ID of LocalizableText record corresponding name
   descriptionID: Integer // ID of LocalizableText record corresponding description
)

LocalizableText // This is nothing but a link table
{
    ID: Integer
}

Translations //This is where all translations are stored.
{
    ID: Integer
    localizableTextID: Integer
    language: String
    text: String
}

要加载此数据,我正在使用不同的查询(修改了上述查询).例如.要在语言中加载带有ID @ID的产品,我将使用以下查询

SELECT
    p.ID,
    p.SKU,
    -- get title, description from the requested translation,
    -- or fall back to the default if not found:
    Title.text Title,
    description.text Description
  FROM Products p
  -- join requested translation for title, if available:
  LEFT OUTER JOIN Translations title
    ON p.titleID = title.localizableTextID
       AND title.Language = @Language
  -- join requested translation for description, if available:
  LEFT OUTER JOIN Translations description
    ON p.descriptionID = description.localizableTextID
       AND description.Language = @Language
  WHERE p.ID = @Id

此查询基于以下假设:产品的各个字段没有默认翻译

类似的查询可用于从productCategory获取记录

本文地址:https://www.itbaoku.cn/post/597441.html

问题描述

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.

其他推荐答案

Using common shared table for all translatable fields of all tables

In the above approach the translation table is an extension of the parent table. Hence ProductTranslation has all the translatable fields of Product. It is a neat and quick approach and nice one as well.

But there is one disadvantage (not sure if it can be called disadvantage). If many more tables require translate-able fields, that many new tables are required. From my experience we took a different approach. We created a generic table for translation and a link table to link translations to the translate-able fields of the parent table.

So I'm going to use the previous example of Product which has two fields title and description that are translate-able to explain our approach. Also consider another table ProductCategory with fields name and description that also require translations.

Product
(
   ID: Integer
   SKU: String
   titleID: Integer // ID of LocalizableText record corresponding title
   descriptionID: Integer // ID of LocalizableText record corresponding description
)

ProductCategory
(
   ID: Integer
   nameID: Integer // ID of LocalizableText record corresponding name
   descriptionID: Integer // ID of LocalizableText record corresponding description
)

LocalizableText // This is nothing but a link table
{
    ID: Integer
}

Translations //This is where all translations are stored.
{
    ID: Integer
    localizableTextID: Integer
    language: String
    text: String
}

To load this data, I'm using different queries (modified the above). 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:
    Title.text Title,
    description.text Description
  FROM Products p
  -- join requested translation for title, if available:
  LEFT OUTER JOIN Translations title
    ON p.titleID = title.localizableTextID
       AND title.Language = @Language
  -- join requested translation for description, if available:
  LEFT OUTER JOIN Translations description
    ON p.descriptionID = description.localizableTextID
       AND description.Language = @Language
  WHERE p.ID = @Id

This query is based on the assumption that individual fields of Product does not have a default translation

Similar query can be used to fetch records from ProductCategory