Several Methods for SQL Server to store multilingual data

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

Direct:

 

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

Or directly:

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 may say that before the XML type is available, 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.