what is best practices for multilanguage database design?

Question

What is the best way to create multilanguage 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
Thanks a lot!

Answer 1

Use a collation that should be capable of handling the languages you expect.... like utf-8

for mysql this would be utf8_general_ci though it might depend on you particular dbms

Answer 2

It may depend on what you're specifically trying to accomplish, but if your database is in unicode it shouldn't matter what languages you're putting into it.

Then you handle the localization in your controller and view layers...

Answer 3

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 langauge-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 this approach, you can handle as many languages as needed (without having to add additional fields for each new language).

Answer 4

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.

Answer 5

I'm using next approach:

Product

ProductID OrderID,...

ProductInfo

ProductID Title Name LanguageID

Language

LanguageID Name Culture,....

Answer 6

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.

Answer 7

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.