What's the best database structure to keep multilingual data?
database, localization, multilingual, mysql, translation
Solution
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>
Problem
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. 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: hard?