ASP.Net / MySQL : Translating content into several languages

Question

I have an ASP.Net website which uses a MySQL database for the back end. The website is an English e-commerce system, and we are looking at the possibility of translating it into about five other languages (French, Spanish etc). We will be getting human translators to perform the translation - we've looked at automated services but these aren't good enough.

The static text on the site (e.g. headings, buttons etc) can easily be served up in multiple languages via .Net's built in localization features (resx files etc).

The thing that I'm not so sure about it how best to store and retrieve the multi-language content in the database. For example, there is a products table that includes these fields...

• productId (int)
• categoryId (int)
• title (varchar)
• summary (varchar)
• description (text)
• features (text)

The title, summary, description and features text would need to be available in all the different languages.

Here are the two options that I've come up with...

Create additional field for each language For example we could have titleEn, titleFr, titleEs etc for all the languages, and repeat this for all text columns. We would then adapt our code to use the appropriate field depending on the language selected. This feels a bit hacky, and also would lead to some very large tables. Also, if we wanted to add additional languages in the future it would be time consuming to add even more columns.

Use a lookup table We could create a new table with the following format...

textId | languageId | content
-------------------------------
10     | EN         | Car
10     | FR         | Voiture
10     | ES         | Coche
11     | EN         | Bike
11     | FR         | Vélo

We'd then adapt our products table to reference the appropriate textId for the title, summary, description and features instead of having the text stored in the product table. This seems much more elegant, but I can't think of a simple way of getting this data out of the database and onto the page without using complex SQL statements. Of course adding new languages in the future would be very simple compared to the previous option.

I'd be very grateful for any suggestions about the best way to achieve this! Is there any "best practice" guidance out there? Has anyone done this before?

Answer 1

It's not good idea just to add new columns to existing table. It will be really hard to add a new language in the feature. The lookup table is much more better but I think you can have problem with performance because number of translated records.

I think best solution is to have a shared table:

products: id, categoryid,

and same tables for every language

products_en, products_de: product_id (fk), title, price, description, ...

You will just select from the shared one and join table with your language. The advantage is that you can localize even the price, category, ...

Answer 2

In your case, I would recommend using two tables:

Product
-------------------------------
ProductID  |  Price   |  Stock 
-------------------------------
10         |   10     |   15


ProductLoc
-----------------------------------------------
ProductID  | Lang   | Name      |  Description
-----------------------------------------------
 10        |  EN    | Bike      |  Excellent Bike 
 10        |  ES    | Bicicleta |  Excelente bici

This way you can use:

SELECT * FROM 
Product LEFT JOIN ProductLoc ON Product.ProductID = ProductLoc.ProductID 
                               AND ProductLoc.Lang = @CurrentLang

(Left join just in case there is no record for the current lang in the ProductLoc table)