I am currently building an app that is to be used by staff in different languages.
My first step with language translation is when I am planning the database and the reference tables I will use for relational data.
As an example, I will use a scenario with a reference table for countries.
My suggested model for this was the following: -
country
id
country_name <--- the name in English (for visual reference)
country_language
id
country_fk (country.id)
language_fk (language.id)
country_name <--- the name of the country in the language specified in the country_language.language_fk field
language
id
language_name
samdark very kindly gave me his feedback on this schema, suggesting the language table was obsolete. He also suggested I start threads specific to each task I am undertaking as it would be easier to follow, discuss and find later.
Any feedback on this would be gareatly appreciated.
I was thinking more about trying to do this using file translation as opposed to database translation but as far as I can see this will make translating the likes of grids near impossible without losing sorting and filtering.
I get the impression the above schema is pretty much correct except samdark suggested I didn’t need to the language table but I am not why… perhaps replace it with ENUM?
I am also going to have to look into exactly how Yii::t() calls the translation as I am not sure it I want to hold static text from pages in the db, rather than with a file.
Following on from that last thought, I think that I can freely use the Yii::t() function for general translations of label, paragraphs etc where as the t() function is not going to come into play with the likes of ActiveDataProvider as I can simply code the translation into the ActiveQuery: -
$strQuery - "SELECT these_columns, the_translation_table.translation
FROM whatever_table
JOIN the_translation_table
ON the_translation_table.id = whatever_table.translation_table_id
JOIN language_table
ON language_table.id = the_translation_table.language_table_id
WHERE language_table.language_code = '" . Yii::$app->language . "'
ORDER BY the_translation_table.translation";
Although this still relies on a language reference table, which samdark suggested I not use.
… and following on from that, now that I have a separation of concerns between the Yii:t() translation and the db/SQL based translation, I see this is more of a generic table schema question and perhaps not really specific to Yii2
So in the post_translation table the language field is not a foreign key? Presumably then you are entering the language code (i.e. ‘de’, ‘es’ etc)? If so, is this not leaving a huge risk of user error?
Ah, got you. So you mean basically there being no validation of his field in the schema itself due to a lack of a foreign key but you have a system where you only populate via the frontend and allow the model to handle the validation?
That’s an interesting way of doing it. I’ve been looking around on StackOverflow etc and I havent come across any examples of people doing it like that. Is it common to have the MVC to control the validation for scenarios like this? The upside is obvious - less joins. The downside being that in a way the schema is MVC-dependent.
