Which entities to translate and which not to save to a table?

Question

MySQL

There is a table that stores rating categories:

rateCategory

id_category | name
1                  Вкус
2                  Цвет
3                  Качество

There is a question about translating these words into other languages ​​for the client.

Does it make sense to create a separate table with translations:

rateCategoryTranslations
id | lang | name | id_category

Or is it better to put these translations into a dictionary on the client?
There are a lot of similar tables - requiring translation. I can't decide how best to proceed.

Answer 1

You still cannot contain all the words or texts in the database. There are many reasons. One of them is that support and innovations in the program will be created faster than taking care of the translation table. Then - all kinds of error texts, even when there is no connection to the database. Fasting - the world has long understood that not only texts are translated, but also pictures, icons or sounds. To do this, there are tools in the program editors. Then not only this field and only this table will be translated, but also a bunch of other fields and other tables.
Therefore, in your place for words or texts that can be translated and are in the database, I would suggest the following. options
1.in place of words or texts, either the texts themselves or, for example, numbers or key will be written for subsequent search in the dictionary. Those. the field is read from the database, it is checked if there is a translation for this key, if so, then the translation for the specific language is used. No, well, then what is available is issued. From the pros - You don't need to change anything in the database, only in the application. Minus - sometimes then there are words or values ​​​​in the database, with which nothing can be done at the level of SQL queries.
2. in principle, as well as the first option, but key does not replace the text, and the field itself turns into a combination of a value and, for example, through some set of characters in key, such as value{key=xxx} . From pluses, as well as in the first case. Of the minuses - the values ​​​​in the tables become not what they were intended for.
I don't like both the 1st and 2nd options, because they combine what is not needed - Values ​​in the database and translation for the client. Therefore, I will offer the 3rd option.
3. in addition to the fields that contain words or texts that can later be translated - add another one that contains a key for translation and for each field with words for translation. Those. if the key field is empty, then nothing is translated on the client side. If there is something, then at least there is a search in the dictionary.
Is there a translation for this key in the dictionary and how to organize it - this is a separate conversation. Here, as I said, you can work with editors' tools, you can also keep tables in the database - it all depends on the desire and capabilities - all this can be edited. Well, do not forget my first remark, when the App starts, and the connection to the database cannot be made. But this is sometimes one of the trivial cases, which is not necessarily taken as a basis.
And what I would not do:
- firstly, for one field in the database, I would not write a dictionary. The second field comes, so what? Neither id is suitable or you need to additionally write which table, etc.
- Also, I would not write different records in the same table, but expressing the same thing, but in different languages. And it does not matter with the same id or with a different one. The language in the client is the language. With product data, purchases, etc. language for displaying text has nothing to do.
I hope any of these ideas work.

Answer 2

The number of categories is very limited. I would generally put everything in one table:

CREATE TABLE rateCategory (
    category_group INT NOT NULL,    -- группа (категория того-сего) (возможно, также FK)
    category_id INT NOT NULL NOT NULL,  -- идентификатор категории в группе
    category_name_language CHAR(5) NOT NULL,    -- язык наименования (возможно, INT и FK)
    category_name VARCHAR(255) NOT NULL,   -- наименование категории на указанном языке
    PRIMARY KEY (category_group, category_id, category_name_language)
);

However, I don't insist on one table for everything...
This structure also allows you to set the priority of languages ​​- if there is no translation into the required language, the option in the default language is used. Moreover, the priority can be set general, for a group, and even for a single term.