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.

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Suggestions on DB design for localization

Author  Topic 

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2012-12-12 : 22:07:28
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

940 Posts

Posted - 2012-12-17 : 19:45:29
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
Aged Yak Warrior

550 Posts

Posted - 2012-12-21 : 18:02:16
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
   

- Advertisement -