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

问题描述

我相信你们中的许多人都面临将数据库后端本地化到应用程序的挑战.如果你还没有，那么我非常有信心地说你将来不得不这样做的可能性非常大.我说的是为您的数据库实体存储多个文本翻译(货币等也可以这样说).

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.

这给您留下了许多文本"表，一个用于您要本地化的每个实体，并添加了一个 TextType 以区分它可能存储的各种文本.

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.

我很好奇是否有关于将这种支持实现到 SQL Server 2005/2008 数据库中的任何记录在案的最佳实践/设计模式(我特别关注 RDBMS，因为它可能包含受支持的关键字和这样有助于实施)?

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)?

我一直在玩弄的一个想法(尽管到目前为止只是在我的脑海中)是利用 SQL Server 2005 中引入的 XML 数据类型.这个想法是制作应该支持本地化的列，XML 数据类型(并绑定一个模式).XML 将包含本地化的字符串以及它所关联的语言代码/文化.

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.

类似的东西

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

然后你会有这样的东西作为 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.

顺便说一句，无论我最终在设计中使用什么方法，我仍然会使用 Linq To SQL(.NET 平台)来查询数据库(XML 方法应该是一个问题，因为它会返回一个 XElement，它可能是解释的客户端)

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)

因此，我们非常欢迎有关数据库本地化设计模式的建议，以及可能对 XML 思想的评论.

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

推荐答案

我认为您可以坚持使用 XML，它允许更简洁的设计.我会更进一步，利用 的 xml:lang 属性这种用法 :

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>

更进一步，您可以通过XPath 查询(如评论中所建议)以避免任何客户端处理.这会给出这样的东西(未经测试):

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屋！