views:

78

answers:

2

I need to localize a SQL Server 2008 database. After investigating recommendations, I have found that it is best to have separate tables or each of the languages for the strings. That way different sorting settings can be set for each table. For example, a typical Product table has ProdID, Product Description, and Price fields. The recommended solution is to set the table structures to have the Product table be ProdID and Price. Then a specific table for each language would have the following structure: ProdID and Description.

My question is how do I create a store procedure that has a parameter which passes in the culture to use for the sub-table and then use that to join the tables? The sub-table needs to change based on the parameter. How can that be done? I am using SQL Server 2008.

+2  A: 

First off, are you sure you really want to implement different tables for each culture? It would make more sense to modify your Product table to remove the description, and then add a ProductDescription table with a ProdID, culture, and description field. This way you don't have to toy around with dynamic SQL (which is what you'll have to use) to select the correct table based on the culture parameter.

Adam Maras
+1  A: 

...specific table for each language would have the following structure: ProdID and Description.

...which is why you're having to look at a really involved setup to get your information out of the database.

A better approach would be to use a single table, and use a code for the language. You don't want to be defining a column per attribute you want translated either, so you'd be looking at implementing something like:

LANGUAGES table

  • LANGUAGE_ID, pk
  • LANGUAGE_DESCRIPTION

Example data:

LANGUAGE_ID  |  LANGUAGE_DESCRIPTION
------------------------------------
1            |  ENGLISH
2            |  FRENCH

TRANSLATED_ATTRIBUTES table

  • TRANSLATED_ATTRIBUTE_ID, pk
  • TRANSLATED_ATTRIBUTE_DESC

Example data:

TRANSLATED_ATTRIBUTE_ID  |  TRANSLATED_ATTRIBUTE_DESC
------------------------------------
1                        |  PROD_ID
2                        |  PROD_DESC

LOCALIZATIONS table

  • LANGUAGE_ID, pk
  • TRANSLATED_ATTRIBUTE_ID, pk
  • TRANSLATED_VALUE

Example data:

LANGUAGE_ID | TRANSLATED_ATTRIBUTE_ID | TRANSLATED_VALUE
----------------------------------------------------------
1           |  1                      | Product ID
2           |  1                      | Produit ID

You'll want a table associating the TRANSLATED_ATTRIBUTE_ID with a given item - Product is the example you've given so:

ATTRIBUTES table

  • ATTRIBUTE_ID, pk
  • ATTRIBUTE_TYPE_CODE, fk
  • TRANSLATED_ATTRIBUTE_ID, fk

Example data:

ATTRIBUTE_ID  |  ATTRIBUTE_TYPE_CODE  |  TRANSLATED_ATTRIBUTE_ID
----------------------------------------------------------------
1             |  PRODUCT              | 1

If you want to relate on a per product basis:

ATTRIBUTES table

  • ATTRIBUTE_ID, pk
  • PRODUCT_ID, fk
  • TRANSLATED_ATTRIBUTE_ID, fk

Now can you use two parameters - the language (English) & what the item is (Product):

SELECT t.translated_attribute_desc, 
       t.translated_value
  FROM LOCALIZATIONS t
  JOIN TRANSLATED_ATTRIBUTES ta ON ta.translated_attribute_id = t.translated_attribute_id
  JOIN ATTRIBUTES a ON a.translated_attribute_id = ta.translated_attribute_id
  JOIN ATTRIBUTE_TYPE_CODES atc ON atc.attribute_type_code = a.attribute_type_code
  JOIN LANGUAGES lang ON lang.language_id = t.language_id
 WHERE lang.language_description = 'ENGLISH' --alternate: lang.language_id = 1
   AND atc.attribute_type_code = 'PRODUCT'

You can pivot the data as necessary.

OMG Ponies
I have been trying to figure out how each table works. Can you provide an example of records in the Attributes table and the file structure and example of records for the Attribute_Types_Code table? How do all these tables relate back to the actual Product table? Doesn't the select statement need to join to it?