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 combination2. 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 |
|
|
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 expiredCan you elaborate it? what do you mean by apply again and expired?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 days2. until specific date3. forever4. until the criteria for this specific rule changesNow 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 |
|
|
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 days2. until specific date3. forever4. until the criteria for this specific rule changesNow 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 |
|
|
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 |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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, WinnerDid I miss anything here, any other column that can be useful in this scenario.--------------------Rock n Roll with SQL |
|
|
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, etcDid 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 |
|
|
Next Page
|