MySQL Database I18N, a JSON approach?

Question

UPDATE: I've come across this question I did after some years: now I know this is a very bad approach. Please don't use this. You can always use additional tables for i18n (for example products and products_lang), with separate entries for every locale: better for indexes, better for search, etc.

---

I'm trying to implement i18n in a MySQL/PHP site.

I've read answers stating that "i18n is not part of database normally", which I think is a somewhat narrow-minded approach. What about product namesd, or, like in my instance, a menu structure and contents stored in the db?

I would like to know what do you think of my approach, taking into account that the languages should be extensible, so I'm trying to avoid the "one column for each language solution".

One solution would be to use a reference (id) for the string to translate and for every translatable column have a table with primary key, string id, language id and translation.

Another solution I thought was to use JSON. So a menu entry in my db would look like:

idmenu label
------ -------------------------------------------
5      {"en":"Homepage", "it":"pagina principale"}

What do you think of this approach?

Answer 1

"One solution would be to use a reference (id) for the string to translate and for every translatable column have a table with primary key, string id, language id and translation."

I implemented it once, what i did was I took the existing database schema, looked for all tables with translatable text columns, and for each such table I created a separate table containing only those text columns, and an additional language id and id to tie it to the "data" row in the original table. So if I had:

create table product (
  id          int          not null primary key
, sku         varchar(12)  not null
, price       decimal(8,2) not null
, name        varchar(64)  not null
, description text          
)

I would create:

create table product_text (
  product_id  int          not null
, language_id int          not null
, name        varchar(64)  not null
, description text
, primary key (product_id, language_id)
, foreign key (product_id) references product(id)
, foreign key (language_id) references language(id)
)

And I would query like so:

SELECT    product.id
,         COALESCE(product_text.name, product.name) name
,         COALESCE(product_text.description, product.description) description
FROM      product
LEFT JOIN product_text
ON        product.id = product_text.product_id 
AND       10         = product_text.language_id

(10 would happen to be the language id which you're interested in right now.)

As you can see the original table retains the text columns - these serve as default in case no translation is available for the current language.

So no need to create a separate table for each text column, just one table for all text columns (per original table)

Like others pointed out, the JSON idea has the problem that it will be pretty impossible to query it, which in turn means being unable to extract only the translation you need at a particular time.