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
