Yves
If user.additionalkeylist and tag.readaccesskeylist are not lists,
naming them `...list` misleads & distracts.
You asked earlier how to fit my preliminary solution into your problem.
The answer is to (i) write the query that lists access-denied messages,
then (ii) write a simple exclusion join from messages to that derived table.
But on (i), how user.additionalkeylist and tag.readaccesskeylist work
remains confusing. You appear to say access may come from ...
(i) message->message_revision->message_revision_tag.readaccesskeylist, or
(ii) message_revision->user.additionalkeylist
which implies there are positive values which provide access, but your
original query used the condition
readaccesskeylist /is not null/
as a test for access /refusal/, which seems to contradict what you now say.
PB
-----
Yves Goergen wrote:
> On 11.02.2008 17:32 CE(S)T, Peter Brawley wrote:
>> 1. user.additionalkeylist and tag.readaccesskeylist are atomic
>> despite their names?
>
> Yes, I forgot the types. Everything is scalar, varchar or integer.
> There are not set or otherwise complex data types.
>
>> 2. You have reciprocal foreign keys, keylist.key referencing
>> user(userID) and user.additionalkeylist referencing keylist.keylistID?
>
> Basically, yes. Although there is a contraint in my application that
> is not visible in the database structure: I distinguish between
> "personal" and "virtual" keys. Personal keys must not be part of a
> user's additional keys list. Virtual keys must not have an additional
> keys list on their own. (And they must not have logon information.) So
> there cannot be a cyclic reference. This is documented in the source
> code and will be enforced on the application layer later.
>
