What's the easiest method to create multi-language database? To produce localized table for each table is making design and querying complex, in other situation to include column for every language is straightforward although not dynamic, help me to know what's the best decide for enterprise programs

What we should do, would be to create two tables for every multilingual object.

E.g. the very first table consists of only language-neutral data (primary key, etc.) and also the second table consists of one record per language, that contains the localized data as well as the ISO code from the language.

In some instances we give a DefaultLanguage area, to ensure that we are able to fall-to that language if no localized information is readily available for a particular language.


Table "Product":


ID                 : int

<any other language-neutral fields>

Table "ProductTranslations"


ID                 : int      (foreign key referencing the merchandise)

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

IsDefault          : bit

ProductDescription : nvarchar

<any other localized data>

With this particular approach, you are able to handle as numerous languages when needed (without needing to add more fields for every new language).

I am using next approach:


ProductID OrderID,...


ProductID Title Title LanguageID


LanguageID Title Culture,....

I suggest the solution published by Martin.

However, you appear to stress about your queries getting too complex:

To produce localized table for each table is making design and querying complex...

So you may be turning over, that rather than writing simple queries such as this:

Choose cost, title, description FROM Items WHERE cost < 100

...you should start writing queries like this:


  p.cost, pt.title, pt.description


  Items p JOIN ProductTranslations pt

  ON (p.id = pt.id AND pt.lang = "en")


  cost < 100

Not really a very pretty perspective.

But rather than doing the work by hand you need to develop your personal database access class, that pre-parses the SQL that consists of your personal localization markup and converts it towards the actual SQL you will have to send towards the database.

By using their system might look something similar to this:


db.query("Choose p.cost, _(p.title), _(p.description)

          FROM _(Items p) WHERE cost < 100")

And I am sure that you can do better still that that.

The bottom line is to possess your tables and fields named in uniform way.

I've found this kind of approach works best for me:

Product     ProductDetail        Country

=========   ==================   =========

ProductId   ProductDetailId      CountryId

- etc -     ProductId            CountryName

            CountryId            Language

            ProductName          - etc -


            - etc -

The ProductDetail table holds all of the translations (for product title, description etc..) within the languages you need to support. Based on your app's needs, you may decide to break the nation table lower to make use of regional languages too.

Martin's option would be much like mine, however how does one handle a default explanations once the preferred translation is not found ?

Would that need an IFNULL() and the other Choose statement for every area ?

The default translation could be saved within the same table, in which a flag like "isDefault" signifies wether that description may be the default description just in case none has been discovered for that current language.

It might rely on what you are particularly attempting to accomplish, but when your database is within unicode it should not appear languages you are putting in it.

Then you definitely handle the localization inside your controller and examine layers...