如何在DataBase Schema中支持多语言方法？ [英] How to support Multi-Languages approach in DataBase Schema?

问题描述

我希望我的数据库支持多种语言的表格中的所有文本值。

那么什么是最好的方法？

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  Date
and 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_code
So, 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.language
as 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中支持多语言方法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！