What are best practices for multi-language database design?

Question

What is the best way to create multi-language database? To create localized table for every table is making design and querying complex, in other case to add column for each language is simple but not dynamic, please help me to understand what is the best choose for enterprise applications

Answer 1

There are a few ways you could structure this. Let's consider a table my_data with columns (data_id, a, b, c, d, e). Here are some options:

(a) Add a language column to my_data, so each row in the table is now a particular language version of the data.

(b) That could result in duplication, for instance if several of the data columns were not translated between languages. In this case, you might want to put the non-translatable data in one table, and translatable in another. So my_data could have just the untranslated (data_id, c, d, e) and a second table my_data_translations would have columns (data_id, language, a, b).

(c) You could have a single table with all of the string translations, assuming a common base language. So the translation table would have columns (source_string, language, translation), and when outputting data in a non-base language, you would look up each string in the translation table before printing the output. However, you have to be careful about context -- for example, a string "Second" in base language English could mean the second in a sequence or a unit of time, and that could have different translations in some languages.