Multilingual around the globe with localization

Localization Simplified - Designing a Translation Table Model for I18n Implementation

March 14, 2022 • Dykraf

Website or web application with multilingual widely used by the company around the world for providing their product or information to the public.

Web Story

Multilingual database table model

Localization on website or web application has become a really important feature to have in almost company to have. The real purpose for these features is for widely users or the public to get to know their products or information.

Using databases such as MySQL recommended using database charset utf8mb4 and a different set of table collate type, recommended using utf8mb4_unicode_ci collate type. This is where the tables can store Unicode data standards which is needed for supported language for localization.

Below is the simple model for the translation table:

CREATE TABLE `content_i18n_simple` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `publishedAt` datetime NOT NULL,
  `title_en` varchar(2000) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `title_fr` varchar(2000) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `body_en` text COLLATE utf8mb4_unicode_ci,
  `body_fr` text COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Query for this table default english translation:

SELECT id, title_en, body_en, publishedAt FROM `content_i18n_simple` WHERE 1;

Or query for french translation:

SELECT id, title_fr, body_fr, publishedAt FROM `content_i18n_simple` WHERE 1;

The pros: The logic for querying this table will be much lighter since it will only query one table.

The cons: When the language has been added, the table fields, SQL query will have an update too. That will be needed a much work to handle just for the addition of a language.

Below is the table model for a much more advanced translation table, which is the separation of translations table:

CREATE TABLE `content_i18n` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `publishedAt` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `content_i18n_translations` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `content_id` int(11) NOT NULL,
  `locale` varchar(5) COLLATE utf8mb4_unicode_ci NOT NULL,
  `title` varchar(2000) COLLATE utf8mb4_unicode_ci NOT NULL,
  `body` text COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Join query for this tables:

SElECT content_i18n.id AS id, publishedAt, title, body
FROM content_i18n
INNER JOIN content_i18n_translations ON content_id = content_i18n.id
WHERE locale = "en";

For anything that is required for a bigger scaled translation system, these translation table models example should do the trick:

CREATE TABLE `content_i18n_adv` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `locale` varchar(5) COLLATE utf8mb4_unicode_ci NOT NULL,
  `title` varchar(2000) COLLATE utf8mb4_unicode_ci NOT NULL,
  `body` text COLLATE utf8mb4_unicode_ci,
  `publishedAt` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `content_localizations_links` (
  `content_id` int UNSIGNED DEFAULT NULL,
  `inv_content_id` int UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `content_localizations_links`
  ADD KEY `content_localizations_links_fk` (`content_id`),
  ADD KEY `content_localizations_links_inv_fk` (`inv_content_id`);

ALTER TABLE `content_localizations_links`
  ADD CONSTRAINT `content_localizations_links_fk` FOREIGN KEY (`content_id`) REFERENCES `content_i18n_adv` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `content_localizations_links_inv_fk` FOREIGN KEY (`inv_content_id`) REFERENCES `content_i18n_adv` (`id`) ON DELETE CASCADE;
COMMIT;

--
-- Table structure for table `i18n_locale`
--

CREATE TABLE `i18n_locale` (
  `id` int UNSIGNED NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `code` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `createdAt` datetime(6) DEFAULT NULL,
  `updatedAt` datetime(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Localization i18n_locale tables should provide languages available in the system. The content_localizations_links will hold the parent and child to keep the foreign key from content_i18n_adv that holds the content from the fields, title, and body data translation. When the data from content_i18n_adv is deleted the data that holds the foreign key from the content_localizations_links table will automatically delete.