Database Localization - Lookup lists - smarter way

Question

I'm looking to add some lookup lists in the database, but I want them to be easy localizable (SQL 2005, ADO.NET)

This would include:

• Easy Management of multiple languages at the same time
• Easy Retrieval of values from the database
• Fallback language (in case the selected language is missing)

I was thinking about having a table that would store the multi-language lookup-list (using for different languages the same id) and use a function that would return the value of the look-up list - by receiving the ID and the Language.

One of the pitfalls would be that i have to manually add a language parameter to every query that uses the lookup list.

I'm looking into a solution that would let me send the parameter as a "session/global variable", or that would send the parameter automatically with the sql query, and the function to retrieve it by itself (either to attach the parameter automatically, either to be able to read the parameter).

The solution can look something like this, but I don't mind if it is different, as long as it doesn't give the parameter explicitly to the Query (pseudocode):

1. Send the language using "the method"
2. Execute Query
3. Get the localized results

Clarification:

1. Normally the query would look like this (remember using the lookup function):

   SELECT .., GetLookupList1(lookup_ID, language), .. FROM TABLE

The GetLookupList1 is a user defined function that retrieves the lookup value for a lookup table. By using this function, the SQL Code is easier to read and maintain.

The body of the function would be something like:

SELECT @result = LookupValue FROM LookupTable1 WHERE ID=@Lookup_ID and Language=@lang
RETURN @result

1. What I want is to be able to remove the language parameter from the function to some kind of a static variable, available only for the current connection/statement/command, so the query would look like

   SELECT .., GetLookupList1(lookup_ID), .. FROM TABLE

Answer 1

If you structure your data like this:

MessageToken    DisplayText       LangCode
firewood        Fire wood         en
firewood        Bois de chauffage fr

When you make your query, just supply the default languageId (if blank) or the supplied languageId. Use a standard list of tokens for the messages.

Select DisplayText from (some table) where MessageToken = 'firewood' and LangId = 'en'

Answer 2

Since there are no user-defined global variables in SQL Server, you'll have to use one of two approaches:

1. Tables - temporary or permanent. Example with permanent tables: http://weblogs.sqlteam.com/mladenp/archive/2007/04/23/60185.aspx.
2. SET CONTEXT_INFO: http://msdn.microsoft.com/en-us/library/ms187768.aspx. Context_info lets you associate 128 binary bytes to a session/connection. It works but be careful. If you get in the habit of using it, you run the risk of accidentally overwriting it in another context. There's only one per session/connection.

Example context_info t-sql:

declare @languagein varchar(30), @contextin varbinary(128),
    @languageout varchar(30), @contextout varbinary(128)

select @languagein = 'ro-RO'
select @contextin = cast(@languagein as varbinary(128))
set context_info @contextin

--do whatever you like here: queries, stored procs. 
--context_info stays 'ro-RO' for the duration of the session/connection

select @contextout = context_info()
set @languageout = replace(cast(@contextout as varchar(30)),0x00, '')
print @languageout

Another technique I've used in localization is a three part coalesce to insure a result. Check for language-region first, then language, then a default. Based on your query:

SELECT COALESCE(langregion.LookupValue, lang.LookupValue, fallback.LookupValue) LookupVal
FROM LookupTable1 fallback
LEFT OUTER JOIN LookupTable1 lang 
    ON lang.ID = fallback.ID AND lang.Lang = @language
LEFT OUTER JOIN LookupTable1 langregion 
    ON langregion.ID = fallback.ID AND langregion.Lang = @languagewithregion
WHERE fallback.ID = @Lookup_ID
AND fallback.Lang = @defaultlanguage

Answer 3

After studying the problem in detail I have found the following:

1. I could use the SET CONTEXT_INFO, but I would have to inject some SQL to solve the problem.

2. The best option would be not to store localized data in the look-up tables. Instead, store some identification strings, and use custom localization logic in the application to match the strings to localized data. For the .NET framework it would be implemented by using resources, with a custom resource provider if I want to retreive localized information from a databse.

Thank you for your answers.