Hi there,

we have to design a database schema which will allow us to store data in more than one language. So the main data will be in English but we want to imagine a structure so that we can store also translations for some of the English columns. For example if I have a table COLORS with records (red, green, blue) we should store somewhere that these fields are in German (rott, grun, blau) and in French (rouge, vert, bleu).
A solution should be to have columns EN_name, FR_name, DE_name.
But when we will want to add another language we will have to modify the whole database structure, adding columns for the new language.

Has anyone any idea how can we implement this?

Thanks,

Florin

A flexible structure would be:

create table languages( language_id ... primary key, ... );
create table colors( color_id ... primary key, ... );
create table color_names( color_id references colors, language_id references languages, color_name ..., primary key(color_id, language_id) );

Yes your solution works in the case of the COLORS table. But we have more tables in the system that require i18n. Some of them have both columns which require i18n and which don't. What should I do? For each column in a table which requires i18n should I define an aditional table for storing the translations, with FK to the translation language? Where should I keep all the logic that tells me that a field has translations and in which table I can find those translations?

What is this "i18n" of which you speak? Never heard of that.

Yes, it's true that if you followed my suggestion above you would be adding an additional translation table for each table that requires it. If you did that, writers of queries would need to know to use the tables, nothing would tell them that.

Sounds like you are looking for a more generic approach. Well I guess you could have a table of English text:

create table text ( text_id ... primary key, english_text ... );

And a table of translations:

create table translations ( text_id, language_id, translated_text );

Then in all tables that needed multilingual text you could store a link to the text table instead:

create table colors( color_id, color_name_text_id references text );

i18n means internationalization ... there are 18 characters between the first letter of the word and the last one ... so there is an "i" followed by 18 characters and a "n".

the idea with setting new tables for the columns that require translations I think is better that the second one. In the case of the second aproach, with the text and translations table, the problem that I see is that sometimes one word has a meaning in a table and maybe in other place another meaning.

The only thing that must be done with the first approach is to store in some tables the info that specific columns in specific tables require translations.

Thanks for the help.

Did you make that up? Since your question is all about making information understandable internationally, you should note that replacing meaningful words like "internationalisation" with meaningless codes like "i18n" isn't helpful! ;o)

the tutorial from Sun for internationalization starts with this issue

http://java.sun.com/docs/books/tutorial/i18n/intro/

so I assumed it is an well known acronim at least in the java community it is

Maybe it is familiar jargon in the "i18n" community, but I can honestly say I have never seen that abbreviation before today. Shall we conduct a poll of other forum users?

so if you say u haven't heard of this term before, it means that you have learnt something new

as long as we live, we learn new things. nobody is born and knows everything

That's true. But having to learn a new symbol "i18n" for a concept that I understand perfectly well as "internationalisation" seems like a poor use of brain cells to me ...