Localized Data In SQL Database

Hi,

is there any best practice to store localized data in a (SQL) database? I couldn't find any helpful resources, so I've made my own concept. Any suggestions to improve the following code?




Code Snippet
USE tempdb
GO

--
-- create a table for localized data
--
CREATE TABLE [LocalizedData]
(
[Identifier] varchar(50) NOT NULL,
[Language] varchar(5) NOT NULL,
[Title] nvarchar(50),
[Description] nvarchar(200),
PRIMARY KEY ([Identifier], [Language])
)
GO

--
-- insert some sample data
--
INSERT INTO [LocalizedData] ([Identifier], [Language], [Title], [Description])
SELECT 'String1', 'en', 'first entry', 'This is my first entry'
UNION SELECT 'String1', 'de', 'erster Eintrag', 'Das ist mein erster Eintrag'
UNION SELECT 'String2', 'de', 'Führerschein', 'Kein Kommentar'
UNION SELECT 'String2', 'en', 'driving licence', 'no comment'
UNION SELECT 'String2', 'en-us', 'driver''s license', NULL
GO

--
-- create procedure to retrieve some localized strings
--
CREATE PROCEDURE GetLocalizedData
(
@identifier varchar(50),
@fallbackLanguage char(2) = 'en',
@lang varchar(5)
)
AS
DECLARE @generalLanguage char(2)
, @specializedLanguage char(5)

IF LEN(@lang) = 5
BEGIN
SET @specializedLanguage = @lang
SET @generalLanguage = LEFT(@lang, 2)
END
ELSE
BEGIN
SET @specializedLanguage = @lang + '-' + @lang
SET @generalLanguage = @lang
END

SELECT COALESCE([S].[Title], [G].[Title], [F].[Title])
, COALESCE([S].[Description], [G].[Description], [F].[Description])
FROM [LocalizedData] [F]
LEFT JOIN [LocalizedData] [G]
ON [G].[Identifier] = @identifier
AND [G].[Language] = @generalLanguage
LEFT JOIN [LocalizedData] [S]
ON [S].[Identifier] = @identifier
AND [S].[Language] = @specializedLanguage
WHERE [F].[Identifier] = @identifier
AND [F].[Language] = @fallbackLanguage
GO

--
-- retrieve some data
--
EXEC GetLocalizedData @identifier = 'String1', @lang = 'de'
EXEC GetLocalizedData @identifier = 'String1', @lang = 'fr'
EXEC GetLocalizedData @identifier = 'String1', @lang = 'en-us'
EXEC GetLocalizedData @identifier = 'String2', @lang = 'en-us'
EXEC GetLocalizedData @identifier = 'String2', @lang = 'en'
EXEC GetLocalizedData @identifier = 'String2', @lang = 'de'





Output:
------------------ -----------------------------
erster Eintrag Das ist mein erster Eintrag
------------------ -----------------------------
first entry This is my first entry
------------------ -----------------------------
first entry This is my first entry
------------------ -----------------------------
driver's license no comment
------------------ -----------------------------
driving licence no comment
------------------ -----------------------------
Führerschein Kein Kommentar

It works like I would expect it; a nice fallback like the .NET FX localization, but... I wonder if this approach will still be fast enough with 100.000 entries and more complex queries.

--
Regards,
Daniel Kuppitz