The Best Way to Localize Data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

The Best Way to Localize Data

Author

Topic

brent
Starting Member

1 Post

Posted - 2005-05-19 : 18:50:20

I've been trying to come up with the "ultimate" way to handle localization of content stored in a database. I've come up with something that works, but it isn't terrible efficient. I'm hoping someone out there can share a better way to do this.

As an example, assume you are storing a list of users. You want to store these fields: UserID, DateCreated, Username, and Password. The UserID, DateCreated, and Password are always the same, but you want to be able to localize the name. What I do is store the data across two tables - one that is language independent and one that can be localized. So I have two tables User and UserLocalization:

User:
UserID (PK)
DateCreated
Password

UserLocalization:
UserID (PK)
LocaleID (PK)
IsDefaultLocale
Name

LocaleID corresponds to a langauge like en-US or en-GB. I also store a table that contains each user's preferred langauges (in order):

UserLocalePreference:
UserID
LocaleID
Order

So for me it might contain two records:
LocaleID = 1 (en-US) and Order = 1 (preferred language)
LocaleID = 2 (ja-JP) and Order = 2 (my second choice)

I assume I'll pass the UserID into any query (so we can get the preferred languages). The goals are as follows:

1. When retrieving a list of all users, you should see names in the language that best matches your preferred list of languages.
2. If there is no record in the database that matches your preferred list of languages, or no preferred languages exist, you should see the "default" language for that record.
3. Ideally it shouldn't be much slower than querying a single table

I have code that does this, but it is slow and ugly and I don't want to send anyone down the wrong path. If it would help, I can post certainly post it.

Thank you very much for any help that can be offered.

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-05-21 : 15:18:03

I dont like that we would have to create a ___Localization table for every table that we want to localize.

Maybe look into using a name / value pair type table. Im away from the database right now, so beware of typos below :)

Something like this:

declare @user table (user_id int, first_name varchar(100) )

declare @locale table (locale_id int, locale varchar(100) )

declare @user_attribute_type table (attribute_type_id int, attribute_name varchar(25) )

declare @user_attribute table (user_id int, locale_id int, attribute_type_id int, attribute_value varchar(100) ) 

insert into @user
select 1, 'nathan'

insert into @locale
select 1, 'en-US' union
select 2, 'sp-SP'

insert into @user_attribute_type
select 1, 'first_name' union
select 2, 'last_name', union
select 3, 'fav_color'

insert into @user_attribute
select 1, 1, 3, 'Red' union
select 1, 2, 3, 'Rojo'

You could even move locale_id out into another table to define default_locale_id... that way when you alter the default you alter it for all localized attributes.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources