Multilingual database design: Coupled Translation table approach

1
votes

I've been doing some research about multilingual and recursive database design for few days. I found interesting the following article: http://www.gsdesign.ro/blog/multilanguage-database-design-approach/ and liked the 4th solution Coupled Translation Table Approach. But I'm seriously confused since I don't understand the case; I see there is only one column id on pages table, and I think I need to increase id value firstly on the pages table. After that I must choose id value and add data on the pages_translation table.

The problem is this: how do I know which page corresponds to the value of id? What should I do?

Can you give me some idea about the multilingual and recursive database design approach you're using?

Coupled Translation Table Approach

table languages

  • id (int)
  • name (varchar)

table pages

  • id (int)

table pages_translation

  • id (int)
  • page_id (int)
  • language_id (int)
  • title (text)
  • content (text)
1
database-designmultilingual
You pre-populate Pages with pages (page-ids) from your site (document). Also pre-populate Languages. Then load Pages-Translations. BTW, ID in Pages-Tanslation is redundant. – Damir Sudarevic
@DamirSudarevic,can you subject a little bit more?Plz! – user1478082

1 Answers

1
votes

I am using the first method proposed in the article. I cannot completely understand the 3rd and 4th methods, maybe because I'm not native English or maybe because I have not enough knowledge.

About the disadvantages and how I solve them:

  • You need to build the watch what column you are working with depending on the language

I am not sure what this means actually...

  • Hard to maintain. Although this is a easy way for 2-3 languages its becomes a real drag when you have a lot of columns or a lot of languages

This is truth if you use phpMyAdmin or similar. I just created some time ago a solution -for trusted people only- that helps me or said trusted translators to edit many and any string of text. It's not complete and I stopped developing it as it 'works' and my page needs a lot of work in other fields that are more important. As you can see, I can choose only the cols I want and not all at the same time, and also change from full text field to small input (long strings or short ones). Other script that helps also solves next disadvantage.

  • Hard to add a new language

I also created another script that helps translating the page string by string and is intended for the end user to collaborate..