What are best practices for multi-language database design? [closed]

What we do, is to create two tables for each multilingual object.

E.g. the first table contains only language-neutral data (primary key, etc.) and the second table contains one record per language, containing the localized data plus the ISO code of the language.

In some cases we add a DefaultLanguage field, so that we can fall-back to that language if no localized data is available for a specified language.

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          : bitProductDescription : nvarchar<any other localized data>

With this approach, you can handle as many languages as needed (without having to add additional fields for each new language).

Update (2014-12-14): please have a look at this answer, for some additional information about the implementation used to load multilingual data into an application.

I recommend the answer posted by Martin.

But you seem to be concerned about your queries getting too complex:

To create localized table for every table is making design and querying complex...

So you might be thinking, that instead of writing simple queries like this:

SELECT price, name, description FROM Products WHERE price < 100

...you would need to start writing queries like that:

SELECT  p.price, pt.name, pt.descriptionFROM  Products p JOIN ProductTranslations pt  ON (p.id = pt.id AND pt.lang = "en")WHERE  price < 100

Not a very pretty perspective.

But instead of doing it manually you should develop your own database access class, that pre-parses the SQL that contains your special localization markup and converts it to the actual SQL you will need to send to the database.

Using that system might look something like this:

db.setLocale("en");db.query("SELECT p.price, _(p.name), _(p.description)          FROM _(Products p) WHERE price < 100");

And I'm sure you can do even better that that.

The key is to have your tables and fields named in uniform way.

I find this type of approach works for me:

Product     ProductDetail        Country=========   ==================   =========ProductId   ProductDetailId      CountryId- etc -     ProductId            CountryName            CountryId            Language            ProductName          - etc -            ProductDescription            - etc -

The ProductDetail table holds all the translations (for product name, description etc..) in the languages you want to support. Depending on your app's requirements, you may wish to break the Country table down to use regional languages too.