Best-practices for localizing a SQL Server (2005/2008) database

Question

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.

Answer 1

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.