MySQL Database I18N, a JSON approach?

"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) descriptionFROM      productLEFT JOIN product_textON        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.

This is not an extension. You loose all advantages of using a relational database. By way like yours you may use serialize() for much better performance of decoding and store data even in files. There is no especial meen to use SQL with such structures.

I think no problem to use columns for all languages. That's even easier in programming of CMS. A relational database is not only for storing data. It is for rational working with data (e.g. using powerful built-in mechanisms) and controlling the structure and integrity of data.

first thought: this would obviously brake exact searching in sql WHERE label='Homepage'second: user while search would be able to see not needed results (when e.g. his query was find in other languge string)