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.
- Client enters database data from web forms in their own language (using nvarchar/UTF16 across all respective fields)
- 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.
- 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?
- How do you handle a table having 20 data fields, rather than just one as in the example?
- 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.
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.