pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedproblem referencing an attrib which is not unique
4+ messages / 3 participants
[nested] [flat]
* problem referencing an attrib which is not unique
@ 2006-02-07 13:45 Vic Rowan <mightymate@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Vic Rowan @ 2006-02-07 13:45 UTC (permalink / raw)
To: pgsql-sql
---------- Forwarded message ----------
From: Vic Rowan <mightymate@gmail.com>
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: problem referencing an attrib which is not unique
@ 2006-02-07 14:17 Richard Huxton <dev@archonet.com>
parent: Vic Rowan <mightymate@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Richard Huxton @ 2006-02-07 14:17 UTC (permalink / raw)
To: Vic Rowan <mightymate@gmail.com>; +Cc: pgsql-sql
Vic Rowan wrote:
> 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.
The problem is with your event_msg table. You have two concepts -
"event" and "event_msg_localised" but only one table.
The logs table should reference "event" (which has one row for each
event and a unique event_id). The "event_msg_localised" table also
references "event".
The event table might only contain the event_id primary-key, but I'd
probably put a description in their for my own use (perhaps saying what
values to expect).
HTH
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: problem referencing an attrib which is not unique
@ 2006-02-07 14:27 Patrick JACQUOT <patrick.jacquot@anpe.fr>
parent: Vic Rowan <mightymate@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Patrick JACQUOT @ 2006-02-07 14:27 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
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 <mailto: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 = '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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: problem referencing an attrib which is not unique
@ 2006-02-07 15:14 Vic Rowan <mightymate@gmail.com>
parent: Patrick JACQUOT <patrick.jacquot@anpe.fr>
0 siblings, 0 replies; 4+ messages in thread
From: Vic Rowan @ 2006-02-07 15:14 UTC (permalink / raw)
To: pgsql-sql
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.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 <mailto: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 = '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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-02-07 15:14 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-07 13:45 problem referencing an attrib which is not unique Vic Rowan <mightymate@gmail.com>
2006-02-07 14:17 ` Richard Huxton <dev@archonet.com>
2006-02-07 14:27 ` Patrick JACQUOT <patrick.jacquot@anpe.fr>
2006-02-07 15:14 ` Vic Rowan <mightymate@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox