Storing multi-language data in JSON (MySQL)

Question

I'm currently designing a multilanguage website's database.

The website must be able to store it's data in an undefined number of languages (3 languages at the moment, can be more in the future).

My question is:

Is there any downside to storing website's field values in JSON strings in MySQL?

My usual approach to this problem was to have an extra table (var_translations), where I would store each translation in a new row:

languages - vars - var_translations

I'm thinking I could store all translations for a "var" in one same row, in the "vars" table, using a TEXT field that could store a JSON string contaning an array for the values, which I could later work with in PHP:

    {
     "name":{
             "EN": "Name", 
             "ES": "Nombre", 
             "FR": "Nom"
            }
    }

I'm not sure if there's anything wrong with this way of storing data, but I like how it supports multiple languages and it keeps the database cleaner and clearer.

Is there anything I should worry about this approach before I start implementing it?

Answer 1

It’s a perfect use of JSON in a database field.

Rolled this strategy out very successfully.

Otherwise you end up contorting your logical data structure to capture languages. And have a shed load of counter tables. The queries and performance become terrible too, especially if row is not present and want to have a default language where desired one not available.

We tended to name the fields with multilingual as a suffix

nameMultiligual {“en-gb”:”Name”, …., default:”en-gb”} hobbiesMultilingual {…}

Use locale codes though.

You can then have a locale mode in your session application and the business layers can take care of pulling the value from the json with session locale, so it’s as easy to use from the presentation layer.

Answer 2

Of course there's a downside : you can't do easy requests, for example to get the elements which don't have a translation in French.

The point of relational databases is to structure data. Don't depart from that logic if you don't have important reasons.

In that precise case, as you saw, it's easy to have a table i18n_name holding the translations.

If (and only if) it's confirmed you should store raw JSON, then you might want to have a look at DBMS having a good support of it, most notably PostgreSQL.