[SQL] problem referencing an attrib which is not unique

[Vic Rowan]

---------- Forwarded message ----------
From: Vic Rowan <[EMAIL PROTECTED]>
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.