Database Design: how to support multi-lingual site?

3
votes

Suppose I have the table:

TABLE: product
=================================================================
| product_id | name           | description                     |
=================================================================
| 1          | Widget 1       | Really nice widget. Buy it now! |
-----------------------------------------------------------------

If I want to provide multi-lingual support, what's the best approach to do that?

Possible solutions:

  1. Add a "language" column to the above table; that'll indicate the language the particular record is. (I don't think this is an option for me since other tables will be using product.product_id as its FK.)
  2. Remove any translatable columns in product table (in the above example, product.name and product.description) and put it in a separate table with a "language" column. That new table will use product.product_id as a FK. (I won't be supporting multi-languages in the first version of my application. This solution means I would have to do an extra JOIN just to get the values of the initially supported language.)
  3. Something else that I didn't consider?
5
mysqldatabasedatabase-designdata-modeling
Solution 2 also means you'd have to ensure that every product has a description record for every language -- otherwise, products may disappear on you if you use inner joins. – phoog
A left join would allow the products to still appear. Although you may not want products to appear if you don't have a translation. It all depends on the use case. – Alan Geleynse
If product is the only table you need to translate, then option 2 is fine. Otherwise you will need a new translation table for each entity that requires translation. – Phil Sandler

5 Answers

4
votes

I would go with solution 2.

This option will minimize your work for your first version, and will reduce repetition between the columns that do not change.

It does require an extra JOIN, but it is a simple key join so it will not affect your performance much.

4
votes

I would go with a third alternative which is a hybrid of your existing design and solution #2. The columns that exist in your table now represent your "neutral" or default language. You would then add a table for each enttity that need translated values which would contain the PK of the main table, a key for the language code and a column for each value in the parent table that needed a translation.So we might have

Create Table product_translations
    (
    product_id int not null References product( id )
    , language_code varchar(5) not null
    , name ...
    , description ...
    , Primary Key ( product_id, language_code )
    ...
    )

Your queries would then look like:

Select P.product_id
    , Coalesce( PT.name, P.name ) As Name
From product As P
    Left Join product_translations As PT
        On PT.product_id = P.product_id
            And PT.language_code = 'en-UK'

It does mean every query where you pull product information will need a Left Join to the translations table and then decide what to do if there is no translation value: return the default language term (as I have in my example above) or return null.

1
votes

I would use a little modified version of (2). I think it's ok not to remove your name and description columns from product table - in this case you will always have default values for product if a localized version does not exist.

0
votes

I think option #2 creates the 1:M relationship in the wrong direction. Now you need a translation table for any base table that requires translation.

A recent solution I used, applied to your sample:

language
--------------
language_id
language_name

language_key
-------------
language_key_id
lang_key_name (or description)

translation
-------------------
translation_id
language_id
language_key_id
translation_text

product
--------------
product_id
product_name_key_id
product_description_key_id

You also may want to add something like "default translation" to the key table, so that adding a translation is optional, and has a fallback value.

Finally, consider caching this data in your app, since it is not likely to change often.

0
votes

Are you certain to always have all content at least in English? Is the number of translations quite small? If so, I'd say keep product as is, and add a similar table for each translation:

TABLE: product_fi
=================================================================
| product_id | name           | description                     |
=================================================================
| 1          | Vimpain 1      | Tosi kiva vimpain. Osta heti!   |
-----------------------------------------------------------------

Then you can OUTER JOIN and COALESCE to get specific language versions when they exist but fall back on English ones:

SELECT
  coalesce(fi.name, en.name) AS name,
  coalesce(fi.description, en.description) AS description
FROM product en
LEFT OUTER JOIN product_fi fi ON en.product_id = fi.product_id
WHERE en.product_id = 1