0

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

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

1 Answer 1

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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