0

My web application is implemented in Grails.

My situation is that I have a domain class (hibernate class) with more columns and some of them are large Strings, up to 4000 characters.

For example :

String description // max 4000
// column2, column3, etc

Now I have to implement a multiple language database for this elements and my strategy is to have one field for every language.

String description_en_US
String description_de_DE

My question is if it is more efficient if I have a separate table for every language, or to save it in one large table. The question has two parts. First, which is more efficient for hibernate if it uses a large memory when loading the data and second, which is more efficient for the database?

It can happen that later I'll have more than 10 languages.

sgleser87
  • 225
  • 1
  • 3
  • 11
  • I always need translated data, which always depends on the locale. Then I had an idea, that in grails there is an option to create transient objects which are not saved in the database but they have their 'getter' and in this getter I read the appropriate translation. – sgleser87 Oct 26 '12 at 12:02

2 Answers2

1

I have dealt with similar situation earlier where we had one table with some values & needed to store their translated values in several language. After several discussions & brainstorming, we finalized on below approach.

Table1: MainTable 

     id, Description, Column1, column2 ......


Tables2: MainTransTable

     id, MainTable_id (FK), Language, description_trans

So it will be MainTable entity & it will have collection of MainTransTable entities (One to Many relationship). Each MainTransTable will represent translated version of MainTable in given language as per Language column

Advantages:
- In future if you want to add value for another language, then you just need to add another row in MainTransTable
- Currently you are only translating only one column. So in future if you decide to translate any other columns,  you can use same table structure with new trans column added in trans table
Ravi K
  • 916
  • 6
  • 9
  • Ok, but did you come to this idea because it is more efficient for the memory and database? That actually my question. – sgleser87 Oct 26 '12 at 11:05
  • 1
    This approach is efficient as well as proper design. Think this way, if you put columns in same table then they will be fetched every time even if you don't need. But when you go for one to many relationship, then it will be lazy loaded. SO when you fetch MainTable, translation data won't come everytime. So MainTable queries will be faster. Whenever you need translated data you can lazily load it. – Ravi K Oct 26 '12 at 11:43
  • Ok, I agree. I always need translated data, which always depends on the locale. Then I had an idea, that in grails there is an option to create transient objects which are not saved in the database but they have their 'getter' and in this getter I read the appropriate translation. – sgleser87 Oct 26 '12 at 12:01
1

I cannot answer the question but I would strongly recommend not using a field for a language but another table with languages and references to this table. This is because if you ever have to add another language you don't have to change the structure of the existing tables but only add another row to the languages table and can use the key as foreign key in the description table. Long strings aren't the problem I think. How many entries to the description table do you expect to maintain?

Campfire
  • 774
  • 1
  • 5
  • 12
  • I don't know yet, but I agree with you that it is better not to change the structure of the database always when I need a new language. – sgleser87 Oct 26 '12 at 11:33