Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
spirit1
Cybernetic Yak Master
11752 Posts |
 Posted - 2005-01-10 : 03:54:42
|
hi guys ang gals!i need some opinions.what is the best way of providing multi language support?Basicaly i'd like to have an item description in 4+ languages.i know it caould be done via xml, or i could store it in 4 different columns in one table. or should i use a separate db for each language... i'm just wondering what my options are, and how you guys would handle it.thanx for any thoughts!Go with the flow & have fun! Else fight the flow  |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-01-10 : 11:14:47
|
Ours is a "bit complicated"  , but here goes:We have a separate table with the language-sensitive material, so:Table = STOCKColumns = PART_ID, QTY_ON_HAND, PRICE etc.Table = STOCK_DESCRIPTIONColumns = PART_ID, ATTRIBUTE, LANGUAGE, DESCRIPTION, SAFETY_INSTRUCTIONS etc.For a "normal" stock table I would have all this one table, but for multi-language I've separated the "Description" and "Safety Instructions" into a separate language-sensitive table.We use a LANGUAGE and an ATTIBUTE column, this allows us to have Brand-sensitive descriptions, as well as language-sensitive.We then have a mapping table which says something like:Brand Language Sequence Attribute Language--------- -------- -------- --------- --------WHOLESALE ENG 1 WHOLESALE ENGWHOLESALE ENG 2 DEFAULT ENGRETAIL ENG 1 RETAIL ENGRETAIL ENG 2 DEFAULT ENGWHOLESALE FR 1 WHOLESALE FRWHOLESALE FR 2 WHOLESALE ENGWHOLESALE FR 3 DEFAULT FRWHOLESALE FR 4 DEFAULT ENG This says that for the Wholesale Brand, for English, we will use any Wholesale-specfic content first, and if we can't find any then we will use Default content.Similarly for Retail brand.However, for Wholesale French we will use Wholsale/French content if we can find any; if that is missing we will use any Wholesale-specific English content, and if there isn't any of that we will use Default Content instead - First French-specific [Default] content if there is any, and if not English Default content.(It might be more valid to make the sequence Wholesale-French, Default-French, Wholesale-French, Wholesale-English; we take the viewpoint that BRAND customisation means that there is something special about a Brand's requirements, so we display the Brand content [in the wrong lanauage] rather than the Default content)We use the Mapping Table to choose descriptive content (descriptions of products etc), Page Content (CMS content for pages etc.), Meta Data (Column names from the data, form-field prompts and PopUp Help etc.) as well as Error Messages.This allows the system to "fail gracefully" - for example if a particular Error Message has not yet been translated from English into French and so on.You just have to JOIN the Mapping Table to the Language Specific table ON the Brand/Language of the current user, ORDER BY Sequence DESC, and do a TOP 1 to get the content for the most appropriate attribute/langauge.If you have less sophisticated requirements then just JOIN using the Language code for the current user and ditch the Mapping table!Kristen |
 |
|
|
jhermiz
3564 Posts |
Posted - 2005-01-10 : 11:57:41
|
So the actual translation is stored in another table ?How is the data stored ? nvarchar ?
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
|
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-01-10 : 12:08:34
|
well it must be nvarchar, for the strange chars the english doesn't handle  Kristen: nice design... not bad at all i must say. haven't thought of a mapping table. but i don't think i need one...Go with the flow & have fun! Else fight the flow |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-01-11 : 01:35:00
|
| "nvarchar"We don;t bother with that until we know that we will have unicode character mapping. Generally you'll have all sorts of other problems at the same time - right to left charater writing and stuff.Having said that I don't know if some Roman character languages need nvarchar to support accents etc."haven't thought of a mapping table, but i don't think i need one..."Aw shucks!Kristen |
|
|
|
|
|
|
|
|
Multi language support