Thread: internationalizing text
so who's got a clever implementation of cross-linguistic texts?
create table something (
id serial,
yadayada int4,
whatever date,
mumble float8,
en varchar(50), -- english
es varchar(50), -- espanol
fr varchar(50), -- francais
de varchar(50), -- deutsch
...
);
or maybe
create table something (
id serial,
yadayada int4,
whatever date,
mumble float8,
...
);
create table something_text (
id int4 references something(id),
lang varchar(5), -- language code 'en-us','it','jp'...
descr varchar(50)
);
anybody done something like this? is there another concept or are
these two the whole ball-o-wax? pro's and con's?
--
It is always hazardous to ask "Why?" in science, but it is often
interesting to do so just the same.
-- Isaac Asimov, 'The Genetic Code'
will@serensoft.com
http://newbieDoc.sourceforge.net/ -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
I think that the standard way to do this is to use a resource identifier, and then have a separate table with all strings. That's the way that most internationalisation is done in programs, and it's probably not bad for databases either.
So maybe:
create table something (
id serial,
yadayada int4,
whatever date,
mumble float8,
...
id_resource int4 references something_text(id)
);
create table something_text (
id int4,
lang varchar(5), -- language code 'en-us','it','jp'...
descr varchar(50)
);
Anyway, just a thought...
MikeA
-----Original Message-----
From: will trillich
To: pgsql-general@postgresql.org
Sent: 23/03/01 04:06
Subject: [GENERAL] internationalizing text
so who's got a clever implementation of cross-linguistic texts?
create table something (
id serial,
yadayada int4,
whatever date,
mumble float8,
en varchar(50), -- english
es varchar(50), -- espanol
fr varchar(50), -- francais
de varchar(50), -- deutsch
...
);
or maybe
create table something (
id serial,
yadayada int4,
whatever date,
mumble float8,
...
);
create table something_text (
id int4 references something(id),
lang varchar(5), -- language code 'en-us','it','jp'...
descr varchar(50)
);
anybody done something like this? is there another concept or are
these two the whole ball-o-wax? pro's and con's?
--
It is always hazardous to ask "Why?" in science, but it is often
interesting to do so just the same.
-- Isaac Asimov, 'The Genetic Code'
will@serensoft.com
http://newbieDoc.sourceforge.net/ -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
_________________________________________________________________________
This e-mail and any attachments are confidential and may also be privileged and/or copyright
material of Intec Telecom Systems PLC (or its affiliated companies). If you are not an
intended or authorised recipient of this e-mail or have received it in error, please delete
it immediately and notify the sender by e-mail. In such a case, reading, reproducing,
printing or further dissemination of this e-mail is strictly prohibited and may be unlawful.
Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free
from computer viruses or other defects. The opinions expressed in this e-mail and any
attachments may be those of the author and are not necessarily those of Intec Telecom
Systems PLC.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
__________________________________________________________________________
On Fri, Mar 23, 2001 at 09:29:17AM -0000, Michael Ansley wrote:
> I think that the standard way to do this is to use a resource identifier,
> and then have a separate table with all strings. That's the way that most
> internationalisation is done in programs, and it's probably not bad for
> databases either.
>
> So maybe:
>
> create table something (
> id serial,
> yadayada int4,
> whatever date,
> mumble float8,
> ...
> id_resource int4 references something_text(id)
> );
> create table something_text (
> id int4,
> lang varchar(5), -- language code 'en-us','it','jp'...
> descr varchar(50)
> );
i was thinking that it was probably the normalization-friendly
version that would be most flexible.
but here's where i run into snags:
create table nation (
code char(2),
lang varchar(5),
descr varchar(50),
primary key (code)
);
insert into nation values ('us','en','United States');
insert into nation values ('us','fr','Etats Unis');
create table org (
...
postcode varchar(12),
nation char(2) references nation(code),
...
);
how do i get the proper "descr" field from table "nation" that's
in language "lang=xyz" using a view, when i don't know which
language will be used at 'compile time'? a straight select/view
will show ALL languages for EACH 'record' sought:
select o.*,n.descr
from nation n, org o
where o.nation = n.code
-- and n.lang = someUnknownQuantityToBeDeterminedLater
-- /* simple global variables would solve this, ahem... :) */
;
right now i've got all the data-driven stuff built into the
sql/plpgsql; the perl code does only the presenting of the
data... i'd like to keep the functions separated, but it doesn't
look possible here. ideas?
--
It is always hazardous to ask "Why?" in science, but it is often
interesting to do so just the same.
-- Isaac Asimov, 'The Genetic Code'
will@serensoft.com
http://newbieDoc.sourceforge.net/ -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
You can use a where clause when selecting from a veiew, so the following
should work for you:
CREATE VIEW my_view AS SELECT o.*, n.descr, n.lang
FROM nation n, org o
WHERE o.nation = n.code
SELECT * FROM my_view WHERE n.lang = someKnownQuantity
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
On Fri, 23 Mar 2001, will trillich wrote:
> On Fri, Mar 23, 2001 at 09:29:17AM -0000, Michael Ansley wrote:
> > I think that the standard way to do this is to use a resource identifier,
> > and then have a separate table with all strings. That's the way that most
> > internationalisation is done in programs, and it's probably not bad for
> > databases either.
> >
> > So maybe:
> >
> > create table something (
> > id serial,
> > yadayada int4,
> > whatever date,
> > mumble float8,
> > ...
> > id_resource int4 references something_text(id)
> > );
> > create table something_text (
> > id int4,
> > lang varchar(5), -- language code 'en-us','it','jp'...
> > descr varchar(50)
> > );
>
> i was thinking that it was probably the normalization-friendly
> version that would be most flexible.
>
> but here's where i run into snags:
>
> create table nation (
> code char(2),
> lang varchar(5),
> descr varchar(50),
> primary key (code)
> );
> insert into nation values ('us','en','United States');
> insert into nation values ('us','fr','Etats Unis');
>
> create table org (
> ...
> postcode varchar(12),
> nation char(2) references nation(code),
> ...
> );
>
> how do i get the proper "descr" field from table "nation" that's
> in language "lang=xyz" using a view, when i don't know which
> language will be used at 'compile time'? a straight select/view
> will show ALL languages for EACH 'record' sought:
>
> select o.*,n.descr
> from nation n, org o
> where o.nation = n.code
> -- and n.lang = someUnknownQuantityToBeDeterminedLater
> -- /* simple global variables would solve this, ahem... :) */
> ;
>
> right now i've got all the data-driven stuff built into the
> sql/plpgsql; the perl code does only the presenting of the
> data... i'd like to keep the functions separated, but it doesn't
> look possible here. ideas?
>
> --
> It is always hazardous to ask "Why?" in science, but it is often
> interesting to do so just the same.
> -- Isaac Asimov, 'The Genetic Code'
>
> will@serensoft.com
> http://newbieDoc.sourceforge.net/ -- we need your brain!
> http://www.dontUthink.com/ -- your brain needs us!
>