0

We have a requirement to develope an application that support multiple languages (English, German, French, Russian) and we know, we can use ASP.NET localization to localize static text of a web form but what would be the approach for data localization of a database in SQL server.

for example my database schema is something like this:

Table-Questions

QID-PK

Question

CreatedBy

Table- Answer

AID-PK

QID- FK

Answer

AddedBy

In the above schema,I want the column "question" from Question table and column "Answer" from Answer table should keep localization value.

How do I achive this.

Santosh
  • 33
  • 1
  • 5

2 Answers2

3

Add a Language table:

  • LanguageID-PK
  • LanguageIdentifier (name as accepted by CultureInfo's constructor, e.g. "de" for German)

Add a TranslatedQuestion table:

  • TQID-PK
  • QID-FK
  • LanguageID
  • Translation

Likewise, add a TranslatedAnswer table:

  • TAID-PK
  • AID-FK
  • LanguageID
  • Translation

This way, of course there is nothing in the data model to guarantee that every question/answer has a transation for a given language. But you can always fall back to the untranslated question/answer.

Clafou
  • 15,250
  • 7
  • 58
  • 89
  • Yes, this approach would help but am wondering about joining multiple tables and result set, will get back to you if I have any quires. – Santosh May 01 '12 at 14:49
0

Add a culture column to the table, then repeat the questions and answers in the culture specific format.

cjk
  • 45,739
  • 9
  • 81
  • 112
  • But this will create data redundancy. My goal is to design the database schema in such way that, in near feature if any new language has to be added into it then it can be easily done without much modification . – Santosh Apr 25 '12 at 14:35