数据库本地化 [英] Database localization

问题描述

我有一些数据库表包含需要本地化的名称和描述列。我最初尝试设计支持这种情况的数据库模式是这样的：

I have a number of database tables that contain name and description columns which need to be localized. My initial attempt at designing a DB schema that would support this was something like:

product
-------
id
name
description


local_product
-------
id
product_id
local_name
local_description
locale_id


locale
------
id
locale

但是，此解决方案需要一个新的 local _ table对于包含名称和需要本地化的描述列的每个表。为了避免这种开销，我重新设计了模式，以便只需要一个本地化表

However, this solution requires a new local_ table for every table that contains name and description columns that require localization. In an attempt to avoid this overhead I redesigned the schema so that only a single localization table is needed

product
-------
id
localization_id


localization    
-------
id    
local_name
local_description
locale_id


locale
------
id
locale

下面是一个示例，将存储在此模式中的是需要本地化的2个表（产品和国家）：

Here's an example of the data which would be stored in this schema when there are 2 tables (product and country) requiring localization:

国家

id,     localization_id
-----------------------
1,      5

产品

id,     localization_id
-----------------------
1,      2

本地化

id,     local_name,   local_description,     locale_id
------------------------------------------------------
2,      apple,        a delicious fruit,     2
2,      pomme,        un fruit délicieux,    3
2,      apfel,        ein köstliches Obst,   4
5,      ireland,      a small country,       2
5,      irlande,      un petite pay,         3

区域设置

id,     locale
--------------
2,      en
3,      fr
4,      de

请注意，本地化表的复合主键是（id，locale_id ），但产品表中的外键仅指该复合PK的第一个元素。这似乎是从标准化POV的一件坏事。

Notice that the compound primary key of the localization table is (id, locale_id), but the foreign key in the product table only refers to the first element of this compound PK. This seems like 'a bad thing' from the POV of normalization.

有什么办法可以解决这个问题，或者是有一个完全不同的架构支持本地化不为每个可本地化表创建一个单独的表？

Is there any way I can fix this problem, or alternatively, is there a completely different schema that supports localization without creating a separate table for each localizable table?

更新：
一些受访者提出了一个需要创建的解决方案每个可本地化表的单独表。但是，这正是我想避免的。上面提出的模式几乎可以解决我的问题，但我不满意的是， local_id 外键仅指相应主键的一部分在本地化表中。

Update: A number of respondents have proposed a solution that requires creating a separate table for each localizable table. However, this is precisely what I'm trying to avoid. The schema I've proposed above almost solves the problem to my satisfaction, but I'm unhappy about the fact that the localization_id foreign keys only refer to part of the corresponding primary key in the localization table.

谢谢，
Don

Thanks, Don

推荐答案

我觉得没事。您正在描述产品与其本地化文本之间的一对多关系。

I think it's fine. You're describing a one-to-many relationship between a product and its localization text.

我想知道您是否也应该本地化英语，而不是将其归一化您的产品表。

I'm wondering if you should also localize the english instead of denormalizing it in your product table.

这篇关于数据库本地化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！