MySQL 用于国际和多语言目的的数据库建模
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10693508/
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
Database modeling for international and multilingual purposes
提问by udexter
I need to create a large scale DB Model for a web application that will be multilingual.
我需要为多语言的 Web 应用程序创建一个大规模的 DB 模型。
One doubt that I've every time I think on how to do it is how I can resolve having multiple translations for a field. A case example.
我每次想到如何去做时都会有一个疑问，那就是如何解决一个领域的多个翻译问题。一个案例。
The table for language levels, that administrators can edit from the backend, can have multiple items like: basic, advance, fluent, mattern... In the near future probably it will be one more type. The admin goes to the backend and add a new level, it will sort it in the right position.. but how I handle all the translations for the final users?
管理员可以从后台编辑的语言级别表可以有多个项目，例如：基本、高级、流利、重要……在不久的将来可能会增加一种类型。管理员转到后端并添加一个新级别，它将在正确的位置对其进行排序..但是我如何处理最终用户的所有翻译？
Another problem with internationalization of a database is that probably for user studies can differ from USA to UK to DE... in every country they will have their levels (that probably it will be equivalent to another but finally, different). And what about billing?
数据库国际化的另一个问题是，用户研究可能因美国、英国和德国而异……在每个国家/地区，他们都有自己的水平（可能相当于另一个国家，但最终是不同的）。计费呢？
How you model this in a big scale?
你如何大规模建模？
回答by sp00m
Here is the way I would design the database:
这是我设计数据库的方式：
Visualization by DB Designer Fork
The i18ntable only contains a PK, so that any table just has to reference this PK to internationalize a field. The table translationis then in charge of linking this generic ID with the correct list of translations.
该i18n表仅包含一个 PK，因此任何表只需引用此 PK 即可将字段国际化。translation然后该表负责将这个通用 ID 与正确的翻译列表联系起来。
locale.id_localeis a VARCHAR(5)to manage both of enand en_USISO syntaxes.
locale.id_locale是一个VARCHAR(5)管理en和en_USISO 语法。
currency.id_currencyis a CHAR(3)to manage the ISO 4217 syntax.
currency.id_currency是一个CHAR(3)管理ISO 4217 的语法。
You can find two examples: pageand newsletter. Both of these admin-managedentites need to internationalize their fields, respectively title/descriptionand subject/content.
您可以找到两个示例：page和newsletter。这两个由管理员管理的实体都需要将它们的领域分别国际化title/description和subject/content.
Here is an example query:
这是一个示例查询：
select
t_subject.tx_translation as subject,
t_content.tx_translation as content
from newsletter n
-- join for subject
inner join translation t_subject
on t_subject.id_i18n = n.i18n_subject
-- join for content
inner join translation t_content
on t_content.id_i18n = n.i18n_content
inner join locale l
-- condition for subject
on l.id_locale = t_subject.id_locale
-- condition for content
and l.id_locale = t_content.id_locale
-- locale condition
where l.id_locale = 'en_GB'
-- other conditions
and n.id_newsletter = 1
Note that this is a normalized data model. If you have a huge dataset, maybe you could think about denormalizing itto optimize your queries. You can also play with indexes to improve the queries performance (in some DB, foreign keys are automatically indexed, e.g. MySQL/InnoDB).
请注意，这是一个规范化的数据模型。如果您有一个庞大的数据集，也许您可以考虑对其进行非规范化以优化您的查询。您还可以使用索引来提高查询性能（在某些数据库中，外键会自动建立索引，例如MySQL/InnoDB）。
回答by eggyal
Some previous StackOverflow questions on this topic:
之前关于这个主题的一些 StackOverflow 问题：
- Designing database schema for a multi-language website
- What are best practices for multi-language database design?
- What's the best database structure to keep multilingual data?
- Schema for a multilanguage database
- How to use multilanguage database schema with ORM?
Some useful external resources:
一些有用的外部资源：
- Creating multilingual websites: Database Design
- Multilanguage database design approach
- Propel Gets I18n Behavior, And Why It Matters
The best approach often is, for every existing table, create a new table into which text items are moved; the PK of the new table is the PK of the old table together with the language.
最好的方法通常是，对于每个现有表，创建一个新表，将文本项移动到其中；新表的PK就是旧表的PK加上语言。
In your case:
在你的情况下：
The table for language levels, that administrators can edit from the backend, can have multiple items like: basic, advance, fluent, mattern... In the near future probably it will be one more type. The admin goes to the backend and add a new level, it will sort it in the right position.. but how I handle all the translations for the final users?
Your existing table probably looks something like this:
+----+-------+---------+ | id | price | type | +----+-------+---------+ | 1 | 299 | basic | | 2 | 299 | advance | | 3 | 399 | fluent | | 4 | 0 | mattern | +----+-------+---------+
It then becomes two tables:
+----+-------+ +----+------+-------------+ | id | price | | id | lang | type | +----+-------+ +----+------+-------------+ | 1 | 299 | | 1 | en | basic | | 2 | 299 | | 2 | en | advance | | 3 | 399 | | 3 | en | fluent | | 4 | 0 | | 4 | en | mattern | +----+-------+ | 1 | fr | élémentaire | | 2 | fr | avance | | 3 | fr | couramment | : : : : +----+------+-------------+Another problem with internationalitzation of a database is that probably for user studies can differ from USA to UK to DE... in every country they will have their levels (that probably it will be equivalent to another but finally, different). And what about billing?
All localisation can occur through a similar approach. Instead of just moving text fields to the new table, you could move any localisable fields - only those which are common to all locales will remain in the original table.
管理员可以从后台编辑的语言级别表可以有多个项目，例如：基本、高级、流利、重要……在不久的将来可能会增加一种类型。管理员转到后端并添加一个新级别，它将在正确的位置对其进行排序..但是我如何处理最终用户的所有翻译？
您现有的表可能如下所示：
+----+-------+---------+ | id | price | type | +----+-------+---------+ | 1 | 299 | basic | | 2 | 299 | advance | | 3 | 399 | fluent | | 4 | 0 | mattern | +----+-------+---------+
然后它变成了两个表：
+----+-------+ +----+------+-------------+ | id | price | | id | lang | type | +----+-------+ +----+------+-------------+ | 1 | 299 | | 1 | en | basic | | 2 | 299 | | 2 | en | advance | | 3 | 399 | | 3 | en | fluent | | 4 | 0 | | 4 | en | mattern | +----+-------+ | 1 | fr | élémentaire | | 2 | fr | avance | | 3 | fr | couramment | : : : : +----+------+-------------+数据库国际化的另一个问题是，用户研究可能因美国、英国和德国而异……在每个国家/地区，他们都有自己的水平（可能相当于另一个国家，但最终是不同的）。计费呢？
所有本地化都可以通过类似的方法进行。您不仅可以将文本字段移动到新表，还可以移动任何可本地化的字段 - 只有所有语言环境通用的字段才会保留在原始表中。
