Mysql – How to handle database of multiples languages and countries

innodbMySQLpivot

Imagine that I have a database of books. I would like to store multiples informations for multiples languages and countries.
Imagine that I would like to support 10 countries and 10 languages.

I want to store at least the name and description for each language.
The price, the currency and availability, the release date for each country.
And other rows.

Here are my options to store this on database and have the possibility with one request to get the infos for all countries and languages at a time :

Solution 1
– have one column per language and country, but in the end I have 100 columns

Solution 2
– have a table "price", a table "currency", "availability" etc, which store the information for one country in one row.
The same for "name", "description" which store the information for one language in one row

solution 3
– have 2 tables, one will contain every data for one country (so one row contain the price, the currency, the availability etc for one country and book). And the other the same but for one language (and contain the name and description for one language per row).

solution 1 makes it easy to retrieve informations for all countries and languages at a time, but this makes a very large table (i have millions of rows) and I believe it is slower to query this table due to number of columns

solution 2, the problem is to retrieve infos for all countries and languages will need one join for each lang and country, so I end up having a lot of join and be stuck with the maximum join limit (61 ?)

solution 3, will lower the number of joins (but still one join per lang and one per country).

What is the best ? How do people do when they want to show informations like that ?

thanks

EDIT:
What do you think of this solution, which is kind of solution 2, but with only one join for each table, instead of one join per language and per country :
http://sqlfiddle.com/#!9/7ae955/1

Best Answer

Normally I use two tables, main table has default language, just in case you forget to add corresponding values into translations tables. 

create table books (
    book_id int,
    book_name varchar(200),
    description varchar(500)
)

create table books_language (
    book_id int,
    language_id vachar(10),
    book_name varchar(200),
    description varchar(500)
)

This returns all records including default language.

select book_id,
       isnull(books_language.laguage_id, 'default')
       isnull(books_language.name, books.name) as name,
       isnull(books_language.description, books.description) as description
from books
     left join books_language
     on books.book_id = books_language.book_id
Related Question