Multilingual site… not a blog… how?

Question

SQL

How to make a database structure of a multilingual site? (1) Many related tables are assumed: tariffs, access levels, object names, object classifiers, etc. (2) And there is a graphic content part common to all languages ​​(graphic pictures-diagrams), where the language is not important (a picture is a picture ... what is the language). (3) And there is some text content "à la" blog.
How to make a multilingual blog is more or less clear. We make a field with a language attribute, and we do the formation of a tape in the desired language through this attribute.
It is not necessary to make the content-graphic part multilingual.
And how to make tables of links and features multilingual? They contain signs of the state of objects, classifiers, class descriptions, access levels, billing, account status, and so on, so on, so on? object class). And these descriptive fields should also be made multilingual. The number of languages ​​is arbitrary (but finite).
The idea comes first - to make separate fields for each language. But such a scheme is not extensible. Those. if a new language appears, you will have to create new fields (and there may be many languages). At the level of existing applications, the appearance of new fields will not change much. But on the other hand, you will have to write new applications for each language (however, if the fields are named smartly, then you can simply “calculate” at the application level which fields to pull for which language.
The second idea is to make separate tables with descriptive characteristics for each link table, and link this table via many-to-one with the table of languages ​​and the links themselves. Everything is beautiful, but the database structure becomes more complicated and can start to slow down. Now, when forming queries of the JOIN type, more tables will have to be merged together (two longer in each query), the total number of queries will increase, and this can be fraught.
The third idea is to write descriptive fields in some markup language and parse this markup at the application level. For example, a description of account types would be"[ru::Золотой][en::Gold][jp::ゴールド]"... The advantages are obvious, but it can start to slow down the application. After all, some descriptive fields are really large (for example, a description of the rights and opportunities available to owners of a "golden" account). In addition, this will require ALL descriptive fields to be made redundant or floating length, and this slows down the DBMS ...
Any other ideas? Maybe there are traditional tricks for resolving multilingualism tested on CMS?

Answer 1

I don’t understand why everything is so complicated when everything fits into a simple scheme. All site content can be conditionally divided into 3 parts:
1) rarely changing data (they are usually hardcoded into a template file);
2) frequently changing data or content generated by users/administrators/moderators;
3) binary data in the spirit of pictures, attached files.
All statics are located in the skin/skin_name/color_scheme/language folder (for example, /skin/modern/red/ru, I have /skin/default/default/ru by default), in the database, any text content requiring translation has columns for each required target language. These can be columns with a postfix original_name_language (for example, topic_content, topic_content_ru) for mysql or table inheritance from postgresql.
Implementation:
1) all text information is concentrated in template files using gettext.
2) postfix columns in all tables.
3) static images associated with the skin are distributed over language folders, addresses to dynamically added images are stored in the database and when requested according to clause 2, we get the address to the desired language image.
The only difficulty you might run into here is gettext and translations that require context. Since the translator receives a po file in his hands, he does not know on which page of the site this or that word is used and what context it has. But this is not a very common case and simply requires the involvement of a developer.

Answer 2

Move everything related in translation to the frontend

Answer 3

The “first idea” is quite extensible, the fields are added quietly, the choice of a competent naming will allow you to do everything automatically. Plus, for languages, you can make a separate table so as not to touch the main ones (but put all the languages ​​in one line).
The “second idea” is good in terms of “make tables”, but you can not complicate it with “joins” (there is no big complication in direct selections, but nevertheless) ... Joins are mainly needed when information from a joining table is “needed right now” . In the case of languages, it is enough for you during the application to simply write the language IDs for the join into the array (instead of the language table join for them), and then at the end of the application make a request to the table, pulling out all the language variables by ID from this array at once and substituting them into the template .
As for the pictures, don't be so specific. They are also multilingual, for example, the same infographics or buttons.

Answer 4

Just put all the "translated" things in separate tables with a language field. Let's say if it will be a "blog" and its content should be translatable, then there will be 2 tables: Title, Content. Each table will have a language field (language = en/ru/de/jp). Then select them accordingly.
ps: This is how multilingual content is implemented in Drupal CMS. See how they did it in detail.