Re: Multilanguage application & database

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 25 Oct 2004 10:16:16 -0500
Message-ID: <ulldux23f.fsf@standardandpoors.com>

On 22 Oct 2004, kristofvdw_at_matt.es wrote:

> POSSIBILITY 2: Putting a foreign key to a language table
> doesn't seem possible, since every language would need a row in
> the resulting table.

This is a fine solution, and if you can't provide a translated row for each, then you will need to at least define the rules of what can be shown if they are missing.

Here is how I solved this problem, albiet, 5 years ago:

ORIGINAL TABLE LAYOUT
    create table tbl
    (
     id
    ,some_nontranslatable_set_of_fields
    ,some_translatable_set_of_fields
    CONSTRAINT XPK PRIMARY KEY (id)
    );

MULTI_LINGUAL ARCHITECTURE:
    create table tbl_b
    (
     id
    ,some_nontranslatable_set_of_fields
    ,language_type
    CONSTRAINT XPK_B PRIMARY KEY (id)
    );

    create table language_type
    (ENG, SPN, ..., HOWEVER YOU DO YOUR TYPES);     create table tbl_l
    (
     id

    ,base_id  
    ,some_nontranslatable_set_of_fields
    ,some_translatable_set_of_fields

    CONSTRAINT XPK_L PRIMARY KEY (id)
    );

    alter table tbl_l add
    ( constraint r_b foreign key (base_id) references tbl_b);

    alter table tbl_language add
    ( constraint r_type foreign key (lanquage_type) references     language_type);

I then created a view called tbl joining these and I had a schema per language type restricting to language type. The user's choice of language defined what schema to log into.

But, with context and VPD, I would just set the preference on context and log into a single schema.

But, I still think you have to solve what the user sees if the language row doesn't exist, and that is an answer your business folks must give you.

-- 
Galen Boyer

Received on Mon Oct 25 2004 - 10:16:16 CDT