Alternative access solution

Hello.
We are looking for alternative access solution for our current data structure: Simplified:

ACCOUNT              - Account data (ACC_ID)
ORGANIZATION         - Organization data (ORG_ID) - hierarchical structure
ACCOUNT_ORGANIZATION - Which organizations can access accounts (ACC_ID, ORG_ID)
USER                 - User data (USR_ID)
USER_ORGANIZATION    - Which organizations user can access. (USR_ID, OGR_ID)

So we want 2 that user is able to see all of his accounts. Currently we do it like this:
SELECT *
  FROM ACCOUNT ACC
 WHERE EXISTS (SELECT 1

                 FROM ACCOUNT_ORGANIZATION ACO
                WHERE ACO.ACC_ID = ACC.ACC_ID AND
                      ACO.ORG_ID IN (SELECT ORG_ID
                                       FROM USER_ORGANIZATION
                                      WHERE USR_ID = USER)
              )

We tried all possible ways of this structure but is not good enough. What we would like is to get rid of ACCOUNT_ORGANIZATION table.

Any ideas.

Thanks in advance.
Miha Received on Fri Nov 09 2001 - 06:31:12 CST