SQL 多语言数据库设计的最佳实践是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/929410/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What are best practices for multi-language database design?
提问by Arsen Mkrtchyan
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
创建多语言数据库的最佳方法是什么？为每个表创建本地化表使设计和查询变得复杂，在其他情况下为每种语言添加列很简单但不是动态的，请帮助我了解什么是企业应用程序的最佳选择
回答by M4N
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)：请查看此答案，了解有关用于将多语言数据加载到应用程序中的实现的一些附加信息。
回答by Rene Saarsoo
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.
关键是以统一的方式命名您的表和字段。
回答by Nick
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 表包含您想要支持的语言的所有翻译（产品名称、描述等）。根据您的应用程序的要求，您可能希望将 Country 表分解为也使用区域语言。
回答by omoto
I'm using next approach:
我正在使用下一种方法：
Product
产品
ProductID OrderID,...
产品 ID 订单 ID，...
ProductInfo
产品信息
ProductID Title Name LanguageID
ProductID Title Name LanguageID
Language
语
LanguageID Name Culture,....
LanguageID 名称文化，....
回答by doM
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”这样的标志指示描述是否是默认描述，以防当前语言没有找到。
