Can I have some advice about globalization in .net for a multi-language CMS?

Question

I am building a CMS that will support 7 different languages/cultures. For images that cannot be changed through the CMS I think I am going to use different resource files. As for the content I was thinking of separate databases. If the user needs to change the current culure I have a drop down that stores the chosen culture in a cookie then I read the cookie and set the culture in the Application_BeginRequest of the global.asax. How can I go about using different connection strings for the different cultures? Will the resource file handle this too? Is it even a good idea to have multiple databases? This is all a first for me and am looking for any pointers anyone may have. Thanks!

Edit: The different languages/cultures will be managed by different users in the CMS so the content may be different for a given page not just a translation.

Answer 1

I would strongly advise you against having a database for each language. You could keep each translatable item into a separate table where each row represents the translation of some user content for a given language.

Answer 2

I would be also against use separate databases for each language. It would be overcomplicated solution. Dont know what you want to save to db, but we are using patern like there is extra transaction table for each table that requires some text to translate. E.g.

tblProduct (Id,Cost,Weight)  
tblProductTranslation (Id, IdProduct, Culture, Name, Description)

You can see that the translated fields are moved to translation table.

Below is snipped of sql which returns product with required Culture. Also if not required culture is available it returns the default one.

SELECT 
en.ID
,en.ProductCategoryId
,ISNULL(req.[Name], en.[Name]) AS [Name]
,ISNULL(req.[Description], en.[Description]) AS [Description]
,...

,en.IsActive FROM (SELECT * FROM res_vwProducts where culture = @DefaultCulture) en LEFT OUTER JOIN (select * from res_vwProducts where culture = @Culture) req on req.ID = en.ID WHERE en.ID = @ID

I can imagine you will do dump of code from some online html editor which may be bit different. In this case I would make it simple and just add Culture into table, like

tblArticle (Id, Culture, Body, Title,...)

Hope it helps. Cheers, X.

Answer 3

Based on what you said, it sounds like the content in the database doesn't actually need to be translated to different languages so you probably don't need separate databases to handle that.

If there is shared database content, you can use a translation table, similar to what @Xabatcha mentioned, though I would suggest a simpler table structure. Something like...

CREATE TABLE TranslatedText
(
    TextID INT,
    LanguageID INT,
    Text NVARCHAR(8000)
)

CREATE TABLE Product
(
    ProductID INT,
    NameTextID INT,
    DescriptionTextID INT,
    Cost MONEY,
    Weight DECIMAL
)

Any table that required translated text could just reference the TextID. The key would actually need to be a composite of TextID and LanguageID. If you are using SqlServer, you might want to create a database function to get the translated text to make it so you don't have to do a join for every sql statement where you need to get the value.