CODEWITHSUNDEEP
asp.netsql-serverlocalization
Kevin Babcock
Kevin Babcock

Reputation: 10247

How should I store localized versions of user-entered data in my database?

I am working for a client on a web app that requires localization in 3 languages (English and 2 others). I understand how to use resources in an ASP.NET application to display localized versions of static data. However, I am not sure how to approach the issue of localized user-entered data. For example, an administrator may want to add some new metadata the application (e.g. a new product category). This will eventually need to be translated into all 3 languages, but it will initially be entered in whatever language the administrator knows. Since this kind of data is not static, we store it in the database. Should we add a culture code to the primary key to differentiate different localized versions of the same data? Is there a "best practice" or pattern I'm not aware of for this kind of problem?

Upvotes: 4

Views: 607

Answers (3)

Muhammad Akhtar
Muhammad Akhtar

Reputation: 52241

I would suggest you make a table to track the Language and then use the languageID as a foreign key in the other table instead of language code.

Language(LanguageID, Name)

And then in the other tables use that LanguageID as a foreign key.

e.g. you are storing localized text in the table 

  LocalizedTextTable(ID,text,LanguageID)

Upvotes: 1

Marko
Marko

Reputation: 5552

My solution was to create a string column which holds encoded data for all supported languages. Special application logic is required to insert and extract the data. Specialized text editor supporting multi-lingual data helped a lot too.

Upvotes: 0

gbn
gbn

Reputation: 432271

Have a child table your your entity, with a composite PK of MainItemID and LanguageCode (EN, DE, FR etc). This child table stores your language specific text.

If you always have English, or it is a fallback then you could have the child table for DE, FR etc and the main table for English. A LEFT JOIN and ISNULL will take care of this.

Either way is OK depending on your exact needs which I suspect is the first one. Of course, you'd need to ensure you have at least one child row on data entry of, say, a new product category

Upvotes: 2

Related Questions