[First I have to apologize because I am posting this in two different forums on this site.]
I am building a system to mimic the NT user and group system. It works fine, but because of the way I am
storing and querying the data (from MSSQL), it takes forever! I need to use some advanced theory to speed it up and
was hoping some of you theorists might have some advice.
We have
--User
who has
--User Rights
who belongs to
--User Groups
who have
--User Rights
For example:
--Bob
who has
--Right 1, 2, 5, 11, and 17
who belongs to
--Group A and R
who have
--Group A has
--Rights 3, 8, 11, 16
--Group R has
--Rights 1, 2, 55, 102
Therefore, Bob has these Rights
--1, 2, 3, 5, 8, 11, 16, 17, 55, 102
How can I store this in a database in a manner where I can quickly compare Bob's Rights against
Phil's or Sally's? I need to be able to tell if they are equal, or (and this is VERY important, and the
part that is killing me) if Bob has all of Sally's Rights plus more. ie. Bob can do everything Sally can
do, and more.
Right now I have tables in a database storing this information like
--Accounts (Bob, Phil, Sally)
--Groups (A, B, C, ...R, S, T)
--Account Rights (keyed by the Account ID#)
--Group Rights (keyed by Group ID#)
This means that I have to perform a UNION and compare the results of the UNION.
I am wondering if there might be some sort of data structure that would allow me to
map one users Rights against another users using a SQL query (I have to use only one query because I am filtering
based on the results of the query)?
And give me an answer of:
--equal Rights
--user A has same and more than B
--user A has some, but not all B (ie. a False)
Depending on the data structure, I can of course, change the way I am storing the data in the database.
I was thinking some sort of matrix, hash, or ingenious implementataion of an existing construct (Travelling Salesman, etc).
Serious cudos to anyone who can help me with this. It's driving me crazy at how long it takes to list users in my app.
Thanks!!