Database modeling for international and multilingual purposes

Question

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?

Answer 1

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