Design Tables For Business Rules

Question

Design Tables For Business Rules

SQL Editor

SSCommitted

Points: 1980
More actions
January 12, 2010 at 11:46 pm

#229028

Hi all
I Am Working On E commerce Project.
I Wand To Store My Business Rules In Tables.
Because Rules May Be Change According To policy And Other Conditions.
Any body give me idea how we define my schema for business rules.
Thanks In Advance

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 1

Unfortunately no. That's just too vague a question to be able to answer it with any meaning or depth.

The first question I'd ask, when you say that you're storing business rules, how will they be applied? Business rules as in "how the data relates to one another" or business rules as in "when Value x hits 50, sell"? There are just too many questions to begin to drill down on what you need.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

SQL Editor SSCommitted Points: 1980 More actions · Answer 2

Thanks for Your Reply.............

Here we define some table and rules.

Suppose I Have An Item Table Which Is As Follows.........

M Item

Item IDItem Name Sale Price

1ITEM120

2ITEM230

3ITEM335

Now We Want Add Rules As Follows

Rule1

If Product Sale Price > 25 Then Tax = 10% Else Tax=5%

We Try To Define A M Rule Table As Follows

M Rule

Rule ID Rule Name Condition Table Condition Field Condition OperatorCondition Value

1Tax(5%) M Item Sale Price <=25

2Tax(10%) M Item Sale Price >25

Continue…………

Calculation Table Calculation Field Plus Minus OperatorValue

M Item Sale Price +%5

M Item Sale Price +%10

If product has Sale price > 25 then rule id 2 applied on that item

If product has sale price <=25 then rule 1 applied

Our Issue as Follows. ……………

1. This Is A Simple Process. Rule May We Vary Complicated In Nature.

Then We Can Further Normalized This m Rule Table to two part .

A. Conditions Part.

B. Calculassions Part.

2. It Is Possible That One Product May Be Fulfill Condition Of Two Rules Then Here We Can Define Precedence Order

3. Rule May Be applied on group like total of all items. Then we need a special object like Total To Implement Rules. This Object May Or May Net Be the part of any table. Then this may be complicate din calculation for calculate intermediate object value.

Please provide me suggestions for override these issue and our thought is right?

Thanks In Advance.

PaulB-TheOneAndOnly SSC Guru Points: 66773 More actions · Answer 3

Define rules in a table, PK will be like (product, sequence) - you can include a row for "total" then enforce/implement via instead of triggers.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 4

I think you could store this in tables, but I'm not sure that you should resolve these in TSQL. However, if you did, then probably you'll need to apply a "weight," or priority, to the rule and make sure that no two rules have the same weight. This will allow you to determine which rule needs to be applied when more than one rule could apply.

I did something similar to this. We stored the actual rule as a segment of TSQL and then used sp_executesql to built a parameterized query string to execute the query. We further broke down the rules by type so we didn't have to try to check every possible rule on any given action, but instead were able to filter the rules by type and then figure out which ones applied. It wasn't easy. But the storage is pretty simple. The table was no more than four or five columns, one of which was text (now I'd used VARCHAR(MAX)).

You might be better off using code to resolve the applicability of a given rule only because TSQL can be persnickety about the methods of filtering data on the fly.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software