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.
