保存多语言数据的最佳数据库结构是什么?(复制)

Possible Duplicate:
Schema for a multilanguage database

可能的重复:多语言数据库的模式。

Here's an example:

这里有一个例子:

[ products ]
id (INT)
name-en_us (VARCHAR)
name-es_es (VARCHAR)
name-pt_br (VARCHAR)
description-en_us (VARCHAR)
description-es_es (VARCHAR)
description-pt_br (VARCHAR)
price (DECIMAL)

The problem: every new language will need modify the table structure.

问题:每种新语言都需要修改表结构。

Here's another example:

这是另一个例子:

[ products-en_us ]
id (INT)
name (VARCHAR)
description (VARCHAR)
price (DECIMAL)

[ products-es_es ]
id (INT)
name (VARCHAR)
description (VARCHAR)
price (DECIMAL)

The problem: every new language will need the creation of new tables and the "price" field is duplicated in every table.

问题是:每一种新语言都需要创建新的表，并且每个表都要重复“price”字段。

Here's another example:

这是另一个例子:

[ languages ]
id (INT)
name (VARCHAR)

[ products ]
id (INT)
price (DECIMAL)

[ translation ]
id (INT, PK)
model (VARCHAR) // product
field (VARCHAR) // name
language_id (INT, FK) 
text (VARCHAR)

The problem: fuc*ing hard?

问题:fuc * ing难吗?

8 个解决方案

#1

Your third example is actually the way the problem is usually solved. Hard, but doable.

你的第三个例子实际上是解决问题的方式。困难,但可行的。

Remove the reference to product from the translation table and put a reference to translation where you need it (the other way around).

从翻译表中删除对产品的引用，并在需要的地方放置对翻译的引用(反之亦然)。

[ products ]
id (INT)
price (DECIMAL)
title_translation_id (INT, FK)

[ translation ]
id (INT, PK)
neutral_text (VARCHAR)
-- other properties that may be useful (date, creator etc.)

[ translation_text ]
translation_id (INT, FK)
language_id (INT, FK) 
text (VARCHAR)

As an alternative (not especially a good one) you can have one single field and keep all translations there merged together (as XML, for example).

作为一种替代(不是很好)，您可以有一个字段，并将所有翻译合并在一起(例如XML)。

<translation>
  <en>Supplier</en>
  <de>Lieferant</de>
  <fr>Fournisseur</fr>
</translation>

#2

Similar to method 3:

类似于方法3:

[languages]
id (int PK)
code (varchar)

[products]
id (int PK)
neutral_fields (mixed)

[products_t]
id (int FK)
language (int FK)
translated_fields (mixed)
PRIMARY KEY: id,language

So for each table, make another table (in my case with "_t" suffix) which holds the translated fields. When you SELECT * FROM products, simply ... LEFT JOIN products_t ON products_t.id = products.id AND products_t.language = CURRENT_LANGUAGE.

因此，对于每个表，创建另一个表(在我的例子中是“_t”后缀)，它保存转换后的字段。当你从产品中选择*时，简单地…在products_t上留下JOIN products_t。id =产品。id和products_t。语言= CURRENT_LANGUAGE。

Not that hard, and keeps you free from headaches.

没有那么难，让你免于头痛。

#3

In order to reduce the number of JOIN's, you could keep separate the translated and non translated in 2 separate tables :

为了减少JOIN的数量，您可以将已翻译的和未翻译的放在两个单独的表中:

[ products ]
id (INT)
price (DECIMAL)

[ products_i18n ]
id (INT)
name (VARCHAR)
description (VARCHAR)
lang_code (CHAR(5))

#4

At my $DAYJOB we use gettext for I18N. I wrote a plugin to xgettext.pl that extracts all English text from the database tables and add them to the master messages.pot.

在我的工作中，我们用的是I18N的gettext。我写了一个插件到xgettext。从数据库表中提取所有英语文本并将其添加到主消息。pot。

It works very well - translators deal with only one file when doing translation - the po file. There's no fiddling with database entries when doing translations.

它工作得很好——译者在翻译时只处理一个文件——po文件。在进行翻译时，不需要修改数据库条目。

#5

[languages] id (int PK) code (varchar)

[语言]id (int PK)码(varchar)

[products]
id (int PK)
name
price
all other fields of product
id_language ( int FK )

I actually use this method, but in my case, it's not in a product point of view, for the various pages in my CMS, this work's quite well.

我实际上使用了这个方法，但是在我的例子中，它不是从产品的角度来看，对于我的CMS中的各个页面，这个工作是很好的。

If you have a lot of products it might be a headache to update a single one in 5 or 6 languages... but it's a question of working the layout.

如果你有很多产品，用5或6种语言更新一个产品可能会让你头疼……但这是一个布局的问题。

#6

What about fourth solution?

第四个解决方案呢?

[ products ]
id (INT)
language (VARCHAR 2)
name (VARCHAR)
description (VARCHAR)
price (DECIMAL)
*translation_of (INT FK)*

*Translation_of* is FK of it self. When You add default language *translation_of* is set to Null. But when you add second language *translation_of* takes primary produkt language id.

*的翻译是它自己的FK。当您添加默认语言*translation_of*被设置为Null。但是当您添加第二语言*translation_of*时，需要主要的produkt语言id。

SELECT * FROM products WHERE id = 1 AND translation_of = 1

In that case we get all translations for product with id is 1.

在这种情况下，id为1的产品的所有翻译都是1。

SELECT * FROM products WHERE id = 1 AND translation_of = 1 AND language = 'pl'

We get only product in Polish translation. Without second table and JOINS.

我们只有波兰翻译的产品。没有第二个表和连接。

#7

-1

Have many to many relationship.

有很多关系。

You have your data table, languages table and a data_language table.

您有您的数据表、语言表和data_language表。

In the data_language table you have

在data_language表中

id, data_id, language_id

id、data_id language_id

I think that might work best for your.

我想这对你来说可能是最好的。

#8

-2

We use this concept for our webiste (600k views per day) and (maybe surprisingly) it works. Sure along with caching and query optimalization.

我们将这个概念用于webiste(每天60万次查看)，并且(可能令人惊讶)它可以工作。当然还有缓存和查询优化。

[attribute_names]
id (INT)
name (VARCHAR)

[languages_names]
id (INT)
name (VARCHAR)

[products]
id (INT)
attr_id (INT)
value (MEDIUMTEXT)
lang_id (INT)

#1