Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Modelling translatable entities in a database

 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is just a fairly general question to canvas people's experience or opinions. We are weighing up the pros and cons of how to model multi-lingual data in a relational database. The "clean" design we reckon is to have a translation table for every entity (so "person" would also have "person_tr"), each translation table would mirror the translatable attributes (i.e. just character data) and be differentiated by locale. So for the entry in "person":

we would have the following in "person_tr":

for example.

This makes the data easy to maintain, however we are concerned that we have tables that might have quite a high number of translatable entities associated with them, so searches by a translated value become significantly more complex. We've got a variety of exotic ways of mitigating this, but I'm curious if anyone has already dealt with this problem or can suggest a better design?

Any input is most welcome.
 
Jan Cumps
Bartender
Posts: 2588
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oracle Apps (Oraccle eCommerce Suite) uses a similar design as the one you are suggesting.
To avoid that each report or form has to recreate the proper links between the core and translation tables, they supply a view that does that work for you.
The view uses a stored function in it's joins to look up the current logged on user's language, and will return the data in that language.
 
Scott Selikoff
author
Saloon Keeper
Posts: 4015
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why multi-language in the database at all? Technically, its a facet of the UI. The person is a "secretary", regardless of the language or how its spelled. In other words, it mixes data and presentation layers, to have the database concerned about supporting multiple languages. If possible, I would leave the multi-language aspect in the control of whatever is outputting the data and use common i18n properties files as needed.

Is there an advantage to having the database know about different languages?
 
Roel De Nijs
Sheriff
Posts: 10228
129
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Paul,

The application of the client I work for has to support 2 languages: Dutch and French (because we live in Belgium ) So we have a similar issue like you, although in our case it's very limited. When we had to decide about the approach we would take we discussed 2 alternatives:
a) the one you mentioned
b) for each translatable attribute create 2 columns: one for dutch and one for french

We opted for the 1st alternative, because it's a lot easier to maintain (certainly if you have to add a 3rd language). So we have a language table with the possible languages and our person_tr table has columns person_id, language_id and a column for each translatable attribute.
Regarding searches it's not that complex, maybe also because we have built a framework ourselves which takes care of these things. Of course you have to join to the appropriate tables, but I would not consider joining as something complex.

Hope it helps!
Kind regards,
Roel
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yeah, I would rather have resource files for each language and add words to it and let the UI and UI framework use the correct resource file.

For the database, for me, the main point would be what is the easiest to add new words, new tables and/or new languages.

Mark
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks folks!

Jan - views are a distinct possibility, unfortunately we have quite a significant support headache for that. We support multiple versions of multiple databases, so pushing the logic into the database is not a popular choice.

Scott/Mark - the reasons is we need end users to be able to set this stuff up on the fly. Though I'd agree with your logic (and its how we deal with all static text data), we can't really push this maintenance on to our customers. My secretary is maybe a bad example, a more common example of what we need is a business description associated with an entity - so several hundred lines of changing text explaining a thing. We need to support customers adding these as new languages are supported by them, and updating their content frequently (in some cases many times in one day).

Roel - its good to hear your experience. Our primary diver was Canadian customers, so a very similar requirement as you probably have in Belgium. Our concern with the complexity of join is that we have some big entities that join to a lot of other entities (we have an awful legacy data model). We already mitigate performance issues by denormalizing some of these; we'd loose that if we used translation tables. So a query currently performing five joins might jump to being a query performing twenty five joins, which we are worried will be a significant determiner of the performance of the application. Needless to say these big entities are by far the most frequently queried things in the database.

Thanks again for you input. Its good to hear we are having the same sort of thoughts as you. Now we just need to make a decision...
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic