Best Way to Create Reliable Database Design for Multi-Language

Last updated on May 10, 2023

If you’re you looking for a database design for developing web or mobile applications with multi-languages? Then you’re in the right place. This article is about database design that helps you to add a multi-language feature to your project.

As a developer, I have faced problems creating reliable database structures for multi-language. After spending days on research I finalized the structure that I am going to show in this article.

Advantages

Before going into details, below are the points that would be covered.

Simple and easy to understand
It can handle many languages
It contains only two tables
A new language can be added easily

Required Tables

We will be needing three tables for the multi-language feature.

languages table
keys table
translations tables

Languages table has great importance for this kind of database design. Specifically, the languages table should have two columns that are following,

id
name

This article will be covering three languages, that are following,

English
Arabic
French

Below are steps that need to be followed in order to make a well-functioning multi-language module.

Create a keys table, it should have three columns.

id
name
group

Then create a translations table with the following columns,

id
key_id
language_id
translate

Both tables can be created with the below script.

CREATE TABLE `keys` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `group` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `translations` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `key_id` BIGINT(20) NOT NULL,
  `language_id` INT(20) NOT NULL,
  `translate` TEXT NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4;

In the keys table, the key should be unique and each key would have multiple translations based on language id.

In the above keys table, sign_in is key and it has three translations based on language id.
Similarly, each key would have three translations. Group itself defines its role, it is grouping the keys. For example, if the login page has multiple keys then the login_page group helps to combine them. In other words, it would be easy to fetch all keys of a specific group using select query with where clause. Below is an example,

SELECT * FROM `keys` WHERE `group` = 'login_page'

Database Schema Design on dbdiagram

In this modern technology era, there are multiple online tools available that provide ease. The dbdiagram is the best and well reputed online tool that provides the facility to show database schema design diagrams by writing code.

We also created a diagram for multi-language database design, to check out click on this link.

Get Translations

There are multiple ways to get translations. In the below list,

Get Translation by Key
Get Translation by Group

Get Translation by Key

We have a method getTranslateByKey() below that fetches the translation by key.

    public function getTranslateByKey($key, $languageId)
    { 
        $query = $this->connection->query("SELECT * FROM `keys` LEFT JOIN `translations` ON `keys`.`id` = `translations`.`key_id`
            WHERE `keys`.`name` = '" . $key . "' AND `translations`.`language_id` =" . $languageId . " LIMIT 1");
        if($query->num_rows > 0){
            while($row = $query->fetch_assoc()){
                $translation[] = $row;
            }
        }
        return $translation;
    }

It has two parameters $key and $languageId and a query with a left join. It returns a row with the multidimensional array. Below is an example result

Array
(
    [0] => Array
        (
            [id] => 9
            [name] => sign_in
            [group] => login_page
            [key_id] => 1
            [language_id] => 1
            [translate] => Sign In
        )

)

Get Translation by Key In JSON Response

The previous method returns translations in array format, which can be converted into JSON responses or API. PHP built-in function json_encode() converts array into JSON, let’s apply it on translation array.

public function getTranslateJsonByKey($key, $languageId)
    { 
        $query = $this->connection->query("SELECT * FROM `keys` LEFT JOIN `translations` ON `keys`.`id` = `translations`.`key_id`
            WHERE `keys`.`name` = '" . $key . "' AND `translations`.`language_id` =" . $languageId . " LIMIT 1");
        if($query->num_rows > 0){
            while($row = $query->fetch_assoc()){
                $translation[] = $row;
            }
        }
        return json_encode($translation, JSON_PRETTY_PRINT);
    }

We have created a new method for getting translations in JSON response. It returns the below response,

[
    {
        "id": "9",
        "name": "sign_in",
        "group": "login_page",
        "key_id": "1",
        "language_id": "1",
        "translate": "Sign In"
    }
]

Get Translation by Group

As we have a group column in the keys table, we can fetch all keys that belong to a group.

public function getTranslateByGroup($group, $languageId)
{ 
    $query = $this->connection->query("SELECT * FROM `keys` LEFT JOIN `translations` ON `keys`.`id` = `translations`.`key_id`
        WHERE `keys`.`group` = '" . $group . "' AND `translations`.`language_id` =" . $languageId . " ");
    if($query->num_rows > 0){
        while($row = $query->fetch_assoc()){
            $translation[] = $row;
        }
    }
    return $translation;
}

This getTranslateByGroup() method fetches all keys from the database by provided group and returns the translations array. It has two parameters $group and $languageId. It returns an array same as below,

Array
(
    [0] => Array
        (
            [id] => 9
            [name] => sign_in
            [group] => login_page
            [key_id] => 1
            [language_id] => 1
            [translate] => Sign In
        )

    [1] => Array
        (
            [id] => 12
            [name] => email
            [group] => login_page
            [key_id] => 2
            [language_id] => 1
            [translate] => Email
        )

)

Conclusion

You need two tables keys and translation to create multilingual module in your project. In the keys table will have unique keys. Each key translate will be stored into translation table along with key_id and language_id.

Written by

Ayaz Ali Shah

I am a skilled full-stack developer with extensive experience in creating and deploying large and small-scale applications. My expertise spans front-end and back-end technologies, along with database management and server-side programming.

Share on: