Sorting and i18n in Database

I have the following data in my db:

ID   Name    Region
100   Sam     North
101   Dam     South
102   Wesson  East
...
...
...

Now, the region will be different in different languages. I want the Sorting to happen right, based on the display value rather than the internal value.

Any Ideas? (And yeah, sorting in memory using Java is not an opt开发者_开发问答ion!)

Unfortunately, if you want to do that in the database, you will have to store the internationalized versions as well in the database (or at least their order). Otherwise, how could the database engine possibly know how to sort?

You will have to create a table with three columns: the English version, the language code and the translated version (with the English version and the language code together being the primary key). Then join to this table in in you query by using the English word and a language code and then sort on the internationalized version.

The best approach to use internationalization is to remove the internationalization from your application and keep it in separated i18n databases. In your application you keep a key that can be used to access those separated databases, normally xml or yml.

As rule of thumb i would suggest:

• keep all database in one format, one place
• extract internationalization strings from your application
• lets your application to pull i18n strings from your i18n from your internationalization database.

You can check the RAILS approach to i18n. It's simply, clean and easy to use.

What do you mean "Display Value"? I take it you are somehow converting that into a different language in java itself, and don't store that value (the localised one, I assume) in the db? If so, you're kind of screwed. You need to get that data into the DB to be able to sort with it there.