Re: Multilanguage application & database

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

On 25 Oct 2004, galenboyer_at_hotpop.com wrote:
> 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

Sorry, need to remove the above attribute, 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:26:15 CDT