language data in JSON (MySQL)

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

This 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, witch 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 in this approach before I start implementing it?

---

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.

链接地址: http://www.djcxy.com/p/37502.html

上一篇: 多语言数据库，默认回退

下一篇: JSON中的语言数据（MySQL）