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 <[EMAIL PROTECTED]> wrote:
Vic Rowan wrote:>>> -- Forwarded message ------> From: *Vic Rowan* <[EMAIL PROTECTED]
[EMAIL PROTECTED]>>> Date: Feb 7, 2006 2:31 PM> Subject: problem referencing an attrib which is not unique> To: pgsql-sql@postgresql.org 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 referenceCREATE TABLE eventlist (
event_id varchar(30) PRIMARY-KEY);One to give the messages translationsCREATE 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 tableCREATE 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-idis 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)?.hthP. Jacquot---(end of broadcast)---TIP 6: explain analyze is your friend
