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 accomplish 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 input on them and any thoughts on other ways.
- Create a unique database for each language set and use ASP.Net to dynamically change which DB the data comes from.
- Create copies of each table appending the local code to hold the translated data (i.e. Table-en, Table-fr, Table1-en, Table1-fr)
- 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.