Hi,

First of all please excuse me if this is not the right place for posting this question. But i am sure that the Hibernate community has loads of developers with experience in DB and OOP issues.

So here is my story:
we have to develop a framework for storing and gathering i18n data in a database. The requirement is to support translations in any number of languages. The sistem will have some hundreds of tables, part of them will be i18n able others not.

So my idea is to create a table with all the columns which are i18n able(COLUMN_NAMES), having FK to a table which holds the tables with i18n columns(TABLE_NAMES).

The main data in the tables will be in English. The translated data will be stored into another table that has foreign keys
1. to the column that is internationalized
2. to the id(PK) of the row with that data
3. to the languages table.

I would prefer to give you an example:
There are 2 tables COLORS and ITEMS that have i18n columns

COLORS
----------


ITEMS
-------


LANGUAGES
--------------


TABLE_NAMES(this table will hold the tables that have i18n fields)
-----------------


COLUMN_NAMES(holds the columns that support i18n)
-------------------


I18NDATA
------------


So in my opinion this schema allows me to store and gather data according to a specific language. The problem I think resides in implementing the preprocessing of all the SQL(HQL) queries.


Anyone any better ideas regarding this issue? Is there any open source framework for such an i18n issue? How this issue would be handled by an Hibernate guru?


Thanks in advance,

Florin

I think it is better to store translated data in "properties" file than in database for performance and simplicity reasons.
It never changes after transalation, doe's it ?

sometimes it may change.
the idea is the following: an user uses the English version of the application then all the products details will be in English. These product details are not stored in properties files but in the database. When an user uses the application in German then the product details should be displayed in German and these details should be retrieved form the database.

I cannot see how can I use properties files, for all the tables that have internationalizable columns should I generate properties files? The content of the database is changing often enough should I trigger a regeneration of the properties files everytime a new internationalized record is inserted?

Store it in DB, if data changes. Queries are faster without joins, but it will be more pain to update files. I think it is possible to model it this way:

ALL_STRINGS_TABLE --single table for all localized text
key
lang
text

SOME_TABLE_1
all_strings_key
.....

SOME_TABLE_2
all_strings_key
.....

It is possible to cache all strings in hash table "( key + lang ) => text" on client side and use queries without joins too (if it is pseudo readonly data and is not too large for memory ).

This is a good subject and any experience is welcome.

Generate .properties file may help but I think using read-only cached map with ehcache or something similar is better. Usually, those data are metadata for the application. So managing them using a read at start and refresh them on explicit demand is the easiest solution, I think.

I am not sure if my problem was right understood or maybe I cannot understand your hints. Let me explain you once again what I want to obtain. I will start with an example:

I have a table ITEMS (id, name, description, VAT_rate). The fields name and description are i18n able; id and VAT_rate are not. For storing the translations of the columns as I have told you I have imagined a tables structure like this:
LANGUAGES (id, name, code)
TABLES(id, table_name)
COLUMNS(id, column_name, id_table)

I hope the fields of these tables are clear. The translations will be kept in a table like this:
TRANSLATIONS(id, id_column, id_language, id_row, translated_text)

So for the record (23, 'Eggs', 'Eggs description', 9) in the ITEMS table I will have the following records in the TRANSLATIONS table:

(121, 45, 2, 23, 'Eggs translated in German') and
(122, 46, 2, 23, 'Eggs description translated in German') where:

- 121 and 122 are the ids of the translations;
- 45 is the id in the COLUMNS table for the record identifying the column 'name' from table ITEMS
- 46 is the id in the COLUMNS table for the record identifying the column 'description' from table ITEMS
- 2 is the id of the German language in the table LANGUAGES
- 23 is the id of the record in the ITEMS table for which the translation is made.

I will have an Item class which will be mapped to the ITEMS table. This object will not contain any internationalized data. I am thinking to have an ItemI18N class which will extend(encapsulate) Item and such an object will contain also the translations for the fields which have translations. Then using the ItemI18N object I will have access to all the translations. Let's say I want to use German then I will have a field in the ItemI18N object saying that I use German and when I call getName() I will obtain the translation if this is available otherwise I will obtain the original name. Another possibility I can see is:
the ItemI18N class will have a constructor with Item and id_language as parameters and here in this constructor will be implemented the logic for retrieving the translations. In this case I do not know how can I save such an object into the database.

Unfortunatelly I do not know how I can implement this kind of mapping. Is it possible to implement this mapping with Hibernate? Has anyone other experiences with such a requirement?

Thanks,

Florin