用于存储本地化数据版本的良好数据库表设计

I'm trying to design some tables to store some data, which has to be converted to different languages later. Can anybody provide some "best practices" or guidelines for this?

我正在尝试设计一些表来存储一些数据，这些数据必须在以后转换为不同的语言。任何人都可以提供一些“最佳实践”或指导方针吗？

Thanks

谢谢

4 个解决方案

#1

Let's say you have a products table that looks like this:

假设您有一个如下所示的产品表：

Products
----------
id
price

Products_Translations
----------------------
product_id
locale
name
description

Then you just join on product_id = product.id and where locale='en-US'

然后你加入product_id = product.id和locale ='en-US'

of course this has an impact on performance, since you now need a join to get the name and description, but it allows any number of locales later on.

当然这会对性能产生影响，因为您现在需要一个连接来获取名称和描述，但它稍后允许任意数量的语言环境。

#2

I beleve that more information on what you are doing would be helpful. CAn you give some samples of the data? And what do you mean by dynamic? That there will be lots of data inserted over time and lots of changes to the data or that the data only needs to be available for a small period of time.

我觉得有关你正在做的事情的更多信息会有所帮助。你能给出一些数据样本吗？动态是什么意思？随着时间的推移会插入大量数据并对数据进行大量更改，或者数据只需要在一小段时间内可用。

#3

In general, you should probably be looking at a parent with common non-localized data, and a child table with the localized data and the language key. If by dynamic, you mean that it changes frequently, you may want to have a look at using triggers and something like a 'translationRequired' flag to mark things that are in need to translation after a change is made.

通常，您可能应该查看具有常见非本地化数据的父级，以及具有本地化数据和语言键的子表。如果通过动态，你的意思是它经常变化，你可能想要看一下使用触发器和类似'translationRequired'标志的东西来标记在做出改变后需要翻译的东西。

#4

Can you describe the nature of the 'dynamic data'?

你能描述一下'动态数据'的本质吗？

One way to implement this would be to have 3 different tables:

实现这一点的一种方法是拥有3个不同的表：

Language Table
- This table would store the language and a key :
- 该表将存储语言和密钥：
语言表该表将存储语言和密钥：

    [1, English], 
    [2, Spanish]

Data Definition Table
- When dynamic data is first entered make a record in this table with and identifier to the data:
- 首次输入动态数据时，请在此表中创建一条带有数据标识符的记录：
数据定义表首次输入动态数据时，在此表中创建一条带有数据标识符的记录：

      [1, 'Data1'], 
      [2, 'Data2']

Data_Language Table
- This table will link the language, data definition and translation
- 该表将链接语言，数据定义和翻译
Data_Language表此表将链接语言，数据定义和转换

      So: [Data_Language, Data_Definition, Language, Translation]
          [1, 1, 1, 'Red']
          [2, 1, 2, 'Rojo']
          [3, 2, 1, 'Green']
          [4, 2, 2, 'Verde']

          etc ...

When the dynamic data is entered create the default 'English' record and then translate at your leisure.

输入动态数据后，创建默认的“英语”记录，然后在闲暇时进行翻译。

#1