Database modeling for international and multilingual purposes

I need to create a large scale DB Model for a web application that will be multilingual.

One doubt that I've every time I think on how to do it is how I can resolve having multiple translations for a field. A case example.

The table for language levels, that administrators can edit from the backend, can have multiple items like: basic, advance, fluent, mattern... In the near future probably it will be one more type. The admin goes to the backend and add a new level, it will sort it in the right position.. but how I handle all the translations for the final users?

Another problem with internationalization of a database is that probably for user studies can differ from USA to UK to DE... in every country they will have their levels (that probably it will be equivalent to another but finally, different). And what about billing?

How you model this in a big scale?

Here is the way I would design the database:

Visualization by DB Designer Fork

The i18n table only contains a PK, so that any table just has to reference this PK to internationalize a field. The table translation is then in charge of linking this generic ID with the correct list of translations.

locale.id_locale is a VARCHAR(5) to manage both of en and en_US ISO syntaxes.

currency.id_currency is a CHAR(3) to manage the ISO 4217 syntax.

You can find two examples: page and newsletter. Both of these admin-managed entites need to internationalize their fields, respectively title/description and subject/content.

Here is an example query:

select
  t_subject.tx_translation as subject,
  t_content.tx_translation as content

from newsletter n

-- join for subject
inner join translation t_subject
  on t_subject.id_i18n = n.i18n_subject

-- join for content
inner join translation t_content
  on t_content.id_i18n = n.i18n_content

inner join locale l

  -- condition for subject
  on l.id_locale = t_subject.id_locale

  -- condition for content
  and l.id_locale = t_content.id_locale

-- locale condition
where l.id_locale = 'en_GB'

  -- other conditions
  and n.id_newsletter = 1

Note that this is a normalized data model. If you have a huge dataset, maybe you could think about denormalizing it to optimize your queries. You can also play with indexes to improve the queries performance (in some DB, foreign keys are automatically indexed, e.g. MySQL/InnoDB).

Some previous StackOverflow questions on this topic:

• What are best practices for multi-language database design?
• What's the best database structure to keep multilingual data?
• Schema for a multilanguage database
• How to use multilanguage database schema with ORM?

Some useful external resources:

• Creating multilingual websites: Database Design
• Multilanguage database design approach
• Propel Gets I18n Behavior, And Why It Matters

The best approach often is, for every existing table, create a new table into which text items are moved; the PK of the new table is the PK of the old table together with the language.

In your case:

1. The table for language levels, that administrators can edit from the backend, can have multiple items like: basic, advance, fluent, mattern... In the near future probably it will be one more type. The admin goes to the backend and add a new level, it will sort it in the right position.. but how I handle all the translations for the final users?

Your existing table probably looks something like this:

+----+-------+---------+
| id | price | type    |
+----+-------+---------+
|  1 |   299 | basic   |
|  2 |   299 | advance |
|  3 |   399 | fluent  |
|  4 |     0 | mattern |
+----+-------+---------+

It then becomes two tables:

+----+-------+   +----+------+-------------+
| id | price |   | id | lang | type        |
+----+-------+   +----+------+-------------+
|  1 |   299 |   |  1 | en   | basic       |
|  2 |   299 |   |  2 | en   | advance     |
|  3 |   399 |   |  3 | en   | fluent      |
|  4 |     0 |   |  4 | en   | mattern     |
+----+-------+   |  1 | fr   | élémentaire |
                 |  2 | fr   | avance      |
                 |  3 | fr   | couramment  |
                 :    :      :             :
                 +----+------+-------------+

2. Another problem with internationalitzation of a database is that probably for user studies can differ from USA to UK to DE... in every country they will have their levels (that probably it will be equivalent to another but finally, different). And what about billing?

All localisation can occur through a similar approach. Instead of just moving text fields to the new table, you could move any localisable fields - only those which are common to all locales will remain in the original table.