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 ?


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

  • Is Price usually tied to country, but never to language? That is one clue of how to split the info. – Rick James Jan 14 '17 at 17:32
  • Be sure to use CHARACTER SET utf8mb4 for book titles, but ascii for country_code. – Rick James Jan 14 '17 at 17:33
  • Do not splay an array across columns in the table (solution 1). This has been discussed many times. – Rick James Jan 14 '17 at 17:34
  • yes price is tied to country but not language – user3416510 Jan 14 '17 at 17:37

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
  • yes but I would like to be able to get informations for all countries and languages at the same time – user3416510 Jan 14 '17 at 16:34
  • On the same row? – McNets Jan 14 '17 at 16:38
  • yes on the same row – user3416510 Jan 14 '17 at 16:47
  • Dynamic query for the existing languages, however, you'll get name_EN, name_IT, name_SP. – McNets Jan 14 '17 at 16:51
  • so, solution 3, with 1 join per country and language, so here 20 joins – user3416510 Jan 14 '17 at 16:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.