Help: How to design a Thesaurus (kind of)

  I'm working for the first time in a commercial application with Oracle and I'm trying to create a Thesaurus for all the tables I'll be using.   

  It will something like a terminology list:

  Thesaurus table:    code       number     }  Primary key
                      type       char       }
                      name       char        - portuguese name
                      english    char        - english name
                      french     char        - french name
                      help       char        - some help
                      ....

  If I have something like:   

  thesaurus

  code type name english french help ...

     1  aa       aa1        ...
     2  aa       aa2        ......
     3  aa       aa3        .......
     1  bb       bb1        .....
     2  bb       bb2        ........
     1  cc       cc1        .....
     2  cc       cc2        .........

  This way I could have in some other tables:

  table blabla1

  field1 field2 ... aa

  xxxx     x                1
  cccc     cc               2

  table blabla2

  field1 field2 ... aa bb

  dfdf     fd               1      2
  fdfd     fd               2      1

  I have implemented this using the (code + type) primary key in the thesaurus table and a insert trigger in all the fields related to the thesaurus:    

  ex: for field aa in blabla1 and blabla2 it is:

      CREATE TRIGGER check_aa
      BEFORE INSERT or UPDATE
      ON blabla1
      DECLARE dummy INTEGER;
      BEGIN
        SELECT COUNT(*) 
        INTO dummy
        FROM thesaurus
        WHERE thesaurus.type = aa AND
              thesaurus.code = :new.aa
        IF DUMMY = 0
        THEN raise_application_error(.....);
      END;

  This way I have to create a trigger for each field related to the thesaurus.

  Is this the best way to do it?
  Any other idea to implement this?
  Any comment or suggestion is welcomed!

  Pedro Pimentel.
  ppf_at_fct.unl.pt Received on Sun May 01 1994 - 22:19:06 CEST