Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Posts
    5

    I18n database schema

    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: I18n database schema

    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) );

  3. #3
    Join Date
    Feb 2004
    Posts
    5

    Re: I18n database schema

    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?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: I18n database schema

    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 );

  5. #5
    Join Date
    Feb 2004
    Posts
    5

    Re: I18n database schema

    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.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: I18n database schema

    Originally posted by orlat84
    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".
    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)

  7. #7
    Join Date
    Feb 2004
    Posts
    5

    Re: I18n database schema

    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

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: I18n database schema

    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?

  9. #9
    Join Date
    Feb 2004
    Posts
    5

    Re: I18n database schema

    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

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: I18n database schema

    Originally posted by orlat84
    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 ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •