Internationalizing Database Data Using Linked Localization Tables

\ In today's globalized world, applications need to be accessible to users from various countries and cultures. This requires support for multiple languages and content localization. In this article, we will explore how to internationalize data in a database using the Linked Localization Tables method, using my mobile fitness application called F/AI and Postgres database as an example.

What Are Linked Localization Tables?

The Linked Localization Tables method involves storing common data in one table and localized data in a related table. This approach allows for efficient management of translations and simplifies the addition of new languages without altering the structure of the main table.

Database Structure in the app

In the F/AI app, there is a primary table public.exercises that stores general exercise data, and a translations.exercises table that holds localized data.

\ Please keep in mind that the structure has been simplified to make it easier to understand.

The public.exercises Table CREATE TABLE public.exercises ( id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, key TEXT NOT NULL, type TEXT NOT NULL );

\ This table contains common exercise data such as the identifier, key, and exercise type.

The translations.exercises Table CREATE TABLE translations.exercises ( id BIGINT NOT NULL, language_code TEXT NOT NULL, name TEXT NOT NULL, description TEXT, PRIMARY KEY (id, language_code), FOREIGN KEY (id) REFERENCES public.exercises(id) ON DELETE CASCADE );

This table stores translations of exercise names, and descriptions in different languages. The primary key is a combination of the exercise id and the language_code.

Sample Data Data from public.exercises

| id | key | type | |----|----|----| | 1 | 3-leg-dog-pose | timebased | | 2 | 3-4-sit-up | repetitionbased | | 3 | 45-degrees-back-extension | repetitionbased | | 4 | 45-side-bend | repetitionbased | | 5 | 90-to-90-stretch | time_based |

Data from translations.exercises

| id | language_code | name | description | |----|----|----|----| | 1 | en | 3 Leg Dog Pose | This exercise is a yoga pose called the 3-leg dog pose. It involves starting in a downward-facing dog position and lifting one leg up towards the ceiling while keeping the hips square. The pose helps to strengthen the arms, shoulders, and core while also stretching the hamstrings and calves. | | 1 | ru | Поза собаки на трех ногах | Это упражнение представляет собой позу йоги, которая называется поза собаки на трех ногах. Она заключается в том, чтобы начать с позы собаки мордой вниз и поднять одну ногу к потолку, сохраняя при этом положение бедер прямоугольным. Поза помогает укрепить руки, плечи и корпус, а также растянуть подколенные сухожилия и икры. | | 2 | en | 3/4 Sit-up | This exercise involves lying on your back with your knees bent and feet flat on the ground. You then lift your upper body off the ground, curling your shoulders towards your knees, until you reach a 3/4 sitting position. This exercise targets the abdominal muscles. | | 2 | ru | 3/4 Подъем корпуса | Это упражнение заключается в том, что вы лежите на спине, согнув колени и поставив ступни на пол. Затем вы поднимаете верхнюю часть тела от пола, сгибая плечи к коленям, пока не достигнете положения сидя на 3/4. Это упражнение нацелено на мышцы живота. | | 3 | en | 45 Degrees Back Extension | This exercise involves lying face down on a back extension machine and lifting the upper body up towards the ceiling while keeping the legs and lower body stationary. The goal is to strengthen the muscles in the lower back and improve posture. | | 3 | ru | Разгибание спины на 45 градусов | Это упражнение заключается в том, чтобы лечь лицом вниз на тренажер для разгибания спины и поднять верхнюю часть тела к потолку, удерживая ноги и нижнюю часть тела неподвижными. Цель — укрепить мышцы поясницы и улучшить осанку. | | 4 | en | 45 Side Bend | This exercise involves standing with feet shoulder-width apart and holding a weight in one hand. The weight is then lowered towards the side of the body, bending at the waist, and then lifted back up to a standing position. This exercise targets the oblique muscles on the side of the body. | | 4 | ru | 45 Боковой наклон | Это упражнение заключается в том, что вы стоите, расставив ноги на ширину плеч, и держите гантель в одной руке. Затем гантель опускается в сторону тела, сгибается в талии, а затем снова поднимается в положение стоя. Это упражнение нацелено на косые мышцы по бокам тела. | | 5 | en | 90 to 90 Stretch | This exercise involves sitting on the floor with legs straight out in front, then reaching forward to touch toes while keeping legs straight. The stretch is held for a few seconds before releasing. It helps to improve flexibility in the hamstrings and lower back. | | 5 | ru | 90 на 90 растяжка | Это упражнение заключается в том, чтобы сесть на пол, вытянув ноги вперед, затем потянуться вперед, чтобы коснуться пальцев ног, держа ноги прямыми. Растяжка удерживается в течение нескольких секунд, прежде чем расслабиться. Это помогает улучшить гибкость подколенных сухожилий и поясницы. |

