I am configuring a new db for a website that will have inputs in multiple languages - at present english and spanish, but this will expand to other languages in the future.

I need some advice for the best way to structure the db to support this scenario...multiple tables? one for english? another for spanish?

Also is there a translation function within mysql to support translation of data queried say from spanish to be read in english...depending upon the chosen language of the querier???

Any suggestions are appreciated.

Definitely not! This would mean rewriting all your SQL each time you add a new language and it would be a pain to look after.


I'm sure MySQL cannot do this but there are interfaces available for google translator. Not sure how good this is though.

You'll want to store a language code alongside each input and a similar code for each user. Difficult to offer more advice without knowing more about the application.

This db application is for a new social networking/dating website I am building. Basically english and spanish are the initial languages. I want people to be able to enter their info in their native language but I want their info queryable in another language, i.e. enter in spanish and someone speaking english can query your info and receive it in english....

Any suggestions???

I have to ask this - if some young lady has entered all her details in Spanish and I can only speak English then is there much point in me being able to read her details? or would you provide a translator for the initial date?

It would also be be very inefficient to search through tables while doing "on the fly" translations. Easiest to code would be having drop down lists for the things that people search for and then just store a code for each interest etc but store and display the correct translation for each language. This will be efficient but won't allow people to be very descriptive ie someone might be able to choose "I like animals" from a drop down but then probably won't be able to select "but I only like spiders".

Perhaps the drop down fields will be the only searchable fields while the text entry fields (in the users language) can be translated into other languages if a user wants to see them. You could also cache these translations so you don't have to translate them again.

Perhaps it would be better to translate everything the user types in into all the languages that the user can speak (no point showing the user to people who she can't communicate with). Then you could do searches on the translations.