Database modeling for international and multilingual purposes

Database modeling for international and multilingual purposes

Here is the way I would design the database:

Visualization byDB 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 contentfrom newsletter n-- join for subjectinner join translation t_subject  on t_subject.id_i18n = n.i18n_subject-- join for contentinner join translation t_content  on t_content.id_i18n = n.i18n_contentinner 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 conditionwhere 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).