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
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.