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.

Solution 1

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

Solution 2

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.