Storage and presentation of database data across multiple languagesHow to handle database of multiples...
Is there a way to pause a running process on Linux systems and resume later?
Do error bars on probabilities have any meaning?
Are all power cords made equal?
Is it common to refer to someone as "Prof. Dr. [LastName]"?
Is layered encryption more secure than long passwords?
Exploding Numbers
Is it Safe to Plug an Extension Cord Into a Power Strip?
SQL Server 2017 crashes when backing up because filepath is wrong
Do the speed limit reductions due to pollution also apply to electric cars in France?
Cryptic cross... with words
How can I differentiate duration vs starting time
Why is it that Bernie Sanders always called a "socialist"?
Sets which are both Sum-free and Product-free.
How does holding onto an active but un-used credit card affect your ability to get a loan?
Badly designed reimbursement form. What does that say about the company?
If I have Haste cast on me, does it reduce the casting time for my spells that normally take more than a turn to cast?
Coworker is trying to get me to sign his petition to run for office. How to decline politely?
How can I handle players killing my NPC outside of combat?
A cancellation property for permutations?
Why and/or operations in python statement are behaving unexpectedly?
Now...where was I?
When distributing a Linux kernel driver as source code, what's the difference between Proprietary and GPL license?
How to transport 10,000 terrestrial trolls across ocean fast?
Figuring out size of Device Drivers and where they are loaded in High Memory
Storage and presentation of database data across multiple languages
How to handle database of multiples languages and countriesWhy is there still a varchar data type?SSIS keeps force changing excel source string to floatBest practices for handling languageFull Text Index and database structureDatabase design - load multiple excels dailyWhat are the benefits vs. drawbacks to using a clustered index in SQL ServerFind the source of a recurrent mass SQL edit on a serverOrder by custom filter without certain dataSQL Server query problem when selecting data from child table based on column in parent tableSQL Server database design for fields of different data types - single table with multiple columns or multiple tables for each data type?
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:
- 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.
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.
sql-server languages locales globalization
add a comment |
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:
- 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.
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.
sql-server languages locales globalization
add a comment |
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:
- 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.
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.
sql-server languages locales globalization
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:
- 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.
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.
sql-server languages locales globalization
sql-server languages locales globalization
edited 9 mins ago
EvilDr
asked Feb 7 at 10:05
EvilDrEvilDr
389620
389620
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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?
- Application / System lookup values? Probably not these since the UI is handling that via resource files.
- 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.
- 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.
Thanks for your valued input. I raised a suggestion to Microsoft about this exact same thing some years ago before the portal was moved to Azure. Strangely, it never got migrated... I voted on your's anyway. I've updated the question to provide a crude example. Sorting isn't really too hard because the .Net interface can handle that natively based on browser locale.
– EvilDr
Feb 8 at 11:04
Is there any way of adding some kind of meta ID/code to each table column that requires translation, then having a single database table that contains the tranlsations for those...? Just a thought (magic wand wish)...
– EvilDr
Feb 8 at 11:18
@EvilDr Thanks for providing those additional details in the question. I will update soon now that I better understand the overall issue.
– Solomon Rutzky
Feb 10 at 16:32
Hi Solomon - did you get chance to consider this in any more detail please?
– EvilDr
11 mins ago
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f229122%2fstorage-and-presentation-of-database-data-across-multiple-languages%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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?
- Application / System lookup values? Probably not these since the UI is handling that via resource files.
- 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.
- 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.
Thanks for your valued input. I raised a suggestion to Microsoft about this exact same thing some years ago before the portal was moved to Azure. Strangely, it never got migrated... I voted on your's anyway. I've updated the question to provide a crude example. Sorting isn't really too hard because the .Net interface can handle that natively based on browser locale.
– EvilDr
Feb 8 at 11:04
Is there any way of adding some kind of meta ID/code to each table column that requires translation, then having a single database table that contains the tranlsations for those...? Just a thought (magic wand wish)...
– EvilDr
Feb 8 at 11:18
@EvilDr Thanks for providing those additional details in the question. I will update soon now that I better understand the overall issue.
– Solomon Rutzky
Feb 10 at 16:32
Hi Solomon - did you get chance to consider this in any more detail please?
– EvilDr
11 mins ago
add a comment |
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?
- Application / System lookup values? Probably not these since the UI is handling that via resource files.
- 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.
- 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.
Thanks for your valued input. I raised a suggestion to Microsoft about this exact same thing some years ago before the portal was moved to Azure. Strangely, it never got migrated... I voted on your's anyway. I've updated the question to provide a crude example. Sorting isn't really too hard because the .Net interface can handle that natively based on browser locale.
– EvilDr
Feb 8 at 11:04
Is there any way of adding some kind of meta ID/code to each table column that requires translation, then having a single database table that contains the tranlsations for those...? Just a thought (magic wand wish)...
– EvilDr
Feb 8 at 11:18
@EvilDr Thanks for providing those additional details in the question. I will update soon now that I better understand the overall issue.
– Solomon Rutzky
Feb 10 at 16:32
Hi Solomon - did you get chance to consider this in any more detail please?
– EvilDr
11 mins ago
add a comment |
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?
- Application / System lookup values? Probably not these since the UI is handling that via resource files.
- 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.
- 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.
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?
- Application / System lookup values? Probably not these since the UI is handling that via resource files.
- 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.
- 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.
answered Feb 8 at 8:50
Solomon RutzkySolomon Rutzky
48.5k581177
48.5k581177
Thanks for your valued input. I raised a suggestion to Microsoft about this exact same thing some years ago before the portal was moved to Azure. Strangely, it never got migrated... I voted on your's anyway. I've updated the question to provide a crude example. Sorting isn't really too hard because the .Net interface can handle that natively based on browser locale.
– EvilDr
Feb 8 at 11:04
Is there any way of adding some kind of meta ID/code to each table column that requires translation, then having a single database table that contains the tranlsations for those...? Just a thought (magic wand wish)...
– EvilDr
Feb 8 at 11:18
@EvilDr Thanks for providing those additional details in the question. I will update soon now that I better understand the overall issue.
– Solomon Rutzky
Feb 10 at 16:32
Hi Solomon - did you get chance to consider this in any more detail please?
– EvilDr
11 mins ago
add a comment |
Thanks for your valued input. I raised a suggestion to Microsoft about this exact same thing some years ago before the portal was moved to Azure. Strangely, it never got migrated... I voted on your's anyway. I've updated the question to provide a crude example. Sorting isn't really too hard because the .Net interface can handle that natively based on browser locale.
– EvilDr
Feb 8 at 11:04
Is there any way of adding some kind of meta ID/code to each table column that requires translation, then having a single database table that contains the tranlsations for those...? Just a thought (magic wand wish)...
– EvilDr
Feb 8 at 11:18
@EvilDr Thanks for providing those additional details in the question. I will update soon now that I better understand the overall issue.
– Solomon Rutzky
Feb 10 at 16:32
Hi Solomon - did you get chance to consider this in any more detail please?
– EvilDr
11 mins ago
Thanks for your valued input. I raised a suggestion to Microsoft about this exact same thing some years ago before the portal was moved to Azure. Strangely, it never got migrated... I voted on your's anyway. I've updated the question to provide a crude example. Sorting isn't really too hard because the .Net interface can handle that natively based on browser locale.
– EvilDr
Feb 8 at 11:04
Thanks for your valued input. I raised a suggestion to Microsoft about this exact same thing some years ago before the portal was moved to Azure. Strangely, it never got migrated... I voted on your's anyway. I've updated the question to provide a crude example. Sorting isn't really too hard because the .Net interface can handle that natively based on browser locale.
– EvilDr
Feb 8 at 11:04
Is there any way of adding some kind of meta ID/code to each table column that requires translation, then having a single database table that contains the tranlsations for those...? Just a thought (magic wand wish)...
– EvilDr
Feb 8 at 11:18
Is there any way of adding some kind of meta ID/code to each table column that requires translation, then having a single database table that contains the tranlsations for those...? Just a thought (magic wand wish)...
– EvilDr
Feb 8 at 11:18
@EvilDr Thanks for providing those additional details in the question. I will update soon now that I better understand the overall issue.
– Solomon Rutzky
Feb 10 at 16:32
@EvilDr Thanks for providing those additional details in the question. I will update soon now that I better understand the overall issue.
– Solomon Rutzky
Feb 10 at 16:32
Hi Solomon - did you get chance to consider this in any more detail please?
– EvilDr
11 mins ago
Hi Solomon - did you get chance to consider this in any more detail please?
– EvilDr
11 mins ago
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f229122%2fstorage-and-presentation-of-database-data-across-multiple-languages%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown