Doctrine 2 - best practices for i18n?

Question

I'm looking for some advise on how to handle tables that need to have fields that must be translated into n languages. I have read that the best approach its to have one base-table that holds all the fields that are not language specific and one table that holds all fields with the translations.

Example:

 PRODUCT             PRODUCT_TRANSLATIONS
+--------------+    +----------------------+
| id           |    | id                   |
+--------------+    +----------------------+
| category_id  |    | product_id           |
+--------------+    +----------------------+
| price        |    | language_id          |
+--------------+    +----------------------+
                    | name                 |
                    +----------------------+
                    | description          |
                    +----------------------+

So we will have the base table PRODUCT that holds all the meta data and a PRODUCT_TRANSLATIONS table where all data is stored that need to be translated into multiple languages. The PRODUCT table has a OneToMany relationship to the PRODUCT_TRANSLATIONS table.

What would be the Doctrine-way to handle such a scenario? If I query the Products table, I would get all translations joined to this table. But most of the time I only want to have one translation for a given product id. I could use a repository class to write my own getter methods to limit the result set to one language only, but I will have a lot of tables that have fields that need to be translated. I bet there is a more generic solution to that problem. Another problem is, that if I query for one object that is related to another object, I will get all translations for that second object.

Btw: I'm aware of the Translatable behavior extension by Gediminas but I don't like the fact that all translations are stored into one table.

So I'm looking for any best practices when it comes to internationalisation. Any thought on this topic is highly appreciated.

Answer 1

Btw: I'm aware of the Translatable behavior extension by Gediminas but I don't like the fact that all translations are stored into one table.

I believe your approach to this problem is mainly related to the size and type of your project. If you want to implement a simple CMS, you can also use an array for saving the content of multi language fields. However this approach is limited.

/**
 * @var array
 *
 * @ORM\Column(name="title", type="array", nullable=true)
 */
private $title;

If the content of your system is completely different from one language to another or your system needs some heavy queries and reports based on those multi language fields then the approach you mentioned would be good.