FuelPHP ORM database schema for i18n, opinions/suggestions

Question

While this question might be similar to many others, I'd like to ask for opinions/suggestions on the best approach for i18n specificaly on FuelPHP.

So, here is what I've got so far:

Database schema #1:

models (id, name_pt, name_es, name_en, description_pt, description_es, description_en)

Sample data #1:

(1, 'Modelo', 'Modelo', 'Model', 'Descrição do modelo', 'Descripción del modelo', 'Model description')

Pros:

Straight and simple
One table per model
No need to use JOIN
Use of a magic method to simplify data access:

public function & __get($property)
{
    if (array_key_exists($property, array('name', 'description')))
    {
        $property = $property.'_'.Session::get('lang_code');
    }

    return parent::__get($property);
}

This way, I'm able to do call:

$model->name;
$model->description;

instead of:

$model->{'name_'.Session::get('lang_code')};
$model->{'description_'.Session::get('lang_code')};

Cons:

Having lots of languages/translated fields might get messy.
Adding a new language, implies adding new fields to the table
The magic method only works when we already have an ORM instance/object. To fetch an ORM instance through the query builder ordered by a translated field it's still required code like:

Model_Model::query()
    ->order_by('name_'.Session::get('lang_code'))
    ->get();

Database schema #2:

languages (id, code, name)
models (id)
i18n_models (id, model_id, language_id, name, description)

Sample data #2:

-- languages
(1, 'pt', 'Português')
(2, 'es', 'Español')
(3, 'en', 'English')

-- models
(1)

-- i18n_models
(1, 1, 1, 'Modelo', 'Descrição do modelo')
(2, 1, 2, 'Modelo', 'Descripción del modelo')
(3, 1, 3, 'Model', 'Model description')

Pros:

Better data organization
Adding a new language is a snap
Like in the first approach, we can also have a direct data access using the set() method to populate the $_custom_data array:

$i18n = Model_I18n_Model::query()
    ->where('model_id', $model->id)
    ->where('language_id', Session::get('lang_code'))
    ->get_one();

$model->set(array(
    'name' => $i18n->name,
    'description' => $i18n->description
));

Cons:

Complexity increases
A JOIN or a second query must be used
An extra table for each model is required

Database schema #3:

On other questions, I've seen people suggest the use of a central i18n table for all the translations, using a row for each translation a model has.

Pros:

Single table for i18n shared between models
Adding a new language should be easy as in the previous approach

Cons:

Complexity increases while fetching data, requiring a JOIN for every translated text a model has
We could try using EAV containers with this approach, although that uses a key/value for mapping, but in this case in particular we also have to use the language_id to fetch the proper translation.

Personaly, I prefer the second approach. What other advantages/disadvantages do you see? Has anyone implemented i18n in a different way on FuePHP? Share your ideas :)

Michael Pawlowsky Michael Pawlowsky · Accepted Answer · 2014-04-17T21:13:32

What I simply do is add a lang field in the the table.

Then I filter on that field:

SELECT * FROM articles WHERE lang = 'en'

I even use it in CRUD for admin sections where the user can switch languages, and they see all the entries for that specific language.

And an editor will be automatically working for content in the language he is in.

INSERT INTO articles VALUES('My Title', 'My Article', 'en')

And simply get 'en' from the users current local. (I do allow them to change in forms though to override it).

FuelPHP ORM database schema for i18n, opinions/suggestions

1 Answers