Performs this implementation scale well:
1) System must support x quantity of languages where x will = as numerous languages as business can translate.
2) All system maintained values (page content, email content, values saved within the 100s of research tables that are user facing) have to support multi-language.
Tables: (sample names used)
language_research_table has listing of all possible languages
local_text_table has listing of all possible text utilized on system (emails, page content, menu labels, footer text etc) together with 1 colunm for every language that system will support - FK towards the language_research_table.
the spanish language_text
By doing this all translation is saved in 1 table for entire system. I'm able to enable/disabled/update/edit/add/remove translations in 1 step. within the code all text is saved like a keyword referencing to (text_id). System picks up what language anyone's session is running and accordingly pulls the written text in the colounm for your keyword. if your particular row is NULL (not converted) it'll default towards the British text column.
Ofcourse this can not work with the research values saved within the 100s of tables, for your i've no plan up to now aside from giving each table it's own colunms for every language. I quickly have user content and to allow customers to translate their user posts like blogs, comments etc that i do not have an agenda. But i wish to fist concentrate on the system text and finalize that.
Your design is problematic for the reason that you will not have the ability to give a new language without adding a column to local_text_table.
A much better design for your table could be:
text_id lang_id (foreign key to language_lookup_table) translated_text
You can now give a language to language_research_table after which start adding translations to local_text_table without coming to a changes for your relational model. If you possess the way to enter this data using a UI (as well as directly within the database), you need to have the ability to add new languages directly being produced.
Find here various options as well as their benefits and drawbacks