How to handle translations for many languages with many different contexts?

Question

Forever i have been doing my translations in the same table where object/element is created.
So for example if i work with posts and title of post needs a translation i do this -> post_title_en, post_title_fr, post_title_nl.
Well, this is quite terrible way to maintain application, you have to hard code in a lot of stuff in your code.

So i'm writing an application at the moment and i would like to break out of this bad habit to translate like this.

How would i approach translations if i don't know what kind of data length i have to hold in table fields?

I could have translation for post_title witch typically would be varchar(255) and i could have something like post_content witch typically would be text. And how would i reference all this with main object?

Any pointers are welcome. Thanks.

Answer 1

Unfortunately MySQL database is schematic. But you can do the trick.

Starting with version 5.0.3, if the ROW_FORMAT for table is set to "COMPACT", NULL values will never use any space in your database. So we could build the scheme like

Answer 2

Create another table consisting something like this:

• post_id (foreign ke to id field of posts table)
• field_type (title, content, etc.)
• language_id (the language)
• value (the string value)

so don't put any language affected values in the posts table (you can still put post author, date and time, etc).