Localization with mysql/PHP

Question

I'm currently building a multilingual website using PHP and MySQL and am wondering what the best approach regarding localization is with regards to my data model. Each table contains fields that need to be translated (e.g. name, description....) in several languages.

The first idea was to create a field for each language (e.g. name_en, name_de, name_fr) and retrieve the appropriate field from PHP using a variable (e.g. $entry['name_' . LANGUAGE]). While it would work, this approach has in my opinion many drawbacks:
-you need as many occurrences of each field as you have languages (bearing in mind you can have en-US, en-CA, en-GB...)
-if you add or remove languages you need to modify the database structure accordingly
-if you have untranslated fields, they are still created for each entry which doesn't seem very optimized

The second idea is to create a translation table that can be used to store the translation of any field of any table in the database:

----------------
translation
----------------
id  INT
table_name VARCHAR
field_name VARCHAR
value VARCHAR
language_id VARCHAR

The table_name and field_name will allow identifying which table and which field the translation is about, while language_id will indicate which language that translation if for. The idea is to create models that would replace the value of the translatable fields (e.g. name, description) by their corresponding translation based on the language selected by the user.

Can you see drawbacks with this approach? Have you got suggestions to make?

Thanks.

Answer 1

The main drawback is that you destroy the relational model by storing metadata like table name and field name as application data. You queries would be too ugly and non-effective.

Another drawback is that you are limited only to one data type of the translatable data. Your table structure would define

value VARCHAR(255)

which means you would store data that would require smaller field always in VARCHAR(255). And if you like to have it even more universal to store also large text you need to define it

value TEXT

which is even worse.

The popular model is the following. For every entity you define the fields which are not language dependent and those which are language dependent and create always 2 tables. For example:

products
--------
id
price
status
picture

products_translations
--------
product_id
language_id
name VARCHAR(100)
description TEXT

This is the proper relational approach. Of course, it also has drawbacks major one being that you would always join 2 table to fetch items and adding/updating of data becomes a bit more complex.

Answer 2

Not sure if this answer will satisfy you, but I discern between two types of texts:

1. static
2. dynamic

Static text is provided by yourself for general application text that users have no influence on. Stuff like form input labels and introductory text. I use gettext for those, so I can send it off to professional translators, if i need it translated.

Dynamic text is text provided by the user of the application, which seems to be what you're talking about. Personally, I discern dynamic text into 2 different types as well.

1. generally applicable
2. specific

An example of the general type would be options inside of HTML select elements, or a tagging system. They're not specific to a single content element, but (can) apply to multiple ones. Examples for a specific text would be the actual content inside of a CMS like an article, or a product description in an online shop.

For the first one I use a kind of central lookup table with a hash of the actual, original text as the index, which i refer to as a foreign key in tables where i use that string. Then you look up that hash in the central table to echo the real text behind it (of course, you ought to use some sort of caching here).

For the latter one I use a classic content table with columns for every content area specific to that logical content unit and a row for each language.

Thus far it's working out pretty well.