Re: Multilanguage application & database

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

Very nice solution!
A varray would be too restrictive in use, but a nested table almost does the trick.

Take this:

CREATE TABLE Languages (
  IdLanguage NUMBER(2) NOT NULL,
  IdExpression NUMBER(5) NOT NULL,
  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 ,
  PRIMARY KEY (IdExpression) )
  NESTED TABLE Translations STORE AS Translations_Expressions;

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

ALTER TABLE Translations_Expressions ADD UNIQUE (IdLanguage);

ALTER TABLE Translations_Expressions ADD   FOREIGN KEY (IdLanguage)
  REFERENCES Languages (IdLanguage) ;

This last statement gives an ORA-30730; nested tables cannot have foreign key contstraints?!
That would mean you could create translations for unexistant languages, or delete a language, even if it has translations.

However it gave me an idea: an external table instead of a nested table DOES have foreign key constraints. I'll post my final solution under the original post.

Thanks,
K Received on Wed Oct 27 2004 - 03:42:19 CDT