Storage and presentation of database data across multiple languages

Question

Apologies if this has been asked before. After extensive searching I cannot find anything that serves as a solution (a similar answer is posted below). I am afraid this is an open-ended (broad) question simply because I don't know what I don't know. Please don't downvote if not suitable for this site; some pointers in the right direction would be much appreciated instead.

We have a web application running on SQL Server 2012 with ASP.Net. There are around 150 tables and 600 stored procedures. We have successfully managed to manage data across three different languages historically, fortunately because each client only required data in their own language.

For example:

1. Client enters database data from web forms in their own language (using nvarchar/UTF16 across all respective fields)
2. ASP.Net uses resource files to provide the interface in the local language

We now have a prospect who requires a standard set of global database data to be available in 20 languages. While the interface translation (although a big job) isn't technically difficult, I am struggling to see how this is achieved in SQL Server, and would appreciate any input from people who have achieve this historically. Given that enterprise-level companies such as Oracle and SAP must do this regularly (I assume), I'm sure it's possible somehow.

A related answer, albeit for MySQL seems to be feasible...

Normally I use two tables, main table has default language, just in case you forget to add corresponding values into translations tables.

create table books ( book_id int, book_name varchar(200), description varchar(500) ) create table books_language ( book_id int, language_id vachar(10), book_name varchar(200), description varchar(500) )

This returns all records including default language.

select book_id, isnull(books_language.laguage_id, 'default') isnull(books_language.name, books.name) as name, isnull(books_language.description, books.description) as description from books left join books_language on books.book_id = books_language.book_id

...but I can't figure out the following practicalities, assuming my database doubles to 300 tables in size.

1. Because each user requires data in their own specific locale, how do you pass every query without doing this dynamically, for the application to bind to the respective display fields?
2. How do you handle a table having 20 data fields, rather than just one as in the example?
3. How do you deal with text searches, when the table required for the data may differ every time?

Or is there a better way entirely?

I've tried widgets such as Google Translator and Bing, but they don't work properly. While they both struggle with data from partial page reloads, they just translate everything, and often not very well. I've also read about plug-in software, although these were for Oracle only.

Examples

Take a skills database for example, where peoples' skills are recorded and managed. The person table won't obviously need translating, and date formats (e.g. date of birth) would be handled by the interface (.Net based on browser settings). However, the skills table is more complex:

Id int,
SkillTitle nvarchar(100),
Description nvarchar(max),
Objectives nvarchar(max)

Then there's the table that defines abilities against the skills:

Id int,
SkillLevel int, 
Title nvarchar(100),
Description nvarchar(max),
ContextualData nvarchar(max)

Basically in my database there's about 70 tables where text-based data would need to be managed in different languages. Some have a couple of text columns, and some as much as 10.

Answer 1

Well, a lot of this depends on exactly what data elements need to be translated, and at the moment I think you might be focusing too much on technical specifics instead of where the need truly lies.

Given this:

ASP.Net uses resource files to provide the interface in the local language

what elements need to be translated in the database?

1. Application / System lookup values? Probably not these since the UI is handling that via resource files.
2. User data? No, this wouldn't make sense. If someone fills out a form, they don't expect it to magically appear in 20 different languages.
3. Customer configured lookup values? Possibly, but they would be the ones supplying the translations.

Can you provide 1 or 2 examples of something that would need this structure? It seems that this structure is what would be used if one were not using resource files.

The only globalization issue I can see here is how to handle sorting and comparison according to each user's preferred language, given that many cultures have different rules, even if sharing many / most of the characters. In this respect, you will need to make use of Dynamic SQL because unfortunately you cannot specify collations dynamically (please vote for my suggestion to allow for this: Allow collation set by variable when using the COLLATE clause (at least in expressions) ). But for now (or possibly forever given the likelihood of that request being implemented :-( ), you will need to attach the COLLATE keyword to relevant parts of queries (WHERE / GROUP BY / ORDER BY / etc) based on the current user's language. So one person might get French_100_CI_AS and someone else might get Latin1_General_100_CI_AS, and so on. This doesn't require multiple fields.

The only thing that different columns for different languages would get you is the ability to index the different collations (i.e. locales). So if there is a place that really needs performance, you can consider specifying the primary language as the collation of the column, and then create non-persisted computed columns that simply return the main column along with a different collation, and then index the non-persisted computed column(s).

If you feel that your app really will need to store multiple languages worth of a single item that would not be handled by the resource files, then please provide at least one, if not two examples. But even if there are some, I can't see how it would ever be all, or even most, columns. Things like customer names don't change based on what language they come from. I mean, I did adopt a Spanish form of my name for Spanish class in Junior High and High School, but that was for class. If I become a customer of a company in a Spanish-speaking country, I will still give them my real name. And the name of my street, city, and state won't change either.