Multi Language Database Design

I use to work on a multi language (English, Malay, Chinese) Android mobile app previously, which required to keep the value in database. There is no perfect solution to design a database to support this.

At first, I through the correct approach should be having another table to keep all translation text, every query on such table will need to join two tables.

table:
- product
column:
- id (product primary key)
- title_id (product_title reference key)
table:
- product_title
column:
- id (product_title primary key)
- key (en, ms, zh)
- value (Good Product, Produk Baik, 好产品)

But this approach will double up the original table size & add complexity to a simple SQL query.

Even if I plan to use only one special table to keep all the translation text.

table:
- product
column:
- id (product primary key)
- title_id (title reference key)
table:
- item
column:
- id (item primary key)
- title_id (title reference key)
table:
- title
column:
- id (title primary key)
- module (product, item)
- key (en, ms, zh)
- value (Good Product, Produk Baik, 好产品)

It is still not solving the need of joining two tables.

After some time, I start to go for second approach, where I store the whole translation text as a JSON string.

table:
- product
column:
- id (product primary key)
- title (JSON string in one line)
{
  "title": {
    "en": "Good Product",
    "ms": "Produk Baik",
    "zh": "好产品"
  }
}

It can be converted to Map<String> title & get the correspond text easily with title.get("en");.

Although this approach solve the join table issue, but it will be hard to make a SQL query search for the title column.

Until recently, I get to involve in a regional mobile app development, where countries like Hong Kong, Malaysia, Indonesia & Singapore are in the consideration.

For Hong Kong, en-GB, zh-CN & zh-TW are important.

For Malaysia & Singapore, en-US, ms & zh-CN are important.

For Indonesia, in is important.

A multi language database design need to be in placed again. So, for this round, I follow a solution I saw at Stack Overflow, to design a multi language SQLite database like an Android string resources.

/res
|
--/values
| |
| -- strings.xml  <-- contains default language values
|
--/values-zh-rTW
| |
| -- strings.xml  <-- contains Traditional Chinese language values
|
--/values-zh-rCN
| |
| -- strings.xml  <-- contains Simplified Chinese language values
| 
--/values-in
| |
| -- strings.xml  <-- contains Bahasa Indonesia language values
|
(etc)

default strings.xml file:

<?xml version="1.0" encoding="utf-8"?>
<resources>
     <string name="db_name">database</string>
     ...
</resources>

“zh-rTW” strings.xml file:

<?xml version="1.0" encoding="utf-8"?>
<resources>
     <string name="db_name">database_tc</string>
     ...
</resources>

“zh-rCN” strings.xml file:

<?xml version="1.0" encoding="utf-8"?>
<resources>
     <string name="db_name">database_sc</string>
     ...
</resources>

“in” strings.xml file:

<?xml version="1.0" encoding="utf-8"?>
<resources>
     <string name="db_name">database_in</string>
     ...
</resources>

Then get the corresponded database in DatabaseHelper by switching database when user change language.

public class DatabaseHelper extends SQLiteOpenHelper  {

    ...

    private DatabaseHelper(Context context) {
        super(context, context(R.string.db_name), null, dbVersion); 
        // R.string.db_name corresponds to the localized String 
        // resource "db_name", based on the users's selected Locale.
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        ... 
    } 

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        ...
    }

    ...

}

This approach seem to be a good fit on Android mobile app development. But it does have limitation as well, as the storage size will increased due to multiple databases introduce.

Which is the best approach for your software development?