第三种选择是最好的，原因如下:

Doesn't require altering the database schema for new languages (and thus limiting code changes) Doesn't require a lot of space for unimplemented languages or translations of a a particular item Provides the most flexibility You don't end up with sparse tables You don't have to worry about null keys and checking that you're displaying an existing translation instead of some null entry. If you change or expand your database to encompass other translatable items/things/etc you can use the same tables and system - this is very uncoupled from the rest of the data.

亚当

我同意随机发生器。我不明白你为什么需要一个表“翻译”。

我想，这就足够了:

TA_product: ProductID, ProductPrice
TA_Language: LanguageID, Language
TA_Productname: ProductnameID, ProductID, LanguageID, ProductName

我正在寻找一些本地化的技巧，并找到了这个主题。 我想知道为什么要用这个:

CREATE TABLE T_TRANSLATION (
   TRANSLATION_ID
)

所以你会得到user39603所建议的:

table Product
productid INT PK, price DECIMAL, translationid INT FK

table Translation
translationid INT PK

table TranslationItem
translationitemid INT PK, translationid INT FK, text VARCHAR, languagecode CHAR(2)

view ProductView
select * from Product
inner join Translation
inner join TranslationItem
where languagecode='en'

你能不能把Translation表去掉，这样你就得到了这个:

    table Product
    productid INT PK, price DECIMAL

    table ProductItem
    productitemid INT PK, productid INT FK, text VARCHAR, languagecode CHAR(2)

    view ProductView
    select * from Product
    inner join ProductItem
    where languagecode='en'

看看这个例子:

PRODUCTS (
    id   
    price
    created_at
)

LANGUAGES (
    id   
    title
)

TRANSLATIONS (
    id           (// id of translation, UNIQUE)
    language_id  (// id of desired language)
    table_name   (// any table, in this case PRODUCTS)
    item_id      (// id of item in PRODUCTS)
    field_name   (// fields to be translated)
    translation  (// translation text goes here)
)

我认为没有必要解释，结构本身就说明了这一点。

下面的方法是否可行?假设您有多个表，其中有超过1个列需要转换。所以对于产品来说，你可能同时需要翻译产品名称和产品描述。你能做到以下几点吗?

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)
    )   

第三种选择是最好的，原因如下:

Doesn't require altering the database schema for new languages (and thus limiting code changes) Doesn't require a lot of space for unimplemented languages or translations of a a particular item Provides the most flexibility You don't end up with sparse tables You don't have to worry about null keys and checking that you're displaying an existing translation instead of some null entry. If you change or expand your database to encompass other translatable items/things/etc you can use the same tables and system - this is very uncoupled from the rest of the data.

亚当