Best Way to Create Reliable Database Design for Multi-Language

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

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,

  1. English
  2. Arabic
  3. 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
        )

)