I have worked on very large application and we used additional columns for the translations.
In our case, the customer created a form (Field labels and input values stored in base language columns).
Then, using additional columns, they added translated version of the labels in the languages they wanted to support.
Our customer customers then visit the form, select the language they want and see the form in their native language.
When the user enters in a value into the form and saves, that was saved in the additional language field in the DB. This would leave the base language value empty. Our customers would then see that there was an answer in the native language and would get that translated and put in the base language field in the DB.
There are lots of challenges and consideration you need to make.
- Is google translate API really good enough? If yes, use that. If not, Who is going to translate and how?
- If you build a portal to allow translations, most translators charge by the word… so keep that in mind when building a tool they can use or that will allow export and import.
- If you are using forms, required fields need to be considered… if you are handling this on the backend
- right to left languages…like Arabic, it is not a great experience just displaying the translated text… the interface should also be right to left.