Internationalisation of database content (text columns)

(Since I got no replies on the novice list, I repost this question here)

Hello,

I'm working on a database schema which contains lots of "type code lookup" tables. The entries of these tables are also hierarchically related among themselves (subtype/supertype), to store rather large and quite complex taxonomies.

Currently the schema does not provide for translation of the entries in these tables, although it is meant to be used for inter-company data exchange, i.e. for use by many different people having all different native languages. When searching for "best practice" guides about the topic of transparently translating database content, I came across about this example:

http://rwec.co.uk/pg-atom/i18n.html

The basic method looks pretty intuitive to me, except that I don't understand why it wouldn't be more obvious to use a composite type, with language codes as column names, instead of an array for the custom "translated text" data type.

Any opinions from more experienced "mahouts"? Any obvious drawbacks of using such a composite type? Other than the fact that the schema has to be modified to add languages, which wouldn't be that desastrous in this case.

Are there any other (better?) "best practice" methods for the internationalisation of database content? Maybe methods that use capabilities (e.g. text search/thesaurus?) already built-into PostgreSQL?

BTW: Methods that use a single table to hold all translations for all strings in all tables of the entire schema are not very useful in this case, since it can't be excluded that depending on the context (i.e. the specific semantics of the specific "type code lookup" table) the translation of one and the same string in one language will be different in other languages.

TIA,

Sincerely,

Wolfgang Keller