Internationalization

Pedro Doria Meunier wrote:

Hi all,

I'm wondering how to internationalize contents of a table, short of
having a column for each language string ...
Anyone with some experience to share? :)

Regards,
Pedro Doria Meunier

How about parent child table layout. The child table has one record for
translation for each document. something like this

Create table ParentDoc (
docid serial,
description text )

Create table ChildDoc (
docid integer,
doc_text text,
short_description text,
language text )

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi Justin,

First of all thank you for your input. :)

Actually what I have is a fully internationalized site by means of
getttext.
*Some* of the content comes from the PGSQL database where 2 tables
relation with others (namely for sensor data description).

These tables have the simplest arrangement: id, description :]

I wondered if there was some sort of pgsql extension providing a text
replacement mechanism of sorts in order to achieve something like
gettext ...

I guess I'll have to resort to what I've previously thought of ...

Regards,
Pedro Doria Meunier.

Justin wrote:

Pedro Doria Meunier wrote:

Hi all,

I'm wondering how to internationalize contents of a table, short
of having a column for each language string ... Anyone with some
experience to share? :)

Regards, Pedro Doria Meunier

How about parent child table layout. The child table has one
record for translation for each document. something like this

Create table ParentDoc ( docid serial, description text )

Create table ChildDoc ( docid integer, doc_text text,
short_description text, language text )

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org

iD8DBQFJ3/bA2FH5GXCfxAsRAlZ0AKCnP9LDOJlOrDSF+Ci4G3CpdX8AlgCdFJqQ
5CrOIuWvgVBXCmyZwhjR8r8=
=DmmH
-----END PGP SIGNATURE-----

On Sat, Apr 11, 2009 at 02:47:53AM +0100, Pedro Doria Meunier wrote:

Actually what I have is a fully internationalized site by means of
getttext.
*Some* of the content comes from the PGSQL database where 2 tables
relation with others (namely for sensor data description).

Why not continue using gettext?

These tables have the simplest arrangement: id, description :]

I wondered if there was some sort of pgsql extension providing a text
replacement mechanism of sorts in order to achieve something like
gettext ...

As with any specialised problem, PG doesn't solve it directly but
provides various tools for you to solve it in which ever way is best for
you.

I guess I'll have to resort to what I've previously thought of ...

If, by this, you mean having a column for each language, I'd recommend
against doing this. Normalisation is normally the thing to aim for in
databases, so something like:

CREATE TABLE descriptions (
id INTEGER,
lang TEXT,
PRIMARY KEY (id,lang)
description TEXT
);

would generally be considered better. The reason being that this way
you don't need to change the database every time somebody wants to
translate the software into a new language. If you wanted to maintain
compatibility with the rest of the existing code, you could create a
view like:

CREATE VIEW descriptions AS
SELECT id, description
FROM descriptions
WHERE lang = 'en';

Or whatever language your messages are already in and call the table
above something else. Your code can carry on thinking there's a
"descriptions" table (or whatever you call the view) and doesn't need to
know that there are other languages available. You can then slowly move
the code across to the new version of the table and get rid of the view
when you're done.

--
Sam http://samason.me.uk/

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Thank you Sam for the valuable input!

Best regards,

Pedro Doria Meunier
GSM: +351 96 17 20 188
Skype: pdoriam

Sam Mason wrote:

On Sat, Apr 11, 2009 at 02:47:53AM +0100, Pedro Doria Meunier wrote:

Actually what I have is a fully internationalized site by means of
getttext.
*Some* of the content comes from the PGSQL database where 2 tables
relation with others (namely for sensor data description).

Why not continue using gettext?

These tables have the simplest arrangement: id, description :]

I wondered if there was some sort of pgsql extension providing a text
replacement mechanism of sorts in order to achieve something like
gettext ...

As with any specialised problem, PG doesn't solve it directly but
provides various tools for you to solve it in which ever way is best for
you.

I guess I'll have to resort to what I've previously thought of ...

If, by this, you mean having a column for each language, I'd recommend
against doing this. Normalisation is normally the thing to aim for in
databases, so something like:

CREATE TABLE descriptions (
id INTEGER,
lang TEXT,
PRIMARY KEY (id,lang)
description TEXT
);

would generally be considered better. The reason being that this way
you don't need to change the database every time somebody wants to
translate the software into a new language. If you wanted to maintain
compatibility with the rest of the existing code, you could create a
view like:

CREATE VIEW descriptions AS
SELECT id, description
FROM descriptions
WHERE lang = 'en';

Or whatever language your messages are already in and call the table
above something else. Your code can carry on thinking there's a
"descriptions" table (or whatever you call the view) and doesn't need to
know that there are other languages available. You can then slowly move
the code across to the new version of the table and get rid of the view
when you're done.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org

iD8DBQFJ4LhQ2FH5GXCfxAsRAqKwAJ98yZXXpFyVrQCqa5vMEA40UMDsXQCfcQXB
nlxeWHPba/Ab35F2gko4OVs=
=YGTU
-----END PGP SIGNATURE-----