What are best practices for multi-language database design?

Question

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

Answer 1

For each multilingual object, you can create two tables.

Here the first table consists only language-neutral data i.e. primary key and the second table contains one record per language, 

which also contains the localized data plus the ISO code of the language.

To fall-back to that language, if no localized data is available for a specified language sometimes we add the DefaultLanguage field.

Example:

Table "Product":

----------------

ID                 : int

<any other language-neutral fields>

Table "ProductTranslations"

---------------------------

ID                 : int      (foreign key referencing the Product)

Language           : varchar  (e.g. "en-US", "de-CH")

IsDefault          : bit

ProductDescription : nvarchar

<any other localized data>

With the mentioned approach, we can handle as many languages as needed (no need to add additional fields for each new language).