Referential data in a multilingual application

The application described in the previous post consists of a lot of tables.
Some of them are populated by the developers and are not maintainable by end users.
Most of these tables have columns that have to be translated. We found a generic solution for these kind of tables.

In the application all tables have an artificial primary key that is populated by a sequence.
We created a table [column_translations] with the following structure

create table COLUMN_TRANSLATIONS
(
 ID                      NUMBER(10)                  not null
 ,TABLE_NAME              VARCHAR2(30)      not null
 ,COLUMN_NAME             VARCHAR2(30)    not null
 ,RECORD_ID               NUMBER(14)           not null
 ,LANGUAGE                VARCHAR2(2)          not null
 ,TRANSLATION             VARCHAR2(4000) not null
)

Given a table name/column name and a record id the translation is actually stored. Around this table a generic function is created that retrieves the translation given a certain language

function get_translation
( p_table_name        in column_translations.table_name%type
, p_column_name      in column_translations.column_name%type
, p_record_id          in column_translations.record_id%type
, p_language            in column_translations.language%type
)
return column_translations.translation%type
;

Now every table gets a seperate view that contains translations for the columns that needs to be translated.
For example:

create or replace view general_messages_vw
(id, code, message, .....)
as
select osc.id
,      osc.code
,      nvl(package.get_translation
      ('GENERAL_MESSAGES'
      ,'MESSAGE'
      , osc.id
      ,package.get_language -- this function returns the actual language
      )
     ,....
     )

Now you have to redirect all calls to the original table to the newly created view. You can do this by shuffling around synonyms and grants.
We decided to actually redirect the calls by changing the calls in database and in the Oracle Forms and Oracle Reports.
We used this mechanism only for referential tables that are not maintained by end-users but you could use it for other referential tables as well.