Re: Multilanguage application & database

> What about using a collection (nested table, varray) of
> type (ISO_CODE, DESCRIPTION) for e.g. column DEPT.DEPTNAME?

Very nice solution, which would almost do the trick. Take this setup:

CREATE TABLE Languages (
  IdLanguage NUMBER(2) NOT NULL,
  IdExpression NUMBER(5) NOT NULL,
  CONSTRAINT Languages_PK
  PRIMARY KEY (IdLanguage) );

CREATE TYPE Translation AS OBJECT (
  IdLanguage NUMBER(2),
  Translation VARCHAR2(2000) );

CREATE TYPE Translations IS TABLE OF Translation;

CREATE TABLE Expressions (
  IdExpression NUMBER(5) NOT NULL,
  Translations Translations ,
  CONSTRAINT Expressions_PK
  PRIMARY KEY (IdExpression) )
  NESTED TABLE Translations STORE AS Translations_Expressions;

ALTER TABLE Languages ADD CONSTRAINT Languages_FK   FOREIGN KEY (IdExpression)
  REFERENCES Expressions(IdExpression) ;

ALTER TABLE Translations_Expressions ADD UNIQUE (IdLanguage);

ALTER TABLE Translations_Expressiones ADD CONSTRAINT Translations_Expressions_FK
  FOREIGN KEY (IdLanguage)
  REFERENCES Languages (IdLanguage) ;

However, this last statement returns an ORA-30730; a nested table cannot have foreign key constraints?!
This could result in making translations for unexisant languages, or the ability to delete a language which is still in use (with the constraint Oracle would prevent these).
Still, it's a far more elegant solution to no relations at all.

Still looking into it (have an idea)...

K Received on Tue Oct 26 2004 - 07:46:50 CDT