List:General Discussion« Previous MessageNext Message »
From:Yves Goergen Date:November 3 2007 10:28pm
Subject:Re: Foreign keys on non-unique columns (problem)
View as plain text  
On 03.11.2007 22:52 CE(S)T, Yves Goergen wrote:
> Is my design bad?

I should explain why I do it this way at all. There's some other tables
in my system that need to keep a list of "keys" (i.e. user IDs) for
several actions. A message (one of the tables) has one keylist for read
access, one for alter access and a third one for reply access. The lists
(if not NULL = empty) contain a list of authorised users to perform the
respective action. (If NULL, everybody's allowed.)

In an older design, I had a separate table for each list type, which was
6 tables altogether. Instead of a "keylist"."KeylistId", there was e.g.
a "MessageReadAccessKeys"."MessageId" referencing "message"."MessageId".
First, this makes 5 more tables and second, I doubt that it would solve
my "non-unique foreign key" problem.

What I need is to store those keys for several tasks, object types and
instances of them.

What I would like to have is the DBMS keeping those references valid.

I guess my last chance is implementing this check in my application
(which I already had before I chose to entirely rely on referential
integrity and then deleted these few lines). Please tell me if there's a
better way.

PS: I searched a little more and found out that PostgreSQL also forbids
foreign keys referencing non-unique columns (like in SQL92) due to
serious bugs in the past, which is one more reason why I don't want to
keep it this way. (The whole thing should be somewhat portable...)

-- 
Yves Goergen "LonelyPixel" <nospam.list@stripped>
Visit my web laboratory at http://beta.unclassified.de
Thread
Foreign keys on non-unique columns (problem)Yves Goergen3 Nov
  • Re: Foreign keys on non-unique columns (problem)Yves Goergen3 Nov
  • Re: Foreign keys on non-unique columns (problem)Martijn Tonies4 Nov
    • Re: Foreign keys on non-unique columns (problem)Yves Goergen4 Nov