Thread: problem referencing an attrib which is not unique

<br /><br />---------- Forwarded message ----------<br /><span class="gmail_quote">From: <b
class="gmail_sendername">VicRowan</b> <<a href="mailto:mightymate@gmail.com">mightymate@gmail.com</a>><br />Date:
Feb7, 2006 2:31 PM <br />Subject: problem referencing an attrib which is not unique<br />To: <a
href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a><br/><br /></span><br />hello everybody,<br /><br
/>Ineed 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
thetables so that displaying a log message is as simple as looking up the event_id from the 'logs' table and similarly
lookingup 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. <br /><br
/>CREATETABLE event_msg (<br />  event_id varchar(30) NOT NULL,<br />  language char(2) NOT NULL,<br />  predefined_msg
varchar(250)NOT NULL,<br />  PRIMARY KEY (event_id, language)<br />);<br /><br />CREATE TABLE logs (<br />  id int NOT
NULL,<br />  event_id varchar(30) REFERENCES event_msg (event_id) NOT NULL,  <br />  placeholder_values
varchar(250),<br/>  priority varchar(20) NOT NULL,<br />  timestamp Date NOT NULL,<br />  primary key (id)<br />);<br
/><br/><br />The problem I am facing is the event_id from logs is not able to reference event_id from event_msg as its
notunique. <br />There are as many entries for each event_id as there are languages supported in the 'event_msg' table.
<br/>I would be glad if somebody could suggest some work around here to the above structure. Or alternately do I need
toalter the table structure altogether and if so what is the better way of doing this? <br /><br />Thanks in advance
forany help offered.<br /><br />Cheers,<br /><span class="sg">Vic Rowan.<br /><br /></span> 

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

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 NULLlanguage char(2) not
nullevent-translationvarchar(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

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.