What is the best way to work with multiple languages ​​in database applications?

Question

I have a series of property files that are used to display messages in different languages.
Therefore, depending on the current language settings, the message may be displayed in English or French or German, etc.
I have different records in my database, and instead of the actual value, I put the key of the property file so that when I retrieve the records using this database, I can display the message in the appropriate language.
For instance. There may be an entry in my database:

John| Smith| AQ| etc

Where the AQ in the corresponding properties file can appear as Doctor or Arzt , etc.
My problem is that I need to sort by screen these attributes whose values ​​in the database, although they are codes, so sorting in the database is not possible.
From here I had a suggestion to use temporary tables and sort, but for me it is suitable for 1 attribute.
I am looking for a more general solution for processing multiple property files and avoid, if possible, checking in the code:
e.g. if this query sorts on X create this temp table etc.
Is there a general solution to this?

Answer 1

Instead of storing language information in property files, store it in tables in the database. Then all this can be done easily.

 Records: first | last | messageid | John | Smith | 1 | Messages: messageid | language | message | 1 | English | Mr. | 1 | Spanish | Sr. | 2 | English | Doctor | etc... 

Then sorting the request by message in the local language will look like this:

 select first, last, message from records r inner join messages m on m.messageid = r.messageid where language = [your current language] order by message 

Answer 2

Great question!

IMHO, you want to continue to use property files to localize this kind of string - it allows you to use Java IL8N's built-in functions, and it saves you a lot of time.

In general, my recommendation is to store local strings for domain objects in a database — for example, if you have a product database and you need to store product names that are explicitly part of the domain; product managers also need to manage product names, and you want to enforce business logic and referential integrity.

You can argue that this applies to the example you cited - the names are part of the person domain and must be managed in a database.

For user interface elements - the text on the button, the name of the menu element - the properties files are absolutely correct.

If you think the “name” is part of the user interface, or you don’t want to move it because you have an established localization process, my recommendation is to sort in Java, not in SQL; Based on how you connect to the database, there are many ways to do this.

Answer 3

When you start the application, you can synchronize tables containing key, local, and actual messages. These are regular tables, so you can access them in regular SQL queries.

But I would recommend doing the sorting that you always describe in the application, because it looks like the application logic.