Schema for language internationalization

Hi folks,

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_fk (

    language_fk (

    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.


Anyone? :)

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

FROM whatever_table

JOIN the_translation_table

ON = whatever_table.translation_table_id

JOIN language_table

ON = 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 ???

Thank you.

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 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 :)