Language Translation for Tables

Question

I know most people use the approach below and create a translation table for the specific table that needs translations but this can amount to a load of tables.

CREATE TABLE Product
(
     Product_id
    ,ProductTrans_id -- FK
)

CREATE TABLE ProductTranslation
(
     ProductTrans_id
    ,Product_id
    ,Name
    ,Descr
    ,lang_code
)

Would the below approach be viable? Say you have lots of tables where more than 1 column needs translating. Could you do the following nad keep all translations in 1 table? I suppose this table would grow hugely in size over time.

   CREATE TABLE translation_entry (
          translation_id        int,
          language_id           int,
          table_name            nvarchar(200),
          table_column_name     nvarchar(200),
          table_row_id          bigint,
          translated_text       ntext
        )

    CREATE TABLE translation_language (
          id int,
          language_code CHAR(2)
        )   

So using the second approach you would get the text like so

select 
     product.name 
    ,translation_entry.translated_text
from product
inner join  translation_entry on product.product_id = translation_entry.table_row_id 
and translation_entry.table_name = 'Product' and translation_entry.table_column_name = 'Name'
and language_id = 3

Answer 1

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.