The sample data includes both English (en) and Russian (ru) translations.

Creating a View to Retrieve Localized Data

To conveniently access localized data, a view (VIEW) is used to join data from both tables and present it based on the selected language.

:::info You need to create similar views for each language you intend to support in your app.

Instead, you can create a function with the language_code argument to get the localized data. This will allow you to avoid code duplication.

:::

CREATE OR REPLACE VIEW public.v_exercises_ru AS SELECT DISTINCT ON (e.id) e.id, e.key, e.type, t.name, t.description FROM public.exercises e JOIN translations.exercises t ON t.id = e.id WHERE t.language_code IN ('ru', 'en') ORDER BY e.id, CASE WHEN t.language_code = 'ru' THEN 1 ELSE 2 END;

This view selects the Russian localization, and if it's not available, defaults to English by using the CASE WHEN construct in the ORDER BY clause.

How Does It Work?

1. Table Relationships: Each record in public.exercises can have multiple localizations in translations.exercises, identified by language_code.
2. View Logic: The v_exercises_ru view combines data from both tables, selecting the appropriate localization. If a Russian translation is unavailable, it falls back to English.
3. Data Retrieval: The application queries the view instead of directly accessing the tables, receiving already localized data.

Advantages of This Approach

• Scalability: Easily add new languages without altering the database structure.
• Flexibility: Ability to have multiple localizations for a single exercise.
• Convenience: Views simplify data access by hiding complex queries.

Output from the View

Example results from querying v_exercises_ru:

| id | key | type | name | description | |----|----|----|----|----| | 1 | 3-leg-dog-pose | timebased | Поза собаки на трех ногах | Это упражнение представляет собой позу йоги, которая называется поза собаки на трех ногах. Она заключается в том, чтобы начать с позы собаки мордой вниз и поднять одну ногу к потолку, сохраняя при этом положение бедер прямоугольным. Поза помогает укрепить руки, плечи и корпус, а также растянуть подколенные сухожилия и икры. | | 2 | 3-4-sit-up | repetitionbased | 3/4 Подъем корпуса | Это упражнение заключается в том, что вы лежите на спине, согнув колени и поставив ступни на пол. Затем вы поднимаете верхнюю часть тела от пола, сгибая плечи к коленям, пока не достигнете положения сидя на 3/4. Это упражнение нацелено на мышцы живота. | | 3 | 45-degrees-back-extension | repetitionbased | Разгибание спины на 45 градусов | Это упражнение заключается в том, чтобы лечь лицом вниз на тренажер для разгибания спины и поднять верхнюю часть тела к потолку, удерживая ноги и нижнюю часть тела неподвижными. Цель — укрепить мышцы поясницы и улучшить осанку. | | 4 | 45-side-bend | repetitionbased | 45 Боковой наклон | Это упражнение заключается в том, что вы стоите, расставив ноги на ширину плеч, и держите гантель в одной руке. Затем гантель опускается в сторону тела, сгибается в талии, а затем снова поднимается в положение стоя. Это упражнение нацелено на косые мышцы по бокам тела. | | 5 | 90-to-90-stretch | time_based | 90 на 90 растяжка | Это упражнение заключается в том, чтобы сесть на пол, вытянув ноги вперед, затем потянуться вперед, чтобы коснуться пальцев ног, держа ноги прямыми. Растяжка удерживается в течение нескольких секунд, прежде чем расслабиться. Это помогает улучшить гибкость подколенных сухожилий и поясницы. |

The view displays data in Russian, and if a Russian translation is not available for a particular exercise, it will display the English version.

Implementation in the Application

In the F/AI application, when requesting exercises for the user, the app queries the v_exercises_ru view, obtaining a list of exercises in the desired language.

\ Example query:

SELECT * FROM public.v_exercises_ru; Conclusion

Using linked localization tables is an effective way to internationalize data in a database. This method allows you to store common data separately from localized content, providing flexibility and scalability to the application. Views simplify data access by providing a unified interface for retrieving localized records.