From: Richard Huxton <dev@archonet.com>
To: M.D.G. Lange <mlange@dltmedia.nl>
Cc: pgsql-sql@postgresql.org
Subject: Re: rule or trigger?
Date: Tue, 07 Jun 2005 16:41:16 +0100
Message-ID: <42A5C01C.1050007@archonet.com> (raw)
In-Reply-To: <42A576E5.1040100@dltmedia.nl>
References: <42A576E5.1040100@dltmedia.nl>
M.D.G. Lange wrote:
> In order for a "dictionary" system I have created the following system:
>
> tbllanguages
> - ID
> - name
> Primary key ( ID )
>
> tbldictionary
> - wordid
> - languageid
> - value
> Primary key ( wordid, languageid)
>
> The idea is to have a word id in several languages, so you only have to
> look up the word id and give the language you would like to get the
> message in and you'll be presented the translation.
> So far so good... only wordid is not unique, making it not suitable to
> use it in foreign keys... however I'd like a similar idea:
>
> tblsystemmessages
> - ID
> - wordid
> - pgsqlerrorcode
> Primary key ( ID )
> "Foreign key" wordid references tbldictionary.wordid
>
> It is not possible to create a constraint Foreign key for "wordid". No
> problem there, but I want to be certain that a given wordid exists in
> tbldictionary.
I'd split the existence of the word from its presence in the
language-lookup table. Then you can have a reference to the existence of
the "raw word".
raw_words
- wordid
- wordname
Of course "wordname" will probably be in one of your target languages
anyway, but conceptually doesn't have to be.
You can always have triggers on tbldictionary to automatically insert
into raw_words if you'd like.
--
Richard Huxton
Archonet Ltd
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: dev@archonet.com, mlange@dltmedia.nl
Subject: Re: rule or trigger?
In-Reply-To: <42A5C01C.1050007@archonet.com>
* 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