ASP.Net / MySQL的：内容翻译成几种语言 [英] ASP.Net / MySQL : Translating content into several languages
问题描述
我有一个ASP.Net网站，该网站使用的MySQL数据库后端。该网站是一个英语电子商务系统，我们正在寻找翻译成约五其他语言（法语，西班牙语等）的可能性。我们将让翻译人员进行翻译 - 我们已经看了自动化服务，但这些还不够好
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.
网站上的静态文本（例如标题，按钮等）可以很容易地在多语言通过净内置的本地化功能（RESX文件等）来担任了。
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）
- 标题（VARCHAR）
- 摘要（VARCHAR）
- 描述（文字）
- 功能（文字）
标题，摘要，描述和功能的文字将需要在所有不同的语言可用。
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...
为每种语言创建其他领域
例如，我们可以有titleEn，titleFr，titleEs等所有的语言，然后重复此为所有文本列。然后，我们将调整我们的code使用相应的字段取决于所选择的语言。这感觉有点哈克，也会导致一些非常大的表。此外，如果我们想在将来添加其他语言这将是耗时增加更多的列。
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
我们会再调整我们的产品表中引用相应的TEXTID为标题，摘要，描述，而是拥有具有存储在产品表中的文本。这似乎更优雅，但我想不出得到这个数据从数据库中，并到页面上，而无需使用复杂的SQL语句的简单方法。当然，增加在未来新的语言会比previous选项很简单。
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?
推荐答案
在你的情况，我会建议使用两个表：
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
（左刚加入的情况下，有在ProductLoc表当前郎没有记录）
(Left join just in case there is no record for the current lang in the ProductLoc table)
这篇关于ASP.Net / MySQL的：内容翻译成几种语言的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
