本地化SQL Server（2005/2008）数据库的最佳做法 [英] Best-practices for localizing a SQL Server (2005/2008) database

问题描述

问题

我确信你们中有许多人面临着将数据库后端本地化到应用程序的挑战。如果你没有，那么我会很自信地说，你今后必须这样做的可能性是相当大的。我正在和您的数据库实体一起存储多种文本翻译（同样适用于货币等）。

例如，经典的类别表可能有一个名称和一个描述列应该被全球化。一种方法是为每个实体确定一个文本表，然后根据提供的语言进行加入以检索值。

这将使您有很多文本表，一个用于您要本地化的每个实体，另外添加一个TextType以区分可能存储的各种文本。

如果在SQL Server 2005/2008 datebase中实现这种支持有任何，记录的，最佳实践/设计模式，我很好奇（我正在具体说明RDBMS，因为它可能包含支持的关键字，帮助实现）？

对XML方法的想法

一个想法我一直在玩（尽管只有到目前为止，在我的头脑中）是利用SQL Server 2005中引入的XML数据类型。这个想法是使得应该支持本地化的列，XML数据类型（并将模式绑定到它）。 XML将包含本地化的字符串以及与之相关的语言代码/文化。

沿着

$ b $
名称（XML ...）
描述（XML ...）
（int，identity）

然后你会有这样的东西作为XML

 <本地化> 
< text culture =sv-SE> Dettaärett namn< / text> 
< text culture =en-EN>这是一个名称< / text> 
< / local> 
  

然后你可以做（​​这不是生产代码，所以我将使用*）

  SELECT * 
从产品
其中Product.ID = 10 
  

您将收到所有本地化文本的产品，这意味着您必须在客户端进行提取。这里最大的问题显然是您在每个查询上返回的额外数据量，其优点将是一个更清洁的设计，无需查找表，连接等。

Btw，我在设计中最终使用了什么方法我仍然会使用Linq To SQL（.NET Platform）查询数据库（XML方法应该是一个问题，因为它会返回一个可以解释为客户端的XElement）

所以关于数据库本地化设计模式的建议，以及可能对XML思想的评论将是非常感谢。

解决方案

我认为你可以坚持XML，允许更清洁的设计。我会进一步利用 xml：lang 属性，其中是为此使用设计的：

 < lnn> 
< text xml：lang =sv-SE> Dettaärett namn< / text> 
< text xml：lang =en-EN>这是一个名称< / text> 
< / l10n> 
  

您还可以通过 XPath查询（如意见中的建议），以避免任何客户端的处理。这将给出这样的东西（未经测试）：

  SELECT Name.value（'（l10n / text [lang（）= en]）[1]'，'NVARCHAR（MAX）'）
 FROM Product 
 WHERE Product.ID = 10; 
  

请注意，此解决方案将是一个优于但不太有效的解决方案，而不是单独的表。某些应用程序可能是可以的。

Question

I'm sure many of you have been faced by the challenge of localizing a database backend to an application. If you've not then I'd be pretty confident in saying that the odds of you having to do so in the future is quite large. I'm talking anout storing multiple translations of texts (and the same can be said for currency etc.) for your database entities.

For example the classic "Category" table might have a Name and a Description column which should be globalized. One way would be to do have a "Text" table for each of your entities and then do a join to retreive the values based on the provided language.

This leaves you with a lot of "Text" tables, one for each entity which you want to localize, with the addition of a TextType to distinguish between the various texts that it may store.

I'm curious if there are any, documented, best-practises / design patterns on implementing this kind of support into a SQL Server 2005/2008 datebase (I'm being specific about the RDBMS since it might contain supported keywords and such which helps with the implementation)?

Thoughts on XML approach

One idea I have been toying with (albeit only in my head so far) was to leverage the XML datatype introduced in SQL Server 2005. The idea was to make columns which should support localization, of the XML datatype (and bind a schema to it). The XML would contain the localized strings along with the language code / culture it was tied to.

Something along the lines of

Product
ID (int, identity)
Name (XML ...)
Description (XML ...)

Then you would have something like this as the XML

<localization>
  <text culture="sv-SE">Detta är ett namn</text>
  <text culture="en-EN">This is a name</text>
</localization>

You could then do (This isn't production code so I'll use the *)

SELECT *
From Product
Where Product.ID = 10

And you would get back the product with all localized texts which would mean you would have to do the extraction on the client-side. The biggest problem here is obviously the amount of extra data that you would have to return on each query, The benefits would be a cleaner design with no look-up tables, joins and so on.

Btw, what ever method I do end up using in my design I will still be using Linq To SQL (.NET Platform) to query the database (the XML approach should be a problem since it would return an XElement which could be interpreted client-side)

So suggestion on database localization design patterns, and possibly comments on the XML thought, would be very apprechiated.

解决方案

I think you can stick with XML which allows for a cleaner design. I would go further and take advantage of the xml:lang attribute which is designed for this usage :

<l10n>
  <text xml:lang="sv-SE">Detta är ett namn</text>
  <text xml:lang="en-EN">This is a name</text>
</l10n>

One step further, you could select the localized resource in your query via a XPath query (as suggested in the comments) to avoid any client side treatment. This would give something like this (untested) :

SELECT Name.value('(l10n/text[lang()="en"])[1]', 'NVARCHAR(MAX)')
  FROM Product
  WHERE Product.ID=10;

Note that this solution would be an elegant but less efficient solution than the separate table one. Which may be OK for some application.

这篇关于本地化SQL Server（2005/2008）数据库的最佳做法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！