ASP.Net/MySQL: translating content into multiple languages

Question

I have an ASP.Net website that uses a MySQL database for the back. The website is an English e-commerce system and we are considering translating it into five other languages ​​(French, Spanish, etc.). We will receive translators to perform the translation - we reviewed automated services, but they are not good enough.

Static text on the site (for example, headers, buttons, etc.) can be easily served in several languages ​​through .Net built-in localization functions (resx files, etc.).

That I'm not so sure about this is how best to store and retrieve multilingual content in a database. For example, there is a product table that includes these fields ...

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

The text of the title, summary, description and function should be available in all different languages.

Here are two options I came up with ...

Create an additional field for each language. For example, we could have titleEn, titleFr, titleEs, etc. For all languages ​​and repeat this for all text columns. Then we would adapt our code to use the appropriate field depending on the chosen language. This seems a bit hacky and will also lead to very large tables. In addition, if we wanted to add additional languages ​​in the future, it would take a long time 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

, , , , . , SQL. , .

! " "? - ?

Answer 1

:

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


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

:

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

( , lang ProductLoc)

Answer 2

. . , , , .

, - :

products: id, categoryid, 

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

. , , ,...