From: Patrick JACQUOT <patrick.jacquot@anpe.fr>
Cc: pgsql-sql@postgresql.org
Subject: Re: problem referencing an attrib which is not unique
Date: Tue, 07 Feb 2006 15:27:03 +0100
Message-ID: <43E8AE37.1080205@anpe.fr> (raw)
In-Reply-To: <450bdf80602070545v56252a49g8dfe0d0d04ae9065@mail.gmail.com>
References: <450bdf80602070531w78b687bdj15e640945b15b49@mail.gmail.com>
<450bdf80602070545v56252a49g8dfe0d0d04ae9065@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: patrick.jacquot@anpe.fr
Subject: Re: problem referencing an attrib which is not unique
In-Reply-To: <43E8AE37.1080205@anpe.fr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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