保留多语言数据的最好的数据库结构是什么? [英] What's the best database structure to keep multilingual data?
问题描述
可能重复:
多语言数据库的模式
/ p>
[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)
问题:每种新语言都需要修改表结构。
这是另一个例子:
[products-en_us]
id(INT)
name(VARCHAR)
description(VARCHAR)
price(DECIMAL)
[products-es_es]
id INT)
name(VARCHAR)
description(VARCHAR)
price(DECIMAL)
问题:每种新语言都需要创建新表,price字段在每个表中都重复。
另一个例子:
[语言]
pre>
id(INT)
name(VARCHAR)
[products]
id(INT)
price(DECIMAL)
[translation]
id(INT,PK)
模型(VARCHAR)//产品
字段(VARCHAR)//名称
language_id(INT,FK)
text(VARCHAR)
问题: fuc * ing hard?
解决方案你的第三个例子实际上是通常解决问题的方式。
从翻译表中删除对产品的引用,并在您需要的地方提供翻译(反之亦然)。
[products]
id(INT)
price(DECIMAL)
title_translation_id(INT,FK)
[翻译]
id(INT,PK)
neutral_text(VARCHAR)
- 其他可能有用的属性(日期,创建者等)
[translation_text]
translation_id(INT,FK)
language_id(INT,FK)
text(VARCHAR)
作为一个替代方法(不是特别好),你可以有一个单独的字段,并保持所有的翻译合并在一起(例如XML)。
< translation>
< en>供应商< / en>
< de> Lieferant< / de>
< fr> Fournisseur< / fr>
< / translation>
Possible Duplicate:
Schema for a multilanguage databaseHere'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.
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?
解决方案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).
<translation> <en>Supplier</en> <de>Lieferant</de> <fr>Fournisseur</fr> </translation>
这篇关于保留多语言数据的最好的数据库结构是什么?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!