我希望我的数据库支持多种语言的表格中的所有文本值。
那么什么是最好的方法？
Edit1 ::
EG
我有这个Person表：
$ b b
ID int FirstName nvarchar（20） LastName nvarchar（20）注释nvarchar（max） BirthDate date ...........所以如果我想让我的程序支持新语言let say French。
我应该每次添加新语言时添加新列吗？ 所以我的Person表将如下所示：
ID int FirstName_en nvarchar（20） FirstName_fr nvarchar（20） LastName_en nvarchar（20） LastName_fr nvarchar（20） Notes_en nvarchar（max） Notes_fr nvarchar（max） BirthDate date ...........添加两个新表格，一个用于语言，另一个用于Person_Languages值？
这样看起来像： Languages 表格：
ID int Lang-symbol nvarchar p> ID int BirthDate Date以及最后 Person_Translation 表格：
LangID int PersonID int 翻译nvarchar（max）还是有更好的东西？
。
解决方案不得不在问卷数据库中处理这个问题。多个问卷需要翻译成多种语言（英语，日语，中文）。我们首先识别了在问卷上打印的所有文本列。对于所有这些，我们需要能够存储翻译。对于每个具有需要翻译的文本列的表，我们创建了一个_translations表，它具有指向原始表的主键的外键，我们的语言表的外键，然后是每个文本字段的unicode列这将需要翻译。在这些文本列中，我们将存储我们需要的每种语言的翻译。
因此典型的查询如下：
选择p.id ，pt.product_name ，pt.product_description 从产品p inner join product_translations pt on p.id = pt.product_id and'fr'= pt.language_code所以，总是只有一个连接额外（对于每个表）来获得翻译。
我应该指出，我们只有tto处理有限数量的表，这不是一个大问题，保持一些额外的％_translations表。
我们考虑添加新的语言的列，但决定反对它的一连串的原因。首先，要支持的语言数量是未知的，但可能是实质性的（10,20种语言或更多）。结合大多数表有至少3个不同的人类可读列的事实，我们必须添加许多，许多文本列，这将导致非常宽的行。所以我们决定不这样做。
我们认为制作一个大的标签表格的另一种方法，有以下列：
（table_name ，id_of_table ，column_name ，language_id ，translated_text）
一个表存储数据库中任何位置的所有翻译。我们决定不这样做，因为它会使编写查询复杂化（因为每个正常列将导致翻译表中的一行，这将导致有效地将已经大的翻译表多重倍数时间加入到正常表中对于您的示例表，您将得到如下查询：
select product.id ，product_name。 translated_text product_name ，product_description.translated_text product_description 从产品p 内部联接翻译product_name on p.id = product_name.id 和'product'= product_name.table_name 和'product_name'= product_name.column_name 和'fr'= product_name.language 内部联接翻译product_description on p.id = product_name.id 和' product'= product_description.table_name and'product_description'= product_description.column_name and'fr'= product_description.language你可以看到，基本上这种类似一个实体 - 属性 - 值设计，这使查询很麻烦。
另一个最后一种方法的问题是，如果不是不可能实施对翻译文本的约束（在我们的情况下主要是单向性约束）将是困难的。有了一个单独的表格的翻译，你可以轻松地，干净地克服这些问题。
I want my database to support multi Languages for all text values in its tables.
So what is the best approach to do that?.
Edit1::
E.G.
I've this "Person" table:
ID int FirstName nvarchar(20) LastName nvarchar(20) Notes nvarchar(max) BirthDate date ...........
So if i want my program to support new language "let say French".
should i add new column every time i add new language ? So my "Person" table will look like this
ID int FirstName_en nvarchar(20) FirstName_fr nvarchar(20) LastName_en nvarchar(20) LastName_fr nvarchar(20) Notes_en nvarchar(max) Notes_fr nvarchar(max) BirthDate date ...........Or should i add 2 new tables one for languages and other for "Person_Languages" values ?
So this will look like : " Languages " table:
ID int Lang-symbol nvarchar(4)" Person " Table:
ID int BirthDate Dateand finally " Person_Translation " table:
LangID int PersonID int Translation nvarchar(max)Or there is something better ??
.
解决方案I have had to deal with this in a questionaire database. Multiple questionaires needed to be translated in multiple languages (English, Japanese, Chinese).
We first identified all text columns that would be printed out on the questionaires. For all these we would need to be able to store a translation. For each table having text columns that would require translations, we then created a _translations table, having a foreign key to point to the primary key of the original table, a foreign key to our language table, and then a unicode column for each text field that would require translation. In these text columns we would store the translations for each language we needed.
So a typical query would look like:
select p.id , pt.product_name , pt.product_description from product p inner join product_translations pt on p.id = pt.product_id and 'fr' = pt.language_codeSo, always just one join extra (for each table) to obtain the translations.
I should point out that we only had tto deal with a limited amount of tables, so it was not a big issue to maintain a few extra %_translations tables.
We did consider adding columns for the new language, but decided against it for a coouple of reasons. First of all the number of languages to be supported was not known, but could be substantial (10, 20 languages or maybe more). Combined with the fact that most tables had at least 3 distinct human readable columns, we would have to add many, many text columns which would result in very wide rows. So we decided not to do that.
Another approach we considered as to make one big "label" table, having the columns:
( table_name , id_of_table , column_name , language_id , translated_text)
effectively having one table to store all translations anywhere in the database. We decided against that too, because it would complicate writing queries (as each 'normal' column would result in one row in the translation table, which would result in effectively joining the already large translation table multiuple times to the normal table (once for each translated column). For your example table you would get queries like this:
select product.id , product_name.translated_text product_name , product_description.translated_text product_description from product p inner join translations product_name on p.id = product_name.id and 'product' = product_name.table_name and 'product_name' = product_name.column_name and 'fr' = product_name.language inner join translations product_description on p.id = product_name.id and 'product' = product_description.table_name and 'product_description' = product_description.column_name and 'fr' = product_description.languageas you can see, essentially this kind of like an entity-attribute-value design, which makes it cumbersome to query.
Another problem of that last approach is that it would make it hard if not impossible to enforce constraints on translated text (in our case mainly unicity constraints). With a separatee table for the translations, you can easily and cleanly overcome those problems.
更多推荐
如何在DataBase Schema中支持多语言方法？
发布评论