Is this good design for multi-language of system t

问题:

Does this implementation scale well:

Requirement is:

1. System must support x number of languages; where x will = as many languages as business can translate

2. All system maintained values (page content, email content, values stored in the hundreds of lookup tables which are user facing) need to support multi-language.

My implementation:

Tables: (sample names used)

local_text_table  
language_lookup_table   

Content_table_1   
Content_table_2   
Content_table_3    
Content_table_4   
....

Plan:

language_lookup_table has list of all possible languages

lang_id   lang_name

local_text_table has list of all possible text used on system (emails, page content, menu labels, footer text etc) along with 1 colunm for each language that system will support - FK to the language_lookup_table.

text_id
eng_text
spanish_text
arabic_text
...

This way all translation is stored in 1 table for entire system. I can enable/disabled/update/edit/add/remove translations in 1 step. in the code all text is stored as a keyword referencing to (text_id). System detects what language the user's session is running and accordingly pulls the text from the column for that keyword. if a particular row is NULL (not translated) it will default to the English text column.

Is this good?

Of course this will not work for the lookup values stored in the hundreds of tables, for that I have no plan as yet apart from giving each table it's own colunms for each language. Then I have user content also to allow users to translate their user postings like blogs, comments etc for which I don't have a plan. But I want to fist focus on the system text and finalize that.

回答1:

Your design is flawed in that you won't be able to add a new language without adding a column to local_text_table.

A better design for that table would be:

text_id
lang_id (foreign key to language_lookup_table)
translated_text

Now you can add a language to language_lookup_table and then start adding translations to local_text_table without making any changes to your relational model. If you have the means to enter this data via a UI (or even directly in the database), you should be able to add new languages directly in production.

回答2:

Clearly you will need an intersection between every table you want to have support in multi language and language table. Also I recommend to use a flag in languages table with the meaning of "installed language" which means that in a particular implementation can be used only some useful languages. This flag will helps you to display in a list only interest languages, not all. Also, the language table you can find all LCID codes in Microsoft forums, already with LCID code which is already used and is common.