Globalization of stored database values

Question

We use resource files (.resx) to translate our .NET 4.5 MVC C # application into different languages. This works great for static text that is in our views. However, we do have values ​​that are retrieved from our SQL database, which also need to be translated.

Example: a drop-down list with values ​​that are populated from a table in a database.

What is the best practice for translating these values ​​into a database?

Answer 1

The last multilingual application I developed, I used a table for the language, and for each table with any string types (char, varchar, etc.) I had a translation table.

Something like that:

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 default English (or primary), so I saved the default language in the base table and only translations to translation tables. Of course, you could save string values ​​only in the translation table, if you want. Please note that this does not take into account the different date and number formats for each culture, this is done at the presentation level.