mysql国际化,MySQL数据库设计与国际化

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"

Each of these tables will share common i18n fields, like:

"title, alternative_title, short_description, description"

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?

or do something like:

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

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.

解决方案

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.

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.

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