Обсуждение: Internationalization
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 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 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFJ34pd2FH5GXCfxAsRAlbLAJ95Yk0Ab5Zak5B7sRl6ux2ybgrR5ACgl+ZH wB8tgw7sotlCE/bCakW+OC4= =ATNN -----END PGP SIGNATURE-----
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-----