Developing a product. There are countries and their cities. And actually the question is - how best to store this? about 250 countries, but more cities, and at times. + everything needs translation of each country and city.

One option is a DB with two tables. But how to deal with translations in this situation? add columns for each language - well, it seems to me.

You can store in files, and when the application starts, read into memory ... but it is.

What do you think about this?

  • And for what purposes reference books, that in them there will be actually except names. How they will be used, random sampling of individual records or obtaining complete lists. What type of database - with a dedicated database server or built into the application. - Mike
  • And yes, how often will the language switch, is it required to show names in several languages ​​at the same time - Mike
  • @Mike shows only one language. Well, I thought that the front (React) receives a list of all countries and stores it so that it would not make more requests. Well, in the place where it is necessary to choose a city, a request will be made for cities in a particular country and will also remain on the front. ~ 250 countries and it is not difficult in principle. And at the expense of transfers, the front itself will resolve what transfer to give. He is the main thing that would be the field with translations. - Tsyklop
  • It turns out such a thing that, according to the design rules, there must of course be a table of countries and a table of names to it, with id, language and name. But it is join at each request. As long as you get the name of one language, it’s not so scary If you have separate columns (which is not conveniently wild), or a json type column with literally {'RU':'США', 'EN':'USA'} which is clearly more convenient (in mysql you can get it as field->>'$.RU' ). But writing a table, if there are many languages, it turns out to be big and again you will get a drop in performance when reading. - Mike
  • But if you want to give the front at once with all the translations, then the selection from the normalized table with join becomes several times more stressful than to read the entire country record with the json field and send this json directly to the client - Mike

2 answers 2

It depends on how many language options. If 2-3, then in 2 bases with columns such as name_ru, name_en ... Also cities. If there are much more localizations, make them into separate databases. But lie to any scenario from fa will be read more slowly.

By the way, there are already many examples, can take a ready-made database and not reinvent the wheel?

  • Maybe there are ready. I do not know. I know that there are ready collections of countries and cities. - Tsyklop pm

Alternatively, you can store it in three tables:

Country table:

 id, country_name, lang_id

Table Cities:

 id, cities_name, lang_id, country_id

Language table:

 id, language_name

Example:

 SELECT COUNTRY_NAME, CITIES_NAME, LANGUAGE_NAME FROM COUNTRY LEFT JOIN CITIES ON COUNTRY.ID = CITIES.COUNTRY_ID LEFT JOIN LANGUAGE ON LANGUAGE.ID = COUNTRY.LANG_ID WHERE LANGUAGE_NAME IN ('EN'); --EN, RU, UK

Result:

  • just so you can not do exactly. Add the entry USA id 1. Language English. We must now add the Russian name. It is necessary to start a separate entry in the USA. And her id will already be 2. And now we need to use this directory and indicate the country somewhere and which id we should take 1 or 2 and how to switch the interface language to understand that everywhere where the USA was used should the USA be shown? - Mike
  • @Mike, added an answer, I think the request speaks for itself - Yaroslav
  • Request does not mean anything. Please output the English name of the country and the Russian names of cities in it. Or at least all the names of the same country (for example, countries with id = 1) - Mike
  • so the joke here is not to keep the name of the country with its translations under one id - Yaroslav
  • английское название страны и русские названия городов в ней why do this at all - Yaroslav