保存多语言数据的最佳数据库结构是什么?
可能的重复:
多语言数据库的架构
这是一个例子:
[ 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>
相关文章