3

I know this topic was discusses a couple of times, but none of them represents the ultimate solution for me.

Situation I'm designing a relational mysql database which later should hold multilingual content. You know this from the Wikipedia or Microsoft Tech Support Pages. The contents should be the same for every language. e.g If translations are missing the site offers you the same content automatically translated or in the languages which the information is available in. If some values are not set, it should fallback to the second or default browser language or translate it e.g. through google. Development environment is Zend.

My ideas so far are for Solving the Problem:

Two Primary Keys: (ID, Language) Advantage: Easy Database Access through database abstraction layers. Problem: Foreign Keys, Relations ships, Fallbacks

Columns with language suffix: Advantage: DB Performance, No relational Problems. Problem: Database abstraction layers cannot handle this?

Has any concept proven itself or is preferable over the other? Has anyone already created something like this and can share his experience with me? Does a modified Zend DB Controller exist for this situation? How do you link this information to a form?

Thank you for your help, hints and suggestions!

Kind regards,

Manuel

Manuel
  • 9,112
  • 13
  • 70
  • 110
  • i'd rather use doctrine I18n Behavior http://www.doctrine-project.org/projects/orm/1.2/docs/manual/behaviors/en#core-behaviors:i18n and save mt time a bit :) – tawfekov Jul 06 '11 at 13:00

3 Answers3

1

The second option would be not maintainable (this should be added on the minuses side). To actually add another language you'll need to modify table and abstraction layers. Sounds like a nightmare.

The first option seems much more promising but unfortunately there is a lot to do to make it work. However, from my experience this is rather typical solution, so I would not reinvent the wheel.
What I have to add is, language fallback should be done on the Zend side, database would miss some information. You may think of some kind of index table to hold information such as unique id of the contents and available languages. If you need to serve something, you would read such record, compare it against of Accept Languages and ask database again for valid contents (using the most suitable language). The only problem is, you would need to create such an index table somehow (the best way I see would be trigger on inserting contents to your content table).

A lot of work but the problem is not too easy.

Paweł Dyda
  • 18,366
  • 7
  • 57
  • 79
  • @tawfekov I think doctrine is doing exactly what Pawel describes? How does this behave if you have shared information? The shared information goes with the identity table and all language related stuff goes towards the referenced table? You search with joins? Is it possible to integrate doctrine into Zend? Is doctrine able to handle relational databases with languages? Do you know a good tutorial/example? – Manuel Jul 06 '11 at 13:57
  • @Manual For doctrine look at Zendcasts.com http://www.zendcasts.com/category/screencasts/databases/doctrine-databases/ and also check out the video podcasts by Jon Lebensold http://itunes.apple.com/us/podcast/zend-screencasts-video-tutorials/id300758106 – Adrian World Jul 06 '11 at 14:44
0

I am working on the exact same problem right now.

Somehow it does not make sense to me to add everything into the same database. Lets say I want to go to the extreme and support some 50 languages this would just bloat my DB. So, I tend to keep my main DB in my main language and then introduce some Zend_Translate concept into it. Zend_Translate should give you the fallback solution you are looking for. While the main navigation and core design is not much of a problem for my web site my biggest concern right now is how to store all the main content and how to translate because these elements contain HTML among other things. For the main content I will probably use some alternate approach and use a separate DB with tables for each language.

Adrian World
  • 3,118
  • 2
  • 16
  • 25
  • posted my idea of solving it underneath. What do you think of this idea? – Manuel Jul 07 '11 at 11:40
  • @Manuel I'm pretty much at the same point with a little difference. I'll use TMX and if I have a translation I will move the content for that column (i.e. even for the primary language) out to the tmx file and add a 'tmx' string. So, I will also check every column like you but if I get $value === 'tmx' I'll delegate your 1,2,3 language decision to Zend_Translate. – Adrian World Jul 07 '11 at 14:02
  • But this means you always come from one main language (probably english) and than translate into other languages? My inputs go from m:n languages. Examples: From German to Englisch, from Spanish to French. This will probably be pretty difficult with translation files. My core application will be translated with Zend Translate as well. – Manuel Jul 12 '11 at 13:24
  • Yes, I set a main language but it can be changed on the fly (not thoroughly tested yet, though). The default basically acts as the main fallback language. In between you can set a route (>1.11 I believe) and jump through a series of languages if a translation is not available. – Adrian World Jul 12 '11 at 14:05
  • Do you have experiences with using google translations? Thou leaving the page in the language of the author and translating contents with google or any other comparable translation service? – Manuel Jul 12 '11 at 14:18
  • Yes, I have cause I am bilingual (de-CH/en-US) and quite frankly it is awful. – Adrian World Jul 12 '11 at 14:39
  • It's still just typical robot translations. It's awful but i guess better than offering the user nothing? – Manuel Jul 12 '11 at 15:25
  • To some extend it's amazing what it does and works quite well for short sentences. If you like to use it I think it boils down to what kind of audience you have. – Adrian World Jul 12 '11 at 15:33
0

My plattform will be a community driven database. So I actually gonna rely on humans translating it. You have to store the information anyways, so my first concern is not the database size or performance, but easy usability. So far my idea is to implement some structure as described above, not yet sure if i'll do it in doctrine or not.

Language decision: Start, application gets users preset language, secondary language, english mother-tong of the article. Fetching the article from the database I will check the following for every column: 1. is the primary language available? 2. Is the secondary language available? 3. If neither of them, display article in mother-tong or english and offer the user to translate it with suggestions from the google translate api. I guess it's gonna be quite a bit of coating and manipulating controllers or building a business model doing this.

@tawfekov is something like this or similar easily realizable with doctrine?

Manuel
  • 9,112
  • 13
  • 70
  • 110