保存多语言数据的最佳数据库结构是什么? [英] What's the best database structure to keep multilingual data?

问题描述

可能的重复:
多语言数据库的架构

这是一个例子:

[ 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.

这是另一个例子:

[ 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.

这是另一个例子:

[ 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)

问题:难吗?

推荐答案

你的第三个例子实际上就是通常解决问题的方式.很难，但可行.

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)

作为替代方案(不是特别好)，您可以拥有一个字段并将所有翻译合并在一起(例如 XML).

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).

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

这篇关于保存多语言数据的最佳数据库结构是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！