| Author |
Topic |
|
MDDC
Starting Member
5 Posts |
 Posted - 2010-03-10 : 12:44:35
|
| Hello,I need to have a web application which will use SQL Server 2005. What specific areas do I have to take care of in order to meet the following requirements:1. The database needs to store correctly content in English, Spanish, French, Japanese, and Korean. 2. The database needs to work correctly for searches in the above languages.Thanks very much in advance!-David |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-03-11 : 04:28:40
|
First off - this is a huge question. There is no easy fix to do this1. The database needs to store correctly content in English, Spanish, French, Japanese, and Korean. You'll probably need to use NVARCHAR datatypes for most storage. (depending on what you implement)The way that we handle this at the moment is through a tagging engine -- each display text in the front end has a unique name (in english) and then we have a translation table with key value pairs (label_name | JanguageID | displaytext )at some point in the future I guess that table would make a good case for horizontal partitioning but we don't need to at the moment.Each employee using our system can specify any of the supported languages in their preferences and see every text item in that language-- This was horrible to develop and set up (our application wasn't designed from the ground up to do this and had to be completely overhauled). Luckily we don't actually have to serve too many different pages (and most text is static) to the user so we've been able to cache most of the translation in memory on the app server(s).However, your scenario may be completely different from our requirements so that may not help so much  2. The database needs to work correctly for searches in the above languages.What do you need to search for? Without knowing the typical use case scenarios you probably won't get any good advice.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
MDDC
Starting Member
5 Posts |
Posted - 2010-03-11 : 09:10:23
|
| Charlie, Thanks soooo much for your input, which is a big step for me to start.Regarding "The database needs to work correctly for searches in the above languages.", I mean 1. When someone type Korean search words, the SQL Server needs to find right matches in both full-text search and field baseed search.2. When someone type German search words, the SQL Server needs to find right matches in both full-text search and field baseed search.2. When someone type English search words, the SQL Server needs to find right matches in both full-text search and field baseed search.Hope it is clear now. Any idea regarding server configuration to accomplish the above?Best. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-11 : 09:28:20
|
| We have Content Table too. One of key fields is Language. So when we want Content_ID=1234 we get the appropriate language version.When user visits the site they choose language. Usually that is automatic based on the Domain name they visit - so if you visit domain.JP then you get the Japanese version.So if you are using JP version and do a search we only check Content Table WHERE Language='JP'All error/warning messages etc. use an ID from the content table - so we have no hard-wired text anywhere (this is the hardest thing to adopt! everything in the code is Content_ID=1234 rather than "That record already exists" )We use templates for all output (so outputting a grid based on a ResultSet from a SQL Query would be "applied" to a template) so foreign languages are no problem - just use the template for that language. Doesn't matter if the language is German, and the Column Headings are much more verbose than English, the designers can take care of that in the design of the German Template.Translation is an issue. If someone wants to change the wording on the English version all templates in other languages have to be translated. So we provide logging of what has been changed, and what has also been translated / what is left to do. We also show translators the before/after (with changes highlighted) so they can work out what has to be changed, and amend the local-language version accordingly.Languages that are written vertically (Japanese) or right-to-left (Arabic?) are a further headache as the rendering of the various "panes" on the page has to organised differently. |
|
|
|
MDDC
Starting Member
5 Posts |
Posted - 2010-03-22 : 17:57:42
|
| Kristen, thanks for sharing your way of handling multi-language issue in the front-end. Could you please let me know what steps you take to conigure SQL server to support multi-language text save and search?Regarding the front-end, I am going to use Java technology.Best. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-03-22 : 23:48:19
|
the master.sys.messages table is a good example of how to do this too.  |
|
|
|
MDDC
Starting Member
5 Posts |
Posted - 2010-03-23 : 12:53:46
|
| Russel, I looked at that table and the only thing I can learn from it is that the column containing actual multi-language content needs to be nvarchar. language_id column itself has nothing to do with configure the system to store multi-language. It is only related to database design to support applications.What else do you recommend I need to configure MS SQL server and tables to handle multiple language ext save and search correctly?Thanks for your input! |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-03-23 : 13:37:44
|
| the language_id isn't for configuring, it's for your front end to be able to retrieve messages in the appropriate language |
|
|
|
MDDC
Starting Member
5 Posts |
Posted - 2010-03-23 : 20:23:43
|
| Thanks for your input. Regards. |
|
|
|
|
How to configure to store/search many languages?