Re: problem referencing an attrib which is not unique - Mailing list pgsql-sql
From | Patrick JACQUOT |
---|---|
Subject | Re: problem referencing an attrib which is not unique |
Date | |
Msg-id | 43E8AE37.1080205@anpe.fr Whole thread Raw |
In response to | problem referencing an attrib which is not unique (Vic Rowan <mightymate@gmail.com>) |
Responses |
Re: problem referencing an attrib which is not unique
(Vic Rowan <mightymate@gmail.com>)
|
List | 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 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