Globalization of Database Stored Values

Question

We are using resource files (.resx) to translate our .NET 4.5 MVC C# application to different languages. This works great for static text that is located in our views. However, we have values that are pulled from our SQL database that need to be translated as well.

An example: Dropdown list with values that are populated from a table in the database.

What is the best practice for translating these values in the database?

Answer 1

The last multilingual application I designed I've used a table for language, and for each table that had any string types (char, varchar etc`) I had a translation table.

Something along these lines:

CREATE TABLE TblLanguage 
(
   Language_Id int identity(1,1) PRIMARY KEY,
   Language_EnglishName varchar(30),
   Language_NativeName nvarchar(30),
   CONSTRAINT UC_TblLanguage UNIQUE(Language_EnglishName) 
)

CREATE TABLE TblSomeData (
    SomeData_Id int identity(1,1) PRIMARY KEY,
    SomeData_TextColumn varchar(50),
    ....
)

CREATE TABLE TblSomeData_T ( -- _T stands for translation 
   SomeData_T_SomeData_Id int FOREIGN KEY TblSomeData(SomeData_Id),
   SomeData_T_Language_Id int FOREIGN KEY TblLanguage (Language_Id),
   SomeData_T_TextColumn nvarchar(100),
   PRIMARY KEY (SomeData_T_SomeData_Id , SomeData_T_Language_Id)
)

My application had English as it's default (or main) language, so I kept the default language in the base table and only the translations on the translation tables. You could, of course, keep the string values only in the translations table if you want to. Note that this does not take into consideration the different date and number formats for each culture, this is done on the presentation layer.