What is the best way to create multi-language database? To create localized table for every table is making design and querying complex, in other case to add column for each language is simple but not dynamic, please help me to understand what is the best choose for enterprise applications
创建多语言数据库的最佳方法是什么?为每个表创建本地化的表是设计和查询复杂的，在其他情况下为每种语言添加列是简单的，但不是动态的，请帮助我理解什么是企业应用程序的最佳选择。
5 个解决方案
#1
162
What we do, is to create two tables for each multilingual object.
我们所做的是为每个多语言对象创建两个表。
E.g. the first table contains only language-neutral data (primary key, etc.) and the second table contains one record per language, containing the localized data plus the ISO code of the language.
例如，第一个表只包含语言无关的数据(主键，等等)，而第二个表包含了每一种语言的一个记录，包含了本地化的数据和语言的ISO代码。
In some cases we add a DefaultLanguage field, so that we can fall-back to that language if no localized data is available for a specified language.
在某些情况下，我们添加一个DefaultLanguage字段，这样我们就可以返回到该语言，如果没有针对指定语言的本地化数据。
Example:
例子:
Table "Product":
----------------
ID : int
<any other language-neutral fields>
Table "ProductTranslations"
---------------------------
ID : int (foreign key referencing the Product)
Language : varchar (e.g. "en-US", "de-CH")
IsDefault : bit
ProductDescription : nvarchar
<any other localized data>
With this approach, you can handle as many languages as needed (without having to add additional fields for each new language).
使用这种方法，您可以根据需要处理尽可能多的语言(不必为每种新语言添加额外的字段)。
Update (2014-12-14): please have a look at this answer, for some additional information about the implementation used to load multilingual data into an application.
更新(2014-12-14):请查看这个答案，了解用于将多语言数据加载到应用程序中的实现的一些额外信息。
#2
13
I find this type of approach works for me:
我发现这种方法对我很有效:
Product ProductDetail Country
========= ================== =========
ProductId ProductDetailId CountryId
- etc - ProductId CountryName
CountryId Language
ProductName - etc -
ProductDescription
- etc -
The ProductDetail table holds all the translations (for product name, description etc..) in the languages you want to support. Depending on your app's requirements, you may wish to break the Country table down to use regional languages too.
ProductDetail表包含您希望支持的语言中的所有翻译(产品名称、描述等)。根据您的应用程序的需求，您可能希望将国家表分解为使用区域语言。
#3
12
I recommend the answer posted by Martin.
我推荐马丁的答案。
But you seem to be concerned about your queries getting too complex:
但你似乎担心你的查询变得过于复杂:
To create localized table for every table is making design and querying complex...
为每个表创建本地化的表，是设计和查询复杂的……
So you might be thinking, that instead of writing simple queries like this:
所以你可能会想，不是像这样写简单的查询:
SELECT price, name, description FROM Products WHERE price < 100
...you would need to start writing queries like that:
…您需要开始编写这样的查询:
SELECT
p.price, pt.name, pt.description
FROM
Products p JOIN ProductTranslations pt
ON (p.id = pt.id AND pt.lang = "en")
WHERE
price < 100
Not a very pretty perspective.
这不是一个很好的观点。
But instead of doing it manually you should develop your own database access class, that pre-parses the SQL that contains your special localization markup and converts it to the actual SQL you will need to send to the database.
但是，您应该开发自己的数据库访问类，而不是手工开发，这将预先解析包含您的特殊本地化标记的SQL，并将其转换为您需要发送到数据库的实际SQL。
Using that system might look something like this:
使用这个系统可能是这样的:
db.setLocale("en");
db.query("SELECT p.price, _(p.name), _(p.description)
FROM _(Products p) WHERE price < 100");
And I'm sure you can do even better that that.
我相信你能做得更好。
The key is to have your tables and fields named in uniform way.
关键是要以统一的方式命名您的表和字段。
#4
8
I'm using next approach:
我用下一个方法:
Product
ProductID OrderID,...
ProductID OrderID,……
ProductInfo
ProductID Title Name LanguageID
ProductID标题名称LanguageID
Language
LanguageID Name Culture,....
LanguageID姓名文化,....
#5
2
Martin's solution is very similar to mine, however how would you handle a default descriptions when the desired translation isn't found ?
Martin的解决方案非常类似于我的解决方案，但是如果没有找到所需的翻译，您如何处理默认的描述呢?
Would that require an IFNULL() and another SELECT statement for each field ?
这需要IFNULL()和每个字段的另一个SELECT语句吗?
The default translation would be stored in the same table, where a flag like "isDefault" indicates wether that description is the default description in case none has been found for the current language.
默认的转换将存储在相同的表中，如“isDefault”标记表明该描述是默认的描述，以防当前语言中没有找到该描述。
#1
162
What we do, is to create two tables for each multilingual object.
我们所做的是为每个多语言对象创建两个表。
E.g. the first table contains only language-neutral data (primary key, etc.) and the second table contains one record per language, containing the localized data plus the ISO code of the language.
例如，第一个表只包含语言无关的数据(主键，等等)，而第二个表包含了每一种语言的一个记录，包含了本地化的数据和语言的ISO代码。
In some cases we add a DefaultLanguage field, so that we can fall-back to that language if no localized data is available for a specified language.
在某些情况下，我们添加一个DefaultLanguage字段，这样我们就可以返回到该语言，如果没有针对指定语言的本地化数据。
Example:
例子:
Table "Product":
----------------
ID : int
<any other language-neutral fields>
Table "ProductTranslations"
---------------------------
ID : int (foreign key referencing the Product)
Language : varchar (e.g. "en-US", "de-CH")
IsDefault : bit
ProductDescription : nvarchar
<any other localized data>
With this approach, you can handle as many languages as needed (without having to add additional fields for each new language).
使用这种方法，您可以根据需要处理尽可能多的语言(不必为每种新语言添加额外的字段)。
Update (2014-12-14): please have a look at this answer, for some additional information about the implementation used to load multilingual data into an application.
更新(2014-12-14):请查看这个答案，了解用于将多语言数据加载到应用程序中的实现的一些额外信息。
#2
13
I find this type of approach works for me:
我发现这种方法对我很有效:
Product ProductDetail Country
========= ================== =========
ProductId ProductDetailId CountryId
- etc - ProductId CountryName
CountryId Language
ProductName - etc -
ProductDescription
- etc -
The ProductDetail table holds all the translations (for product name, description etc..) in the languages you want to support. Depending on your app's requirements, you may wish to break the Country table down to use regional languages too.
ProductDetail表包含您希望支持的语言中的所有翻译(产品名称、描述等)。根据您的应用程序的需求，您可能希望将国家表分解为使用区域语言。
#3
12
I recommend the answer posted by Martin.
我推荐马丁的答案。
But you seem to be concerned about your queries getting too complex:
但你似乎担心你的查询变得过于复杂:
To create localized table for every table is making design and querying complex...
为每个表创建本地化的表，是设计和查询复杂的……
So you might be thinking, that instead of writing simple queries like this:
所以你可能会想，不是像这样写简单的查询:
SELECT price, name, description FROM Products WHERE price < 100
...you would need to start writing queries like that:
…您需要开始编写这样的查询:
SELECT
p.price, pt.name, pt.description
FROM
Products p JOIN ProductTranslations pt
ON (p.id = pt.id AND pt.lang = "en")
WHERE
price < 100
Not a very pretty perspective.
这不是一个很好的观点。
But instead of doing it manually you should develop your own database access class, that pre-parses the SQL that contains your special localization markup and converts it to the actual SQL you will need to send to the database.
但是，您应该开发自己的数据库访问类，而不是手工开发，这将预先解析包含您的特殊本地化标记的SQL，并将其转换为您需要发送到数据库的实际SQL。
Using that system might look something like this:
使用这个系统可能是这样的:
db.setLocale("en");
db.query("SELECT p.price, _(p.name), _(p.description)
FROM _(Products p) WHERE price < 100");
And I'm sure you can do even better that that.
我相信你能做得更好。
The key is to have your tables and fields named in uniform way.
关键是要以统一的方式命名您的表和字段。
#4
8
I'm using next approach:
我用下一个方法:
Product
ProductID OrderID,...
ProductID OrderID,……
ProductInfo
ProductID Title Name LanguageID
ProductID标题名称LanguageID
Language
LanguageID Name Culture,....
LanguageID姓名文化,....
#5
2
Martin's solution is very similar to mine, however how would you handle a default descriptions when the desired translation isn't found ?
Martin的解决方案非常类似于我的解决方案，但是如果没有找到所需的翻译，您如何处理默认的描述呢?
Would that require an IFNULL() and another SELECT statement for each field ?
这需要IFNULL()和每个字段的另一个SELECT语句吗?
The default translation would be stored in the same table, where a flag like "isDefault" indicates wether that description is the default description in case none has been found for the current language.
默认的转换将存储在相同的表中，如“isDefault”标记表明该描述是默认的描述，以防当前语言中没有找到该描述。