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 2008 Forums
 Transact-SQL (2008)
 Design new rules system

Author  Topic 

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-06-21 : 00:59:55
Hi, I have to design an rule management system. The rules target a table, if found the system takes some additional steps based on the business rules for that rule. Need to design an ER/table structure to handle the rules. Maybe need a master rules table.

Suppose there is a table T1 which has a set of records and rules need to be applied to this table. rules can be:
1. Detect if more than one record exists for a unique combination
2. Detect if a record is not in any category (records are categorized, for simplicity assume a record belongs to either category A or B or,C)
3. further rules....

What I want to know is how to go about designing the above? Maybe an ER or simply for now the table structure and the records in the rules table. Also need to create procedures to handle and detect such rules.
How would the above design work.

Thanks

--------------------
Rock n Roll with SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 02:25:40
we usually do these kind of validations using a procedure. This is quite a common scenario in ETL projects where we will dump the records from the source (may be an excel/text file or another RDBMS table) onto a staging area table. Then we call a procedure which will do validations step by step and do the logging of errors if any. Then at the end it will report error rows back for correcting/resubmission and save correct ones.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-06-21 : 02:43:11
Yes you are right, the table on which the rules are applied is a Staging table and on the application of the rules, the "clean" records will be moved to a final table. Records which fall under the "rules logic" may or may not have additional business logic. Now I am at the inception stage of this rules design. Can you please give me some pointers on the structure of this rules table and in general any other entities belong to this rules system? For example: how will the first rule be applied or how will the rule text be stored and then implemented?

Thanks

--------------------
Rock n Roll with SQL
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 02:50:25
The basic checks like uniqueness, datatype validity etc are done inline in procedure and there's no separate rules table for that. The business specific checks like particular field should be of this format or should allow only these pattern of values etc are usually implemented by means of regular expressions which we store in a table against fileds if its complex. We make use of CLR code for doing the matching.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 02:51:00
The basic checks like uniqueness, datatype validity etc are done inline in procedure and there's no separate rules table for that. The business specific checks like particular field should be of this format or should allow only these pattern of values etc are usually implemented by means of regular expressions which we store in a table against fileds if its complex. We make use of CLR code for doing the matching.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-06-21 : 03:01:09
Ok, so I was thinking on the lines of creating a new Rules table and making each Staging row go through the Rules table. So if Staging has 10 records and there are 5 rules, this means for each record the procedure will be called which applies the 5 rules to the record one after the other, is this the way it should work?

--------------------
Rock n Roll with SQL
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 03:05:21
that would require the use of dynamic sql etc which would be messy and i wont recommend. So far as the rules are consistent i would prefer doing it inline and wherever i need small flexibility i'll use methods like regex match.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-06-21 : 03:37:28
Can you please elaborate on this "So far as the rules are consistent". From what I understand now is: maybe each rule will have a procedure and the rule is coded inline in the procedure itself. For each staging row a parent procedure is called which in turn calls all the rules sub-procedures. How to record that a rule occurred? Will this be logged into a logging table? Also, what if we need to apply the rule again or if the rule has expired.
Maybe I should have given this info earlier, do you think we may need a table now? If yes, how will it operate and implemented.I am a bit lost here.

--------------------
Rock n Roll with SQL
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 03:51:01
For each staging row a parent procedure is called which in turn calls all the rules sub-procedures.

no need of separate procedures you could wrap it all in the same procedure

How to record that a rule occurred? Will this be logged into a logging table?
yep you can log it if you want (but in our case we dont need to do it as because its all in one procedure it will go through all the rules anyways!)

Also, what if we need to apply the rule again or if the rule has expired

Can you elaborate it? what do you mean by apply again and expired?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-06-21 : 04:22:37
We have this concept of override. For Ex., one rule detects if a unique combination for a source occurs twice and it picks one, say based on Comments column. Record source is part of the unique combination. In such a case an override will occur (which record takes preference and gets loaded into final table) and choices can be:
1. specified no. of days
2. until specific date
3. forever
4. until the criteria for this specific rule changes

Now say the next day the same set of records come again and thus fall under the same rule. If the above override was for say n no. of days then system should apply the same override to this new set of record as well.

