How to add localizations and versions to an MySQL entity

Question

Suppose I have a database table app that stores information about apps in an app store.

+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id           | varchar(36)  | NO   | PRI | NULL    |       |
| name         | varchar(255) | YES  |     | NULL    |       |
| developer    | varchar(255) | YES  |     | NULL    |       |
| description  | varchar(255) | YES  |     | NULL    |       |
| icon         | varchar(255) | YES  |     | NULL    |       |
| support      | varchar(255) | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+

Now I want to add localization and versions to the apps.
For example, I want to store description and support fields in different languages. Also, I want to store different description and icon fields for different app versions. The list of translatable and versioned fields is shown below:

+--------------+--------------+------------+
| Field        | Translatable | Versioned  | 
+--------------+--------------+------+-----+
| id           | NO           | NO         |
| name         | NO           | NO         |
| developer    | NO           | NO         |
| description  | YES          | YES        |
| icon         | NO           | YES        |
| support      | YES          | NO         |
+--------------+--------------+------+-----+

I need to implement new additional CRUD APIs

• to create, retrieve, update, and delete an app localization by id and language_code
• to create, retrieve, update, and delete an app version by id and version.

Now I wonder how to store different fields for different languages and versions. For example, I have an app with versions 1.0 and 2.0, description and support in English and Spanish, and also different descriptions in English and Spanish for version 1.0 and 2.0. Note that the id, name, and developer remain the same for all languages and versions.

Answer 1

First, I would keep the structure of app table as is, to make sure you have fallback values if the translation/version lacks an entry.

You can have a table like this:

localized_app(id, app_id, fieldname, fieldvalue, version_id, language_id)

So, you create a description entry like this:

(<some app id>, 'description', 'version 2.0', <some version id>, <some language id>)

an icon entry like this:

(<some app id>, 'icon', 'slkfhkshf', <some version id>, null)

and finally a support entry like this:

(<some app id>, 'support', skfsfks', null, <some language id>)

Naturally, you will need a table for versions and another for languages. Then, you can run something like this:

select ifnull(d.fieldvalue, app.description) as my_description ...
from app
left join localized_app d
on app.id = d.app_id and d.fieldname = 'description' and d.version_id = 1 and d.language_id = 2
left join localized_app i
on app.id = i.app_id and i.fieldname = 'icon' and i.version_id = 1
left join localized_app s
on app.id = s.app_id and s.fieldname = 'support' and s.version_id = 2

In the select clause we try to read the fieldvalue for description and if it did not exist, then fall back to the default in the app record. You can operate similarly for other fields. If you are worried about the performance, then, instead of fieldname you could use field id and create a new table along with foreign keys.