Re: problem referencing an attrib which is not unique

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 <patrick(dot)jacquot(at)anpe(dot)fr> wrote:
>
> Vic Rowan wrote:
>
> >
> >
> > ---------- Forwarded message ----------
> > From: *Vic Rowan* <mightymate(at)gmail(dot)com <mailto:mightymate(at)gmail(dot)com>>
> > Date: Feb 7, 2006 2:31 PM
> > Subject: problem referencing an attrib which is not unique
> > To: pgsql-sql(at)postgresql(dot)org <mailto:pgsql-sql(at)postgresql(dot)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 = '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
>