MySQL数据库设计与国际化 [英] MySQL Database Design with Internationalization

问题描述

我要开始在中型应用程序上工作，我计划它的数据库设计。
我不知道的一件事是这个。
我将有很多表需要国际化，例如：membership_options，gender_options，language_options等

I'm going to start work on a medium sized application, and i'm planning it's db design. One thing that I'm not sure about is this. I will have many tables which will need internationalization, such as: "membership_options, gender_options, language_options etc"

每个表都将共享同一个i18n字段，例如：
title，alternative_title，short_description，description

Each of these tables will share common i18n fields, like: "title, alternative_title, short_description, description"

在你看来，这是最好的方法吗？
为每个需要它们的表具有相同的字段的i18n表？

In your opinion which is the best way to do it? Have an i18n table with the same fields for each of the tables that will need them?

或者做类似的操作：

Membership table     Gender table
----------------     --------------
id | created_at       id | created_at
1  -  22.03.2001       1 - 14.08.2002
2  -  22.03.2001       2 - 14.08.2002


General translation table
-------------------------
record_id | table_name | string_name | alternative_title| .... |id_language
 1        - membership   regular         null                     1 (english)
 1        - membership   normale         null                     2 (italian)
 1        - gender       man             null                     1(english)
 1        -gender        uomo           null                      2(italian)

这将避免我重复以下内容：

This would avoid me repeating something like:

membership_translation table
-----------------------------
membership_id | name | alternative_title | id_lang
1               regular  null             1
1               normale  null             2

gender_translation table
-----------------------------
gender_id | name | alternative_title | id_lang
1           man     null                1
1           uomo    null                2

所以我可能会减少db表的数量，但我不知道性能。我不是一个DB设计师，所以请让我知道。

and so on, so i would probably reduce the number of db tables, but i'm not sure about performance.I'm not much of a DB designer, so please let me know.

推荐答案

我看到这样做的最常见的方法是两个表， membership 和 membership_ml ，其中一个存储基本值，ml表存储本地化字符串。这与您的第二个选项相似。我看到的大多数系统都是这样做的，因为它们在设计时没有考虑到国际化，所以额外的_ml表稍后会被粘贴。

The most common way I've seen this done is with two tables, membership and membership_ml, with one storing the base values and the ml table storing the localized strings. This is similar to your second option. Most of the systems I see like this are made that way because they weren't designed with internationalization in mind from the get go, so the extra _ml tables were "tacked on" later.

我认为更好的选择与你的第一个选项相似，但有点不同。您将有一个中央表来存储所有的翻译，但是不使用表名和字段名称，您将使用令牌和一个中央内容表来存储所有翻译。这样，你可以在基表中的标记和Content表中的转换之间强制执行某种RI。如果你想的话。

What I think is a better option is similar to your first option, but a little bit different. You would have a central table for storing all the translations, but instead of putting the table name and field name in there, you would use tokens and a central "Content" table to store all the translations. That way you can enforce some kind of RI between the tokens in the base table and the translations in the Content table if you want as well.

我实际上是在一段时间内对这个问题提出了一个问题，因此您可以查看一些更多的info（而不是在这里重写模式示例）。

I actually asked a question about this very thing a while back, so you can have a look at that for some more info (rather than repasting the schema examples here).

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