数据库本地化

Question

我正在寻求意见，是否有更好/不同/常见的解决方案来解决以下问题：

---

我有一个产品数据库，其中包含产品的英文名称（该应用程序的默认语言），如果有可用的翻译，我需要这些名称的翻译。

目前我有以下设置：

一个产品表

CREATE TABLE products
(
  id serial NOT NULL,
  "name" character varying(255) NOT NULL,
  CONSTRAINT products_pkey PRIMARY KEY (id)
)

和产品本地化表

CREATE TABLE products_l10n
(
  product_id serial NOT NULL,
  "language" character(2) NOT NULL,
  "name" character varying(255) NOT NULL,
  CONSTRAINT products_l10n_pkey PRIMARY KEY (product_id, language),
  CONSTRAINT products_l10n_product_id_fkey FOREIGN KEY (product_id)
      REFERENCES products (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)

我使用以下查询来检索本地化产品列表（以德语为例），并回退到默认的英文名称:

SELECT p.id, COALESCE(pl.name, p.name) 
from products p LEFT 
JOIN products_l10n pl ON p.id = pl.product_id AND language = 'de';

这段SQL代码是使用postgres方言编写的。数据存储为UTF-8格式。

- Fionn

Answer 1

看起来不错。我可能会改变的一件事是你处理语言的方式：那应该是一个单独的表格。因此，你将拥有：

CREATE TABLE products_l10n
(
  product_id serial NOT NULL,
  language_id int NOT NULL,
  "name" character varying(255) NOT NULL,
  CONSTRAINT products_l10n_pkey PRIMARY KEY (product_id, language),
  CONSTRAINT products_l10n_product_id_fkey FOREIGN KEY (product_id)
      REFERENCES products (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
  CONSTRAINT products_l10n_language_id_fkey FOREIGN KEY (language_id)
      REFERENCES languages (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)

CREATE TABLE languages
)
  id serial not null
  "language" character(2) NOT NULL
)

除此之外，我认为你已经拥有了最好的解决方案。

- Daniel Spiewak

---

数据库不应该能够将字符(2)字段优化为整数吗 - 因为如果存在问题，ISO 639-1语言代码比整数更易读。 - Fionn

我也有同样的想法。但仔细一看，使用额外的层有一些优点：可以枚举所有可用/配置的语言（在GUI中很有用），可以删除整个语言（带有DB约束）并且可以防止“拼写错误”。虽然当使用2个字符代码时后者不应该有影响 ;) - kazu

Answer 2

看起来不错 - 类似于我喜欢的本地化技术 - 那么对于宽字符（日语）呢？我们总是使用nvarchar来处理。

然而，我们在国际采购业务中实际发现，在产品方面跨越国际边界没有一致性，因为每个国家的供应商都不同，所以我们国际化/本地化了我们的界面，但数据库完全不同。

- Cade Roux

---

已编辑问题 - 数据始终为Unicode（UTF-8）。 - Fionn

Answer 3

我能提供的唯一变化是，您可能还想包括国家/方言的可能性；例如，不仅使用英语（en），而是使用美式英语（en-US）。这样，您就可以考虑到所有变化（例如，英国拼写，加拿大法语可能与法国口音有所不同等）。

- Chris

Answer 4

在处理这种事情时，我通常会建立一个不包含任何名称的产品表和一个仅包含名称（以及其他内容）的产品翻译表。

然后我就可以使用以下查询：

SELECT 
    i.id, 
    i.price, 
    it.label 
FROM 
    items i 
    LEFT JOIN items_trans it 
        ON i.id=it.item_id AND it.lang_id=(
            SELECT lang_id
            FROM items_trans
            WHERE item_id=i.id
            ORDER BY
                (lang_id=1) DESC,
                (lang_id=0) DESC
            LIMIT 1
        )

你觉得呢？

- Emmanuel

---

我不喜欢这个选项，因为在这种情况下，你不能保证未翻译的名称至少有一个默认值。 - Tengiz

Answer 5

在我看来还不错。

显然，你应该将本地化名称放入Unicode列中，你可以选择将英文默认值放入ASCII字段中（假设数据库支持）。最好全部使用Unicode并“忘记”它。

- Rob Williams

---

已编辑问题 - 数据始终为Unicode（UTF-8）。 - Fionn

Answer 6

其他人没有提及的唯一复杂因素是字符集 - 你能处理希伯来语、阿拉伯语、俄语、中文、日文吗？如果一切都是Unicode，那么你只需要担心GB18030（中文），它是Unicode的超集（如果我没有理解错的话）。

- Jonathan Leffler