Re: advice on schema for multilingual text

On Sun, May 21, 2006 at 06:55:18PM +0200, Tino Wildenhain wrote:

> > I have a website that has multilingual text stored in the database.
> > Currently I just have a flat table (lets called it "translations"), one
> > row per text item, one column per language. This works OK, for now, but
> > I am looking at a redesign. Mostly I want to keep information about the
> > languages in teh db as well, so that look like an extra table, one row
> > per lang.

I have re-implemented gettext in (pl/pg(SQL)) if you are
interested. The language for a particular db user can be
switched on the fly (but it's bound to the user so is the
same for all concurrent connections of this user which may
not be useful for web apps).

It roughly works like so:

select set_curr_lang('de_DE');

to set the language for the current db user to de_DE
(German). Then

select i18n.i18n('some text');

to insert a string to be translated. Then

select i18n_upd_tx('some text', 'etwas Text', 'de_DE');

to insert the translation (for locale de_DE). Then in your
queries do

select _(some_column) from ... where ...;

and it will return the text from some_column either
translated into German or in the language it was inserted
into the database in if there's no translation available.

I routinely use this in views where I do

create view ... as
select
...
label,
_(label) as l10n_label,
...
from
...
;

All this is isolated into a schema "i18n":

http://cvs.savannah.gnu.org/viewcvs/gnumed/gnumed/server/sql/gmI18N.sql?rev=1.25&root=gnumed&view=log
http://cvs.savannah.gnu.org/viewcvs/gnumed/gnumed/server/sql/gmI18N-dynamic.sql?rev=1.3&root=gnumed&view=log

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346