What's the best way to handle I18N in lookup tables?

Question

In a multilanguage application with lookup tables, what's the best way to handle translations?

For example for a country lookup table, a US user should see English country names, as a German should see German names. But still their IDs should be the same.

I can think of the following:

• Add a different lookup table for every language
• Use a single lookup table with multiple entries for the same country flagged according to its language.
• Get rid of all lookup tables and do all lookups by program code
• [Any other idea I didn't think of?]

Answer 1

The big question here is - can translations can be altered by end users?

If the answer is "NO", resource bundles are easier and faster to use than tables. Instead of actual text your tables will contain resource key (or you could use primary key for that purpose if it's textual rather than numeric) and appropriate resource bundle will contain the translation.

If the answer is "YES", you have to store translations in the database. I have found, however, that the easiest approach in this scenario is to mimic the above resource bundle functionality in the database - e.g. have a single table with "locale", "resource key", "resource value" columns that all other tables will use to look up actual localized text.