Search code examples
c#oracle-databasearchitecturerule-enginebusiness-rules

Execute business rules dynamically on an Oracle table

I am trying to apply some business rules (inclusion and exclusion rules) on an Oracle table and update a status column based on the rules. A file will be later extracted from this table based on the value of the status column. Now the business wants to configure the rules using a UI. So the rules will be dynamic.

I am planning to store all possible columns on which users might want to use Business Rules in a staging table. In the rule UI all columns from this staging table will be displayed. Then I am planning to create a stored procedure to fetch rules from Rule table and use with dynamic update statement to update the status column. Would Dynamic SQL be good for this?

Rule Table :

Id, RuleName, RuleDesc, ColumnName, Operator, Value, Connector, ConnectOperator, RulePrecedence

Connector- To connect to rules say

#1 Code=1001
#2 state='FL'

In the first rule user can put #2 as Connector and ConnectOperator can be "and/or"

Or I can go for C# code, fetching all rows from table into objects and then use LINQ queries to filter rows based on rules and update the table. Maybe use NHibernate as ORM since Oracle is the underlying database. But not sure if the performance would be good with Oracle. The number of rows to be processed can be around 500K

I am also confused, if database will be the best place to store the rules or there are some other means but for sure users want to configure and create the rules and a rule can refer columns which may belong to different tables (that's why i want to store all possible columns in the staging table though I understand it has limitations, if business wants to create rules on a column which is not there in this staging then this architecture will not work)

Any suggestions how I should approach this design?

Solution

  • I went ahead with storing the rules in database table and executing them on run time using stored procedure and dynamic sql. I needed to do some performance tuning on sql queries and now it is working fine. I was able to process 64 MN records within 45 mins which is not bad. I used set based operation (update statement) for rule processing.