How do you localize a database driven website

Question

I've been playing with the .NET built in localization features and they seem to all rely on putting data in resx files.

But most systems can't rely on this because they are database driven. So how do you solve this issue? Is there a built in .NET way, or do you create a translations table in SQL and do it all manually? And if you have to do this on the majority of your sites, is there any reason to even use the resx way of localization?

An example of this is I have an FAQ list on my site, I keep this list in the database so I can easily add/remove more, but by putting it in the database, I have no good way have translating this information into multiple languages.

Answer 1

In my opinion, localizing dynamic content (e.g., your FAQ) should be done by you in your database. Depending on how your questions are stored, I would probably create a "locale" column and use that when selecting the FAQ questions from the database. I'm not sure if this would scale very well when you started localizing lots of tables.

For static content (e.g, form field labels, static text, icons, etc) you should probably be just fine using file-based resources. If you really wanted to, however, it looks like it wouldn't be super hard to create a custom resource provider implementation that could handle this.

Here's some related links:

• http://channel9.msdn.com/forums/Coffeehouse/250892-Localizing-with-a-database-or-resx-files/
• http://weblogs.asp.net/scottgu/archive/2006/05/30/ASP.NET-2.0-Localization-_2800_Video_2C00_-Whitepaper_2C00_-and-Database-Provider-Support_2900_.aspx
• http://www.arcencus.nl/Blogs/tabid/105/EntryID/20/Default.aspx
• http://msdn.microsoft.com/en-us/library/aa905797.aspx
• http://www.codeproject.com/KB/aspnet/customsqlserverprovider.aspx

Answer 2

Currently, translation is not something that can be done automatically. The best way is to get a person to translate and use Nick's methods to show the proper language.

Answer 3

For a given item in your data model, split out the description part into a localized table with a locale Id column (LCID).

So the table for Product would not in fact contain the products description or name, but only its hard and fast values (ProductId, EAN, NumberInStock, NextStockData, IsActive, IsPublished) etc.

ProductDescription then contains ProductId, Name, Description, LCID

Answer 4

I live in Canada so multilingualism is a big deal. I've seen two approaches. First option is to store all localized data for a specific record in a different table, linked to the original table by the primary key, and the locale. The second option is similar to the first, except that for each locale, there is a different table, with the locale as a suffix for the table name.

Option A

Item (ItemID, ...)
ItemLocal (ItemID,LocaleID,....)

Option B

Item (ItemID, ...)
Item_ENUS (ItemID,....)
Item_ENGB (ItemID,....)
Item_FR (ItemID,....)

A third option I thought Of recent, which would be really nice if a database supported it natively would be to store the values for all locals in the same field. And if the field was set up as varchar-multilocal, then you would have to access it by passing a parameter to specify the language. Nothing like this exists as I know it, but it's something that I think would really make things a lot easier, and a lot more fluid.

Answer 5

We use a mix of RESX files and Option A of Kibbee's response. We created a simple tool to manage the RESX files online: http://blog.lavablast.com/post/2008/02/RESX-file-Web-Editor.aspx

Answer 6

@Kibbee: native support would be best! I also couldn't find anything by now, except: http://stackoverflow.com/questions/258483/best-practices-for-localizing-a-sql-server-20052008-database

Did you find other resources in the meanwhile?