Using a TABLE for conflicting roles, wondering about a,b and b,a.
646947Jul 22 2008 — edited Jul 25 2008Our application defines roles for users. A user may have multiple roles except where the two roles conflict according to our business rules. As such, we are looking to CREATE a TABLE that lists these conflicting roles, to be checked when adding a role to a user. The question is what should this TABLE look like.
I am guessing a very simple:
CREATE TABLE Roles_Not_Allowed(Role1 VARCHAR2(15), Role2 VARCHAR2(15));
should do the job.
When roles A and B conflict, the entry can be either A,B or B,A. Making the query:
WHERE Role1 IN (:Role1, :Role2) AND Role2 IN (:Role1, :Role2)
If i make the PK(A,B), to keep the same entry from being in there twice, this does not preclude the opposite from being entered. That is, A,B may also exist as B,A. That might be an issue in keeping this accurate.
I am open to other ideas here.
We are using 10g. We just migrated from 9i.