Multi-language database

Question

I am developing an Android application that supports Arabic and English, but I am still confused about the database design:

The approach I am following is

creating a language table that holds two records (Arabic and English)
creating a table that holds normal data (not language specific)
creating a translation table that holds language specific content

For example to implement (Help) I created the following tables:

Language        (int id, text Code)
Help            (int id)
Help_translation(int id, int Help_id, int Language_id, text Question, text Answer)

I have read this solution so many time, but I still don't know why do we have a separate table for (Help) that does nothing!

Is there something wrong about my approach?

Jonathan Leffler Jonathan Leffler · Accepted Answer · 2012-12-29T22:02:52

On the face of it, the Language table will appear once in the system. I suggest using the 'll_tt' notation for language and territory based on the ISO country codes (ISO 3166) and ISO language codes (ISO 639-2). Thus, you might have en_gb for British English, en_us for US English, and ar_sa for Saudi Arabian Arabic, and ar_eg for Egyptian Arabic. This may be more powerful than you currently need, but gives you a direction for the future.

Thus, the Language table might have columns:

id integer (autoincrement) Primary key.
code char(5) — Unique.

The Help table in your design is for 'documentation' purposes. It records the valid help numbers that the application can use. A fuller version of the table might include information about the message: where it is used in the application; which version it was introduced in; which version it became obsolete with (or maybe not — we have internationalized message files that have to be good for use with releases over a period of about 10 years); dates associated with the version — or dates instead of the version; and 'notes to the translator' (guidelines for how to translate the message, if any special guidelines are needed).

The minimalistic version of the Help table with just the Help ID number will do for the time being:

id integer (autoincrement). Primary key.

The Help_Translation table stores the strings that the application will display. The id column is of minimal value here; I would omit it (but you can keep it if you wish). The Help_ID column is a foreign key reference to the Help table; the Language_ID column is a foreign key reference to the Language table. For your chosen application, there appears to be both a question and an answer for each help item.

Therefore, the Help_Translation table has the columns:

id integer (autoincrement, optional, not sure when you'll use it).
Help_ID integer — foreign key references Help(ID).
Language_ID integer — foreign key references Language(ID).
Question text — the question in the appropriate language for the given Help_ID.
Answer text — the answer to the question in the appropriate language for the given Help_ID.
Primary key: Help_ID and Language_ID.

An alternative design would have simply a translation table with a 'Message ID' and a 'Language ID' and the translated string (primary key on Message ID and Language ID). There would be a table of Messages, identifying the valid Message IDs plus supporting data of the type outline previously. This might include the default message to be used (untranslated) when there is no translated version of the message for a particular language/territory (or you can make up more complex schemes for handling missing messages, so that when Arabic is requested but the translation for, say, Libya (LY) is incomplete, it falls back onto ar_sa and only then onto en_gb). Your Help table might then contain a Help ID, plus two Message ID values, one for the Question and one for the Answer. The advantage of this scheme is that all translated messages are in a single table.

There are undoubtedly other schemes that could be devised.

Multi-language database

1 Answers