Language Translation for Tables

Language Translation for Tables

I'm not sure why you're concerned about the number of tables: having fewer tables doesn't automatically mean your database is smaller, more efficient or better designed. Especially if reducing the number of tables increases the complexity of your queries, I would be very careful about doing it.

Anyway, I would go for one translation table per 'base' table. The main reason is that your second solution isn't flexible: if the primary key is not a single integer then it becomes extremely difficult to implement and use. Querying for translations is also more complex, and depending on the size of the table and the data, it may be difficult to index it effectively.

It's not clear why you have a TranslationID on the Products table; usually the relationship is the other way around:

create table dbo.Products (
    ProductCode char(10) not null primary key,
    ProductName nvarchar(50) not null,
    ProductDescription nvarchar(100) not null,
    -- other columns
)

create table dbo.ProductsTranslations (
    ProductCode char(10) not null,
    LanguageCode char(2) not null,
    ProductName nvarchar(50) not null,
    ProductDescription nvarchar(100) not null,
    -- other translations
    constraint FK1 foreign key (ProductCode)
        references dbo.Products (ProductCode),
    constraint FK2 foreign key (LanguageCode)
        references dbo.Languages (LanguageCode),
    constraint PK primary key (ProductCode, LanguageCode)
)

Depending on your toolset and deployment process you may want to generate translation tables directly from the base ones as part of your database build. And you can use views to provide a convenient, 'fully translated' version of the base table.

One interesting question is what language is used for the columns in Products and if they can be used directly when no translation is required. My suggestion would be that all production code should pass a language parameter and take the text from the ProductsTranslations table only, even for English (or whatever your internal corporate language is). That way you can be sure that all 'official' names are found in the same table, and the columns on the base table are there for clarity and completeness of the data model as well as developer convenience and (possibly) internal use on ad hoc reports and so on.

Correct way making a translation table?

Why not to join two tables, master one with id,type,name fields and nested with id,master_id,lang,value. For the given example that will be looking like:

ID      TYPE     NAME
 1      text     question

ID      MASTER_ID      LANG        TRANSLATION
 1      1              en          question
 2      1              nl          vraag

The translation set for one language is given by SQL query:

SELECT * FROM `nested` WHERE `lang` = 'nl'
-- vraag
-- .....

The translation for the given term (e.g. question, having id=1):

SELECT * FROM `nested` WHERE `master_id` = 1 AND `lang` = 'nl' 
-- vraag

Schema for a multilanguage database

What do you think about having a related translation table for each translatable table?

CREATE TABLE T_PRODUCT (pr_id int, PRICE NUMBER(18, 2))

CREATE TABLE T_PRODUCT_tr (pr_id INT FK, languagecode varchar, pr_name text, pr_descr text)

This way if you have multiple translatable columns it would only require a single join to get it + since you are not autogenerating a translationid it may be easier to import items together with their related translations.

The negative side of this is that if you have a complex language fallback mechanism you may need to implement that for each translation table - if you are relying on some stored procedure to do that. If you do that from the app this will probably not be a problem.

Let me know what you think - I am also about to make a decision on this for our next application.
So far we have used your 3rd type.

mysql translation tables with missing language fallback

okay, so the following query can probably be done without a subquery but with a join instead. I'd trust the query optimizer does this, but I wouldn't be too sure.

SELECT l.name as language,
       (SELECT cl.name 
        FROM country_languages cl 
        WHERE cl.country_id=[the wanted country id]
        ORDER BY cl.language_id=l.id DESC,
                 cl.language_id=1 DESC
        LIMIT 1) as country_name
FROM languages l

In this version language_id 1 is used as the prefered fallback, you could probably add more languages in a similar manner. Using FIND_IN_SET instead as a second order criterion would work as well (FIND_IN_SET(cl.language_id,'1,2,3') DESC or whatever order you'd prefer).

Of course this query right now is for a fixed country_id. It could be extended in a similar manner for multiple countries with another join:

SELECT l.name as language,
       (SELECT cl.name 
        FROM country_languages cl 
        WHERE cl.country_id=c.id 
        ORDER BY cl.language_id=l.id DESC,
                 cl.language_id=1 DESC
        LIMIT 1) as country_name
FROM countries c
JOIN languages l

an alternative to subqueries would be to join the country_languages twice, and just select the first one not being null (which is probably one of the cleaner solutions):

SELECT l.name as language, 
       COALESCE(first.name, second.name) as country_name
FROM countries c
JOIN languages l
LEFT JOIN country_languages first ON 
        (first.country_id=c.id AND first.language_id=l.id)
LEFT JOIN country_languages second ON
        (second.country_id=c.id AND second.language_id=1)

If language id 1 is your fallback language. This can be expanded as well to provide multiple fallback languages ...

---

Related Topics