X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 7BCD69DCBF8 for ; Tue, 7 Feb 2006 11:14:09 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 78488-06 for ; Tue, 7 Feb 2006 11:14:11 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.196]) by postgresql.org (Postfix) with ESMTP id 954369DCBB2 for ; Tue, 7 Feb 2006 11:14:04 -0400 (AST) Received: by wproxy.gmail.com with SMTP id i11so644142wra for ; Tue, 07 Feb 2006 07:14:08 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=erPcRqzjuVNSD7axyn3f9pn1EIwksF/3MzfXgkR3xWp4pzRipwjRwMmKYCe1OG1lt8Y8uOp1Cene/G8snMVzQ4bFFYjnrlPPLyBoDvYj7Cnu5gu1kV035KrA37jOd1KwbEx6dYybvZV9BvJEalKiPztyEEVQ/i8v4dEImNMqkII= Received: by 10.64.220.7 with SMTP id s7mr760182qbg; Tue, 07 Feb 2006 07:14:08 -0800 (PST) Received: by 10.65.59.4 with HTTP; Tue, 7 Feb 2006 07:14:08 -0800 (PST) Message-ID: <450bdf80602070714u61f282f5qba3098ac08ef2ddf@mail.gmail.com> Date: Tue, 7 Feb 2006 16:14:08 +0100 From: Vic Rowan To: pgsql-sql@postgresql.org Subject: Re: problem referencing an attrib which is not unique In-Reply-To: <43E8AE37.1080205@anpe.fr> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_14790_26589360.1139325248444" References: <450bdf80602070531w78b687bdj15e640945b15b49@mail.gmail.com> <450bdf80602070545v56252a49g8dfe0d0d04ae9065@mail.gmail.com> <43E8AE37.1080205@anpe.fr> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.602 required=5 tests=[AWL=0.601, HTML_MESSAGE=0.001] X-Spam-Score: 0.602 X-Spam-Level: X-Archive-Number: 200602/62 X-Sequence-Number: 24072 ------=_Part_14790_26589360.1139325248444 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Thanks a lot Patrick and Richard for the help! Especially about the details that I hadnt even asked for, like 2 chars for language and I guess it makes very much sense in considering these for situations like EN-US or EN-UK. It was really insightful. On 2/7/06, Patrick JACQUOT wrote: > > Vic Rowan wrote: > > > > > > > ---------- Forwarded message ---------- > > From: *Vic Rowan* > > > Date: Feb 7, 2006 2:31 PM > > Subject: problem referencing an attrib which is not unique > > To: pgsql-sql@postgresql.org > > > > > > hello everybody, > > > > I need some thing like this below for an application which stores log > > messages in multiple languages. The table 'event_msg' stores > > predefined messages in multiple languages which can be populated with > > place holder values from the application. (These of course are > > language independent). So, the event_id associates these predefined > > messages from both the tables so that displaying a log message is as > > simple as looking up the event_id from the 'logs' table and similarly > > looking up the event_id and language from the 'event_msg' table to > > retreive the predefined_msg with the correct language - the > > application determines the lang from a settings file - and combining > > them to display the log message. > > > > CREATE TABLE event_msg ( > > event_id varchar(30) NOT NULL, > > language char(2) NOT NULL, > > predefined_msg varchar(250) NOT NULL, > > PRIMARY KEY (event_id, language) > > ); > > > > CREATE TABLE logs ( > > id int NOT NULL, > > event_id varchar(30) REFERENCES event_msg (event_id) NOT NULL, > > placeholder_values varchar(250), > > priority varchar(20) NOT NULL, > > timestamp Date NOT NULL, > > primary key (id) > > ); > > > > > > The problem I am facing is the event_id from logs is not able to > > reference event_id from event_msg as its not unique. > > There are as many entries for each event_id as there are languages > > supported in the 'event_msg' table. > > I would be glad if somebody could suggest some work around here to the > > above structure. Or alternately do I need to alter the table structure > > altogether and if so what is the better way of doing this? > > > > Thanks in advance for any help offered. > > > > Cheers, > > Vic Rowan. > > > I think you need three tables > > One to list the allowable events, which will be used as reference > CREATE TABLE eventlist ( > event_id varchar(30) PRIMARY-KEY > ); > > One to give the messages translations > CREATE TABLE messagetranslations( > event-id varchar(30) references eventlist (event_id) NOT NULL > language char(2) not null > event-translation varchar(250) > PRIMARY KEY (event_id, language) > ); > > and your log table > CREATE TABLE logs ( > id int NOT NULL, > event_id varchar(30) REFERENCES eventlist (event_id) NOT NULL, > placeholder_values varchar(250), > priority varchar(20) NOT NULL, > timestamp Date NOT NULL, > primary key (id) > ); > > btw, event-id could be just an integer. If, as I understand, event-id > is so large a string, > it's probably because it contains the english name of the event. > Just put it in an occurrence of messagetranslation, with language =3D 'EN= ' > other thing : with only 2 chars as language id, how do you distinguish > EN-US and EN-UK > (or whatever id the latter can have assigned)?. > > hth > P. Jacquot > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > ------=_Part_14790_26589360.1139325248444 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Thanks a lot Patrick and Richard for the help! Especially about the details= that I hadnt even asked for, like 2 chars for language and I guess it make= s very much sense in considering these for situations like EN-US or EN-UK. = It was really insightful.

