用于国际和多语言目的的数据库建模 [英] Database modeling for international and multilingual purposes

问题描述

我需要为多语言的网络应用程序创建一个大规模的数据库模型。

I need to create a large scale DB Model for a web application that will be multilingual.

我怀疑我每次都会考虑如何做它是如何解决有一个字段的多个翻译。一个案例。

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.

管理员可以从后端编辑的语言级别表可以有多个项目：basic，advance，fluent，mattern ...不久的将来可能会再有一种类型。管理员转到后端并添加一个新级别，它会将其排序在正确的位置..但是如何处理最终用户的所有翻译？

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?

如何以大规模建模？

推荐答案

这里是我设计数据库的方式：

Here is the way I would design the database:

可视化 DB Designer Fork

i18n 表仅包含PK ，所以任何表只是必须引用这个PK国际化一个字段。 translation 表负责将此通用ID与正确的翻译列表链接。

The i18n table only contains a PK, so that any table just has to reference this PK to internationalize a field. The table translation is then in charge of linking this generic ID with the correct list of translations.

locale.id_locale 是一个 VARCHAR（5）来管理 en 和 en_US ISO语法。

locale.id_locale is a VARCHAR(5) to manage both of en and en_US ISO syntaxes.

currency.id_currency 是 CHAR（3）管理 ISO 4217语法。

您可以找到两个示例：页和通讯。这两个管理员管理的 都需要将其字段国际化 title / description 和 subject / content 。

You can find two examples: page and newsletter. Both of these admin-managed entites need to internationalize their fields, respectively title/description and subject/content.

这是一个查询示例：

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.lb_locale = 'en_GB'

  -- other conditions
  and n.id_newsletter = 1

注意，这是一个标准化的数据模型。如果您有巨大的数据集，也许您可​​以考虑取消规范化以优化您的查询。您还可以使用索引来提高查询性能（在某些数据库中，外键会自动编入索引，例如 MySQL / InnoDB ）。

Note that this is a normalized data model. If you have a huge dataset, maybe you could think about denormalizing it to 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).

这篇关于用于国际和多语言目的的数据库建模的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！