表格的语言翻译 [英] Language Translation for Tables

问题描述

我知道大多数人使用下面的方法并为需要翻译的特定表创建一个翻译表，但这可能会导致大量的表.

I know most people use the approach below and create a translation table for the specific table that needs translations but this can amount to a load of tables.

CREATE TABLE Product
(
     Product_id
    ,ProductTrans_id -- FK
)

CREATE TABLE ProductTranslation
(
     ProductTrans_id
    ,Product_id
    ,Name
    ,Descr
    ,lang_code
)

以下方法可行吗?假设您有很多表格需要翻译超过 1 列.你能做下面的 nad 把所有的翻译都放在 1 个表中吗?我想这张桌子会随着时间的推移而大大增加.

Would the below approach be viable? Say you have lots of tables where more than 1 column needs translating. Could you do the following nad keep all translations in 1 table? I suppose this table would grow hugely in size over time.

   CREATE TABLE translation_entry (
          translation_id        int,
          language_id           int,
          table_name            nvarchar(200),
          table_column_name     nvarchar(200),
          table_row_id          bigint,
          translated_text       ntext
        )

    CREATE TABLE translation_language (
          id int,
          language_code CHAR(2)
        )   

所以使用第二种方法你会得到这样的文本

So using the second approach you would get the text like so

select 
     product.name 
    ,translation_entry.translated_text
from product
inner join  translation_entry on product.product_id = translation_entry.table_row_id 
and translation_entry.table_name = 'Product' and translation_entry.table_column_name = 'Name'
and language_id = 3

推荐答案

我不知道你为什么关心表的数量:表越少并不意味着你的数据库更小、更高效或更好设计的.特别是如果减少表的数量会增加查询的复杂性，我会非常小心地这样做.

I'm not sure why you're concerned about the number of tables: having fewer tables doesn't automatically mean your database is smaller, more efficient or better designed. Especially if reducing the number of tables increases the complexity of your queries, I would be very careful about doing it.

无论如何，我会为每个基本"表选择一个翻译表.主要原因是您的第二个解决方案不灵活:如果主键不是单个整数，那么实现和使用将变得非常困难.查询翻译也比较复杂，根据表和数据的大小，可能很难对其进行有效索引.

Anyway, I would go for one translation table per 'base' table. The main reason is that your second solution isn't flexible: if the primary key is not a single integer then it becomes extremely difficult to implement and use. Querying for translations is also more complex, and depending on the size of the table and the data, it may be difficult to index it effectively.

不清楚为什么您在 Products 表上有一个 TranslationID；通常这种关系是相反的:

It's not clear why you have a TranslationID on the Products table; usually the relationship is the other way around:

create table dbo.Products (
    ProductCode char(10) not null primary key,
    ProductName nvarchar(50) not null,
    ProductDescription nvarchar(100) not null,
    -- other columns
)

create table dbo.ProductsTranslations (
    ProductCode char(10) not null,
    LanguageCode char(2) not null,
    ProductName nvarchar(50) not null,
    ProductDescription nvarchar(100) not null,
    -- other translations
    constraint FK1 foreign key (ProductCode)
        references dbo.Products (ProductCode),
    constraint FK2 foreign key (LanguageCode)
        references dbo.Languages (LanguageCode),
    constraint PK primary key (ProductCode, LanguageCode)
)

根据您的工具集和部署过程，您可能希望直接从基本表生成转换表，作为数据库构建的一部分.并且您可以使用视图来提供一个方便的、完全翻译"的基表版本.

Depending on your toolset and deployment process you may want to generate translation tables directly from the base ones as part of your database build. And you can use views to provide a convenient, 'fully translated' version of the base table.

一个有趣的问题是Products 中的列使用什么语言，以及它们是否可以在不需要翻译时直接使用.我的建议是，所有生产代码都应该传递一个语言参数，并仅从 ProductsTranslations 表中获取文本，即使是英语(或任何您的内部公司语言)也是如此.这样您就可以确保所有官方"名称都在同一个表中，并且基表上的列是为了数据模型的清晰和完整性以及开发人员的方便和(可能)内部临时使用而存在的报告等.

One interesting question is what language is used for the columns in Products and if they can be used directly when no translation is required. My suggestion would be that all production code should pass a language parameter and take the text from the ProductsTranslations table only, even for English (or whatever your internal corporate language is). That way you can be sure that all 'official' names are found in the same table, and the columns on the base table are there for clarity and completeness of the data model as well as developer convenience and (possibly) internal use on ad hoc reports and so on.

这篇关于表格的语言翻译的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！