On 2/7/06, Patrick JACQUOT <p= atrick.jacquot@anpe.fr> wrote:
Vic Rowan wrote:

>
>
> ---------- Forwarded message -= ---------
> From: *Vic Rowan* <mightymate@gmail.com <mailto: mightymate@gmail.com>>
> Date: Feb 7, 2006 2:31 PM
> = Subject: problem referencing an attrib which is not unique
> To: pgsql-sql@postgresql.org <ma= ilto: pgsql-sql@postgresql.org>= ;
>
>
> hello everybody,
>
> I need some thin= g like this below for an application which stores log
> messages in m= ultiple languages. The table 'event_msg' stores
> predefined messages in multiple languages which can be populated w= ith
> place holder values from the application. (These of course are<= br>> language independent). So, the event_id associates these predefined
> messages from both the tables so that displaying a log message is = as
> simple as looking up the event_id from the 'logs' table and simi= larly
> looking up the event_id and language from the 'event_msg' tab= le to
> retreive the predefined_msg with the correct language - the
>= ; application determines the lang from a settings file - and combining
&= gt; them to display the log message.
>
> CREATE TABLE event_msg= (
>   event_id varchar(30) NOT NULL,
>   lan= guage char(2) NOT NULL,
>   predefined_msg varchar(250) NOT= NULL,
>   PRIMARY KEY (event_id, language)
> );
&= gt;
> CREATE TABLE logs (
>   id int NOT NULL,
>   event_id varchar(30) REFERENCES event_msg (event_id) N= OT NULL,
>   placeholder_values varchar(250),
> =   priority varchar(20) NOT NULL,
>   timestamp Date NO= T NULL,
>   primary key (id)
> );
>
>
> The problem I am facing is the event_id fro= m logs is not able to
> reference event_id from event_msg as its not = unique.
> There are as many entries for each event_id as there are la= nguages
> supported in the 'event_msg' table.
> I would be glad if som= ebody could suggest some work around here to the
> above structure. O= r alternately do I need to alter the table structure
> altogether and= if so what is the better way of doing this?
>
> Thanks in advance for any help offered.
>
> Ch= eers,
> Vic Rowan.
>
I think you need three tables

O= ne to list the allowable events, which will be used as reference
CREATE = TABLE eventlist (
  event_id varchar(30) PRIMARY-KEY
);

One to give t= he messages translations
CREATE TABLE messagetranslations(
event-id = varchar(30) references eventlist (event_id) NOT NULL
language char(2) n= ot null
event-translation varchar(250)
PRIMARY KEY (event_id, language));

and your log table
CREATE TABLE logs (
  id int = NOT NULL,
  event_id varchar(30) REFERENCES eventlist (event_i= d) NOT NULL,
  placeholder_values varchar(250),
  priority varcha= r(20) NOT NULL,
  timestamp Date NOT NULL,
  prim= ary key (id)
);

btw,  event-id could be just an integer= . If, as I  understand, event-id
is so large a string,
it's probably because it contains the english name of the event.
Jus= t put it in an occurrence of messagetranslation, with language =3D 'EN'
= other thing : with only 2 chars as language id, how do you distinguish
EN-US and EN-UK
(or whatever id  the latter can have assigned)= ?.

hth
P. Jacquot


---------------------------(end of b= roadcast)---------------------------
TIP 6: explain analyze is your frie= nd

------=_Part_14790_26589360.1139325248444--