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
CHARACTER SET utf8mb4
for book titles, butascii
forcountry_code
.