MySQL Database Design with Internationalization

Question

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.

Answer 1

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