I am working on a multi-lingual web application. This application will provide the search facilty to data related to products in different languages according to the clients locale information.
There are different approaches i have identified to store the multilingual data each have its own good and bad points.
One table per language
One column in table for each language
One row containing the data in each language with the language id
I want to have a good advice for it so that in future this design will help me for maintinance and w.r.t response time.
I will wait for your quick response.
Of the 3 approaches, only one allows for addition of further languages without having to create or alter tables - and therefore, without having to modify your application code either. That is the 3rd option with the row per language. The additional condition on queries ("and language_code=?") should not impact performance significantly.