How to store localized versions of user input in my database?

Question

I work for a client in a web application that requires localization in three languages ​​(English and 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 problem of localized data entered by the user. For example, an administrator might want to add some new application metadata (for example, a new product category). This should ultimately be translated into all 3 languages, but will initially be entered in any language that the administrator knows. Since this type of data is not static, we store it in a database. Should culture code be added to the primary key to distinguish between different localized versions of the same data? Is there a "best practice" or pattern that I don't know about for this problem?

Answer 1

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

If you always have English, or this is a rollback, you can have a child table for DE, FR, etc. and the main table for the English language. LEFT JOIN and ISNULL will take care of this.

In any case, everything is in order, depending on your exact needs, which, I suspect, are the first. Of course, you need to make sure that you have at least one child line when entering data, for example, in a new product category

Answer 2

I would suggest you create a table to track the Language , and then use the language ID as a foreign key in another table instead of the language code.

 Language(LanguageID, Name) 

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

eg. you save localized text in a table

  LocalizedTextTable(ID,text,LanguageID) 

Answer 3

My solution was to create a row column that stores encoded data for all supported languages. To insert and extract data requires special application logic. A specialized text editor that supports multilingual data also helped.