这是很好的设计为多语言的系统文本支持x数量的langs？ [英] Is this good design for multi-language of system text to support x number of langs?

问题描述

此实施是否可扩展性良好：

需求为：

b $ b

1. 系统必须支持x种语言;




2. 所有系统维护的值（页面内容，电子邮件内容，存储在数百个查找表中的值用户面对）需要支持多语言。

我的实现：

表格:(使用的示例名称）

  local_text_table 
 language_lookup_table 
 
 Content_table_1 
 Content_table_2 
 Content_table_3 
 Content_table_4 
 .... 
  

计划：

language_lookup_table具有所有可能的语言列表

  lang_id lang_name 
  

local_text_table具有系统上使用的所有可能文本的列表电子邮件，页面内容，菜单标签，页脚文本等），以及系统将支持的每种语言的1个colunm - FK到language_lookup_table。

text_id

eng_text

spanish_text

arabic_text

...

这样，所有翻译都存储在整个系统的1个表中。我可以在1步中启用/禁用/更新/编辑/添加/删除翻译。在代码中，所有文本都存储为引用（text_id）的关键字。系统检测用户会话正在运行的语言，并相应地从该关键字的列中提取文本。

这是不是很好？

当然，这对于存储在数百个表中的查找值不起作用，因为我没有计划，除了给每个表自己的每个语言的colunms。然后我有用户内容，以允许用户翻译他们的用户发布，如博客，评论等，我没有一个计划。但是我想要专注于系统文本并最终确定。

解决方案

您的设计有缺陷，

这个表的更好的设计是：

< blockquote>

  text_id 
 lang_id（language_lookup_table的外键）
 translated_text 
  

现在，您可以向language_lookup_table添加语言，然后开始向local_text_table添加翻译，而不对关系模型进行任何更改。如果您有通过UI（或甚至直接在数据库中）输入此数据的方法，您应该能够直接在生产中添加新语言。

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.

解决方案

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.

这篇关于这是很好的设计为多语言的系统文本支持x数量的langs？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！