B/s system multi-language support implementation solution

The interface layer multi-language implementation solution is no longer fresh, and asp.net is now well supported. It can be easily done with only the. resx resource file.

The problem now is that the content also needs multi-language support. How should we design the database?

This document uses a simple content management system as an example to describe how to store articles (with titles, content, and other fields) in multiple languages.

Solution 1:Each field that may be multilingual is designed into two fields.

If both Chinese and English versions are supported at the same time, the design of the article table may be:

ArticleID, Title, TitleEn, Content, ContentEn

The disadvantages of this design are obvious, and the scalability is too poor. If you need to support more languages such as traditional Chinese, French, and Russian, you need to add extension fields accordingly.

Solution 2:Add a language Id field to the document table.

For example, the design of the article table is as follows:

ID ArticleID, LanguageID (or LanguageName), Title, Content

1 1 1 faint Chinese

2 1 2 English Content

......

This design makes it possible to achieve better scalability.

Recommended.

 

 

========================================================== ======================================

Post an article on previous summary and analysis

 

This article outlines how to deal with the problem of multi-language in database design. The multi-language here refers to the business such as: in ERP software, when we fill in the customer name, in addition to the customer's Chinese name, you also need to enter his/her English name. In general, if it is a common project software, it is relatively simple. You only need to design a fixed ChineseName and EnglishName fields. This article does not discuss this form, but discusses how to implement universal multilingual storage and reading in large-scale platform-Based ERP software.

　　Sub-Table Method

The first method is to create a sub-Table. U9 is like this. You need to note that if every entity contains a multi-language field, a table with the suffix _ Trl will appear. You may be in trouble. Otherwise, the platform automatically handles these operations in the background. You only need to mark this field as a multi-language field.

Theoretically, his storage is the most consistent with the database design principles. No matter how many languages your system uses, the database structure remains unchanged. However, I always think that the SQL query will be complicated, although the platform will also help you complete this process. I was wondering how to implement a multilingual strategy? Multi-Language Policy Example: If this field does not have the corresponding traditional Chinese, take the Simplified Chinese, if not, take the default language content. So how to implement it in an SQL statement?

Either way, at least I don't like this.

Multi-field mode

Well? Which design is this? Isn't it the same as the project design I mentioned above?

The data structure is the same. The only difference is that the structure of the database is shielded by the ORM. When designing an object, you only design the Name field, whose type is "multilingual ", then, when initializing at the client, the customer can decide the language to use, and then the ORM automatically adds these columns in the background.

This is the design I want, because it is concise enough that anyone can easily write SQL languages. And it must be the most efficient. It is also easy to implement the value policy mentioned above. You only need to orchestrate multiple nested IIF functions.

What are the disadvantages? Of course, there is a lot of redundancy first. Even if you do not enter the corresponding English, it will take up a space. Secondly, if the customer is nervous, he suddenly chooses more than a dozen languages and finds that he does not need them and wants to delete them? Then, I need to check whether all the related fields in the database have no data before deciding to delete the language and all related fields. This is a problem.

XML Field

In this way, I will not draw a picture. It is very simple. There is only one field Name. However, the data type is not nvarchar, but is defined as XML. This is the new type of SQLServer2005, we can store data such as the following in this field:

<Items>
<Item lng = "" value = "default"/>
<Item lng = "CHS" value = "Chinese"/>
<Item lng = "EN" value = "English"/>
</Items>

How can I output the corresponding Chinese content in SQL? Simple:

Select EmployeeId, Name. value ('(/items/item [@ lng = "CHS"]/@ value) [1]', 'nvarchar (max) ') FROM Employees

Very simple, I like it.

However, some people may say that before the xml type exists, I have used nvarchar to implement it. Using a custom function can solve the problem (such: /en/english/chs/Chinese ). However, I think the string processing method is not perfect, mainly because you must handle special characters carefully, otherwise it is easy to confuse. If the XML type is used, the database will process this. In addition, SQL Server optimizes XML-type queries, which is much faster than SQL user-defined functions. Moreover, I believe that SQL Server will support XML better in the future.