How to store a list of countries in the database for a multilingual site?

Question

Database design

Hello.
Tell me how best to store a list of countries in the database for a multilingual site?
What to store in the table with countries if the site is multilingual (many languages, more than 10) and each country needs a translation.
What to store in the table, where and how to store translations for countries?

Answer 1

The list of countries can be stored in configs. If you really want to - in the database, but without translation.
Well and then on a key to get transfer where it is necessary. It is not necessary to store translations in the database, unless these are some posts in an international blog.

Answer 2

Of course, each needs a translation.
You yourself will translate, or google translate, but you need it.
Store translations in a database or in files (as in wp ).

Answer 3

Can you make one table in the key of which there will be a country code, and in the columns there will be translations. Well, actually throw a request to the database by the country code and the code of the translation language.
Country and language codes are not a problem to find in Google.

Answer 4

I stored the translations as a json string, where the key is the language code, and the value is the translation.

Answer 5

Translations are stored in the translation store. It should be just lightning fast, it will be accessed a lot and often even by one user when rendering one page.
They do it in the simplest way - a key-value array, and so as not to load everything into the RAM at once (there will be several thousand transfers over time) - they are divided into groups and connected as needed one more group and one more group
Again - the RAM is installed on one computer. And if we have several sites, we need either in RAM on each site, or a very quickly responding server with translations. There's less of a problem to find by key than to provide a minimum ping. And if the RAM - then ensure the update on all translation sites and conflicts between updates at different times
The database stores the keys from this repository. After extracting the information, they copy the fields with the _lang subscript, extracting the necessary keys from the translations and substituting variables into the phrase
. I really liked the syntax of one of the WordPress modules that I saw many years ago. They say it also comes from ruby, but I have never written in ruby.
"[:en]hello world[:ru]hello, world[:by]will break, world"
and the _translate() function will be needed
later _translate_to_language() - when it will be necessary to show something in English in the Russian version
then _translate_international() - when you need to show on any version the basic language that most nations understand, for example
then _get_translate_array () which gives just an array for the front, in order to send translations in a bunch there and do _translate () there
thanks to such an entry, I can put both the translation key and the entire phrase in this syntax into the database, for example, if I have editing headers in the admin panel when which it makes no sense to change the global translation database. I'll just write the text divided in this way into the database and everything will work exactly for this record.
Over time, it becomes necessary to make translations with support for the endings "22 rubles, 25 rubles, 21 rubles", our syntax becomes:
"[:en]ruble[: ru1]rubles[:ru2]roubles[:en]rouble[:en1]roubles[:en2]roubles"
and the _getPluralIndexFromString() function will be needed
later we will need substitutions,
"[:en]hello, {=name}[:en]hello, {=name}"
and the _interpolate() function is needed. Why is it equal? To search, you could use while (strpos()) rather than a regular expression
, and of course you want to not constantly feed an array of key-value parameters into it, but simply pass them separated by commas so that the translation looks something like this
__('@path. type.key', $param1, $param2) and it itself understood that the first parameter should be put in the first
one, at some point it will seem that storing text for a couple of paragraphs in translations is a so-so idea, and there will be a need to make whole pages possible to do right away in Russian, that is, a language subfolder will appear in the templates folder and the template engine will first have to search for a page in the desired language
parsing all these phrases into a large array with nestings is at least a thousand times to apply the regular expression. so the converted translations are better stored in the cache. but there are more complex ways to implement, but faster in the end - to saddle .pot files and connect them, they seem to be indexed inside and can still track changes if they are parsed from the code with the right program.
it would seem that you can immediately make an array, why these regular expressions, because the only problem is that it is inconvenient to give texts to translators - they do not see the meaning of the message, reading it in one language, if you have en.php, ru.php files, and tell them "open the two together" - no one usually does, only complain about going bad. so in this syntax they immediately see
again, in this syntax, translation keys can be painlessly sorted alphabetically in any editor, because There are no line breaks as such - we use \n. and you can press F9 and everything becomes neat,
but in principle, I heard that they do it in some fast query language, so that they are not even stored in the database, but in elasticsearch with your own web page where you can hammer requests many times per second and that's okay, then there, indexes are manually built when uploading texts, but updating them is also a fun job;
it is most convenient to name the keys in the following convention
path_to_module.type.key
at first it seems that the phrase itself in English can be the key, but then you need to get English from the Russian text and bummer. so immediately the keys, no options
site_categories_form.title.product => "[:en]Product"
site_categories_form.option.product_empty => "[:en]-- CHOOSE A PRODUCT --"
Unfortunately, this is not a problem like "let's do translations" - this is a normal layer. Every time you stumble upon something that is inconvenient for translators, then you’ll update the hell, then a mismatch occurs between just a text and a phrase key, then languages ​​\u200b\u200bcrossed, then you can’t output in the desired language, then the redirect does not work. To collect all this, you will have to suffer.
This is how I am doing now, who knows smarter - please indicate where it is better as soon as possible, I will improve with pleasure