Multilanguage application & database

Hi,

We have to create a multilanguage application; meaning that tables in the database with fields like "description", "label", "name", etc. need this information in more than 1 language, so it can be shown to each user in his own language. So I'm NOT asking for different character sets (I've found dozens of posts about that).

e.g.: EMP(IDEMP, NAME, IDDEPT) >---- DEPT(IDDEPT, NAME) Some EMP's row: IDEMP: "1", NAME: "A.KING", IDDEPT: "1" Some DEPT's row: IDDEPT: "1", NAME: "ACCOUNTING" Some result for English user: "A.KING works at ACCOUNTING" Some result for Spanish user: "A.KING trabaja a CONTABILIDAD"

POSSIBILITY 1:
Putting in a field for each language isn't really possible, since we don't know how many languages the application will use (and new ones might be added later).
-> DEPT(IDDEPT, NAME_EN, NAME_SP, NAME_NL, ...)
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. We'd need a composite key of IDDEPT and IDLANG, which results in the link to EMP's foreign key being lost (or we should remove the foreign key constraint and manage it ourselves, but that means removing that what's Oracle all about)!
-> DEPT(IDDEPT, NAME, IDLANG) >---- LANG(IDLANG, NAME)
DEPT's row (1): IDDEPT: "1", NAME: "ACCOUNTING", IDLANG: "1" DEPT's row (2): IDDEPT: "1", NAME: "CONTABILIDAD", IDLANG: "2" ...

POSSIBILITY 3:
Thanks for your suggestions, guys!

K Received on Fri Oct 22 2004 - 08:25:59 CDT