The above choices for override exist in other rules as well.

--------------------
Rock n Roll with SQL
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 04:25:28
quote:
Originally posted by rocknpop

We have this concept of override. For Ex., one rule detects if a unique combination for a source occurs twice and it picks one, say based on Comments column. Record source is part of the unique combination. In such a case an override will occur (which record takes preference and gets loaded into final table) and choices can be:
1. specified no. of days
2. until specific date
3. forever
4. until the criteria for this specific rule changes

Now say the next day the same set of records come again and thus fall under the same rule. If the above override was for say n no. of days then system should apply the same override to this new set of record as well.

The above choices for override exist in other rules as well.

--------------------
Rock n Roll with SQL


This has to be implemnted as a series of IF..ELSE conditional logic I guess

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-06-21 : 04:51:30
But how will the next day's records know what the override was as it may need to apply the same override. So I guess we need to store the rule applied, who the winner was so any subsequent records falling under the same rule with the same set of unique combination can take up the same override.

--------------------
Rock n Roll with SQL
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 04:59:12
quote:
Originally posted by rocknpop

But how will the next day's records know what the override was as it may need to apply the same override. So I guess we need to store the rule applied, who the winner was so any subsequent records falling under the same rule with the same set of unique combination can take up the same override.

--------------------
Rock n Roll with SQL


but wont the override rule be passed along? otherwise how do you determine which one to apply in first time?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-06-21 : 05:17:43
The first override will come in by the user; user will select which override to choose out of the above 4 once selected, the entry for that unique combination would go into a table which specifies the winner and the override chosen; so any subsequent records with the same unique combo will apply the previous override as was chosen by the user.


--------------------
Rock n Roll with SQL
Go to Top of Page

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-06-21 : 05:20:04
the first override for any unique combination will be chosen by the user; subsequent same unique combinations will apply the same override.

--------------------
Rock n Roll with SQL
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 05:20:18
quote:
Originally posted by rocknpop

The first override will come in by the user; user will select which override to choose out of the above 4 once selected, the entry for that unique combination would go into a table which specifies the winner and the override chosen; so any subsequent records with the same unique combo will apply the previous override as was chosen by the user.


--------------------
Rock n Roll with SQL


ok...in that case its best to have a table to store user preferences so that you can fetch it next time. Also there should be logic to reset it if user wants to set a new override.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-06-21 : 05:48:41
ok, that was a good point about user wanting to reset the override.

So then, to recap: make a procedure with in-line rules execution; if 5 rules then each record goes through 5 rules or maybe if 1st rule holds true then it stops there itself. Log the unique combination for the rule applied; the expiry date of that rule (there will always be a date as all the 4 overrides are related to a time period) and the winner (as chosen by the user) - columns -> UniqueCombo, expiryDate, Winner


Did I miss anything here, any other column that can be useful in this scenario.

--------------------
Rock n Roll with SQL
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 05:52:48
maybe if 1st rule holds true then it stops

why? shouldnt it be otherway around? first rule broken, report failure and quit otherwise keep on checking next ones 2,3, etc

Did I miss anything here, any other column that can be useful in this scenario.

I cant think of anything else based on your scenario

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-06-21 : 05:59:25
Yes you are right; report failure else the next ones.

And follow-up question - report failure in my case is reporting to the user about the records which failed and the user choosing to override with one of them. This means we need a way to store this failure somewhere (say unique combo, ruleapplied) so we can present it to the user. Now, if we write rules queries without a master rules table how will we handle the above? Any thoughts?

--------------------
Rock n Roll with SQL
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 06:04:41
you can log the failures along with messages (or codes if you maintain a message table with standard message list) in the table along with failed records. This will ensure user to understand which rule caused it to fail and can then decide to override them.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-06-21 : 06:25:13
ok thanks this is good alternative, somewhat similar to having masters rules table with a code and a message. So you are suggesting not to have a rules master table and just code in-line all the rules and on failure log with a rule code and present to the user; but yes we implement such a table then we also need to store some kind of queries to fire against each rule, not sure if this rules master table will serve me any purpose.

--------------------
Rock n Roll with SQL
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -