Globalize Database ASP.NET MVC

Question

I would appreciate some thoughts here, we have globalized all the static content on the our site without issue but now we have come to the database content that users of the site can enter. For example a user can create a survey with questions and answers and this needs to be globalized as well below is the way I have thought of doing it and the problems:

Solution - Separate tables for specific cultures, for example we have a Survey table with a name field, we move that name field to a SurveyCultures table which allows multiple name fields for each culture we want to support to be added against a survey. This would have to be done for the SurveyQuestions and SurveyAnswers table. (this is the way it seems to have been done on the MVC storefront example)

Problems - This will create a lot of extra tables, one for every table that allows users to enter text that is visible to other users. Also will create a very complicated front end allowing a user to enter the question, then select to enter the question for a different culture. Also leads to problems if they only half translate a survey for a culture.

My question is: Is this the best way to go, is this the way people usually do it and if not what is the recommended practice for this that is usually undertaken?

Any feedback or thoughts would be greatly appreciated as I am going round in circles.

Many thanks

Ofer Zelig · Accepted Answer · 2012-02-23 15:29:33Z

2

Stick with your original tables. It's definitely not correct to add lots of tables. Nightmare to maintain and generally wrong.

In the relevant table(s) add a column (field) named Locale. It would contain for example: en-US, fr-FR, he-IL etc.

Then in your code, query this field and decide how to render your text or where to route it to. Same for inserts/updates.

You can, for example:

Query all questions that are in a given language.
Get a given question in all languages available.
Save every data you have in every language you want, in the very same structure of tables and columns.

etc.

edited Feb 23, 2012 at 15:29

answered Feb 23, 2012 at 15:15

Ofer Zelig

17.5k9 gold badges63 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Philip Kelley · Accepted Answer · 2012-02-23 15:15:24Z

0

I've little experience dealing with this issue, but the way I would approach it would be to add a "Language" column to existing tables. This might lead to something like:

Table: Survey
Columns: Unique identifier, date created, owner, etc.

Table: SurveyTitle
Columns: Language, Title, IsReleased (flag indicating whether survey in this language is ready for use), etc.

Table: SurveyQuestion
Columns: Language, QuestionNumber, QuestiontText etc.

Table: Language (this is a lookup table, used for foreign keys on all those Language columns)
Columns: Language (Id), Description, etc.

(All those etc. are fill-in for whatever you need that is specific to your requirements) This way you have:

A list of surveys
A list languages that the survey is in (with flags for on/off, or ready/still being written)
A list of questions in each language
A list of all languages that are supported

answered Feb 23, 2012 at 15:15

Philip Kelley

40.5k11 gold badges62 silver badges93 bronze badges

1 Comment

user351711 Over a year ago

I went with something very much along these lines as it gave me the most control. Many thanks.

Collectives™ on Stack Overflow

Globalize Database ASP.NET MVC

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related