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" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
