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_name <--- the name in English (for visual reference)
country_name <--- the name of the country in the language specified in the country_language.language_fk field
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.
Still struggling with this and I need to find a solution before I can really get into the database modelling for my app…
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
ON the_translation_table.id = whatever_table.translation_table_id
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?
Nope, it’s not. You can validate input using https://www.ietf.org/rfc/rfc5646.txt values.
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?
Yep. Validation/correction is quite easy if you have intl extension installed in PHP:
$normalizedLocale = Locale::canonicalize($language);
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.
Yes, it’s pretty common to validate/normalize incoming data. If you don’t want to depend on MVC, do it in the model.
Thanks again, you’ve been a great help