SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Suggestions on DB design for localization
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Eagle_f90
Constraint Violating Yak Guru

USA
422 Posts

Posted - 12/12/2012 :  22:07:28  Show Profile  Visit Eagle_f90's Homepage  Reply with Quote
I am building an ASP.Net site that will pull dynamic data from SQL server. The site will be built with localization/globalization foundations in anticipation that in a year or so the site will start holding translations for non-english speaking users. I already know how acomplish this in ASP.Net for the static content but I would like some suggestions on a DB design for the dynamic data. I have toyed with the following 3 ideas and would like some inpute on them and any thoughts on other ways.

1: Create a unique database for each language set and use ASP.Net to dynamicly change which DB the data comes from.
2. Create copies of each table appending the local code to hold the translated data (i.e. Table-en, Table-fr, Table1-en, Table1-fr)
3. Leave DB and table design intact (only adding a single column to hold the local code) and insert translated data as a new row entry.

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

LoztInSpace
Aged Yak Warrior

939 Posts

Posted - 12/17/2012 :  19:45:29  Show Profile  Reply with Quote
I'd go with 3.
Assuming you also want to default to some kind of fallback translation (English, say) for entries that have not been translated, I'd recommend wrapping it all up in views and/or SPs such that the caller doesn't really know how it all works because it can get funky.
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
341 Posts

Posted - 12/21/2012 :  18:02:16  Show Profile  Reply with Quote
Tenatively #3. If you mean store the language code for each row as required, and make the existing char columns nchar so they can hold any language.

Also, make sure you create custom messages for ALL error messages coming from the code. MS already has multi-language capability for message ids used in RAISERROR / FORMATMESSAGE.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000