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

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

I'm using next approach:

Product

ProductID OrderID,...

ProductInfo

ProductID Title Name LanguageID

Language

LanguageID Name Culture,....

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.

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.description
FROM
  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.

Martin's solution is very similar to mine, however how would you handle a default descriptions when the desired translation isn't found ?

Would that require an IFNULL() and another SELECT statement for each field ?

The default translation would be stored in the same table, where a flag like "isDefault" indicates wether that description is the default description in case none has been found for the current language.