I'm developing a multilanguage software. As far as the application code goes, localizability is not an issue. We can use language specific resources and have all kinds of tools that work well with them.
But what is the best approach in defining a multilanguage database schema? Let's say we have a lot of tables (100 or more), and each table can have multiple columns that can be localized (most of nvarchar columns should be localizable). For instance one of the tables might hold product information:
CREATE TABLE T_PRODUCT ( NAME NVARCHAR(50), DESCRIPTION NTEXT, PRICE NUMBER(18, 2) )
I can think of three approaches to support multilingual text in NAME and DESCRIPTION columns:
Separate column for each language
When we add a new language to the system, we must create additional columns to store the translated text, like this:
CREATE TABLE T_PRODUCT ( NAME_EN NVARCHAR(50), NAME_DE NVARCHAR(50), NAME_SP NVARCHAR(50), DESCRIPTION_EN NTEXT, DESCRIPTION_DE NTEXT, DESCRIPTION_SP NTEXT, PRICE NUMBER(18,2) )
Translation table with columns for each language
Instead of storing translated text, only a foreign key to the translations table is stored. The translations table contains a column for each language.
CREATE TABLE T_PRODUCT ( NAME_FK int, DESCRIPTION_FK int, PRICE NUMBER(18, 2) ) CREATE TABLE T_TRANSLATION ( TRANSLATION_ID, TEXT_EN NTEXT, TEXT_DE NTEXT, TEXT_SP NTEXT )
Translation tables with rows for each language
Instead of storing translated text, only a foreign key to the translations table is stored. The translations table contains only a key, and a separate table contains a row for each translation to a language.
CREATE TABLE T_PRODUCT ( NAME_FK int, DESCRIPTION_FK int, PRICE NUMBER(18, 2) ) CREATE TABLE T_TRANSLATION ( TRANSLATION_ID ) CREATE TABLE T_TRANSLATION_ENTRY ( TRANSLATION_FK, LANGUAGE_FK, TRANSLATED_TEXT NTEXT ) CREATE TABLE T_TRANSLATION_LANGUAGE ( LANGUAGE_ID, LANGUAGE_CODE CHAR(2) )
There are pros and cons to each solution, and I would like to know what are your experiences with these approaches, what do you recommend and how would you go about designing a multilanguage database schema.