0

I have question related to business rules. Let's have a entity Account with some properties (amount, name, type etc.) on which we define business rules.

I store my rules in database table as follows

Rule_id | Field | Operator | value .

Rule can be like , amount > 1000, name ="abc", type="x" etc. Rules are grouped and mapped to a user.

Account are created in system, and admin has to approve them. When admin login based on his rule set, admin should see relevant accounts.

Like if admin rule set is amount>500, then any account less 500 is not shown to him.

My question is best way to implement it in database, how to query so that relevant accounts can be fetched depending on underlying rule set.

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
Arnet11
  • 79
  • 1
  • 12

2 Answers2

0

create a user table something like this

users

 userId | Username | UserType | Rule_id

when a user log-in, join the user table with rule table on Rule_id and generate the condition using a query like this 

select 'Select * from Account where '+Field+Operator+value from rules   
where Rule_id=1

The result of this query will give another sql query on executing the same will fetch the accounts that are accessible for that user

result of the above query

Select * from Account where amount>1000
Joe G Joseph
  • 23,518
  • 5
  • 56
  • 58
0

What you describe is quite similar to Oracle's Fine-Grained Access Control (AKA Virtual Private Database). This is primarily a security tool, but it could be used to enforce generic business rules. Find out more.

Even if you choose not to use FGAC (or can't because you don't have Enterprise Edition) it will give you some hints about how to implement a solution: use sys_context and namespaces to hold the rules, and views referencing sys_context functions to enforce them.

APC
  • 144,005
  • 19
  • 170
  • 281