Creating A Rules (Conditions) Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Creating A Rules (Conditions) Table

Author

Topic

SouthSdRob
Starting Member

2 Posts

Posted - 2014-07-23 : 10:14:45

Hi, I'm trying to create a rules and/or conditions table for a trucking company I'm working with. Instead of putting the business rules into the program code, they want to have a configuration page that would allow them to change/update rules as time goes on.

As an example, they have incentives for their sales employees on shipped orders based on number of loads and billable amounts. In their first 180 days with a new customer, they award say $100 for any sales person that fills at least 5 orders and has billed out at least $5,000.00 with this new customer.

Right now, my Rules Table looks something like this:
ID - Int (Auto-Increment)
Step - Int
Operation - VarChar
Data_Column - VarChar
Condition - VarChar
Condition_Value - VarChar
Logical_Operation - VarChar

Here are my 3 rows (for now)
ID = 1
Step = 1
Operation = NULL
Data_Column = 'Employee_Position'
Condition = '='
Condition_Value = 'Sales Person'
Logical_Operation = 'AND'

ID = 2
Step = 2
Operation = 'SUM'
Data_Column = 'ORDERS'
Condition = '>='
Conditional_Value = '5'
Logical_Operation = 'AND'

ID = 3
Step 3
Operation = 'SUM'
Data_Column = 'Billed_Amount'
Condition = '>='
Conditional_Value = '5000'
Logical_Operation = NULL

In order, this rule would make sure the Employee_Position = 'Sales Person' AND the SUM of Orders is >= '5' AND the Billed_Amount >= '5000'

Obviously there are a few problems here. 1. I need a row that matches the sales person to the customer in question. 2. there is a time-constraint in which this incentive must be met.

Has anybody seen a table or tables designed in this fashion this abstract so that when a web app is completed, all that you have to give the customer is some sort of configuration page that would allow him or her to configure their incentive rules just by assigning them with a web front that would record the rules in a SQL back end table(s) ???

Thanks in advance...

Rob Bruesewitz
Developer

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-07-23 : 10:59:41

Read through this article before you go further:

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

SouthSdRob
Starting Member

2 Posts

Posted - 2014-07-23 : 11:17:34

Thanks for the link. I understand what the writer is warning but I really enjoyed the link written by Erland Sommarskog which explains that the approach, while a tough challenge, can be done successfully. I will continue to read his work and map out this design in the best way possible. Thanks...

Rob Bruesewitz
Developer

robvolk
Most Valuable Yak

15732 Posts

Posted - 2014-07-23 : 19:58:11

Here's another angle you can try:

http://technet.microsoft.com/en-us/library/aa964135%28v=sql.90%29.aspx

Be warned that the kind of thing you're proposing will likely perform (and scale) poorly. If you go this route you should do tests on larger row counts than you expect to have, at least 10x more, so you can find any potential bottlenecks ahead of time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources