Re: Alternative access solution

ed.prochak_at_alltel.com (Ed prochak) wrote in message news:<e51b160.0111091450.62dfb1c9_at_posting.google.com>...
> miha.znidarsic_at_ixtlan-team.si (Miha) wrote in message news:<8c69a0da.0111090431.3fe3f665_at_posting.google.com>...
> > 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
>
>
> What do you mean by "not good enough"??
> and then do you mean to get rid of ACCOUNT_ORGANIZATION from the
> query or from the database?
>
> Do you mean you tried other ways of organizing the query, or did you
> also try reorganizing the tables (different tables with different
> relations). What exactly did you try?
>
> Please explain the problem.

Hello. Thanks 4 Your answer.

We tried many things, from changing Select query to changing table design. Our main point was to get rid of table ACCOUNT_ORGANIZATION or other tables 4 every ACCOUNT row.
We tried with adding field ORGANIZATIONS to table ACCOUNT. This field would hold all organizations that can use this account. So we try 2 use function 2 verify this organizations are in list of organizations user can see. Using function gave us bad results. We tried also with Row Level Security but limitation of 2000 chars was problem.

Problem was that plain select on table ACCOUNT resulted in some 4000 block gets.
When we used other tables 2 restrict rows, we came to minimum 350.000 block gets.

What we wanted was alternative solution of this problem. Maybe using some math functions or some principles used in similar situations (like folder protection where folder equals account, users are in groups, groups see some folders).

Finally we found 1 pretty good solution with cost of 40.000 block gets.
In ACCOUNT we put field ORGANIZATIONS which holds all organizations that see this account written in way that we can check easily: '.4.5.6.11.43' (4,5,6,11,43) are ID of organizations. Than we created new table ALL_USER_ORGANIZATION which holds all organizations user work 4 in the way that has fields USR_ID, ORG1, ORG2, ORG3,... ORG40. So 1 user has normally few rows in this table and ORGx hold ID (65) of organization in form ready to use with like operator ('%.65.%') so query looks like
SELECT *
  FROM ACCOUNT ACC
 WHERE EXISTS (SELECT 1

                 FROM ALL_USER_ORGANIZATIONS AUO
                WHERE ACC.ORGANIZATIONS LIKE AUO.ORG1 OR
                      ACC.ORGANIZATIONS LIKE AUO.ORG1 OR
                      ACC.ORGANIZATIONS LIKE AUO.ORG1 OR
                      ACC.ORGANIZATIONS LIKE AUO.ORG1 OR
                      ...
                      ACC.ORGANIZATIONS LIKE AUO.ORG40
              );

If anyone has some suggestions 2 improve this, welcome, otherwise hope this helps some other also.

Bye, Miha Received on Wed Nov 28 2001 - 06:50:01 CST