Rules based updates

Question

Rules based updates

Bruin

SSCrazy

Points: 2058
More actions
May 13, 2022 at 3:10 pm

#4032471
I found an article that is very similar to how I would like to apply business rules to update my table. I will have 4 colname values I would like to use:
Channel,ShipFrom,ProdLine,CustType
Channel -- Will be only 2 values
ShipFrom - Could be single entry, or ALL, or ALL except(xx,xx,xx)
Prodline -- All(*), or using a Like 5%
Custtype - Could be empty,Single value, or a string of values(444,321,999)
I'm trying to figure out how do define the Operator field based upon the different rules since it's not as easy as equal(=) to this value. Some will need to use Like, or In, or maybe even NOT IN.
Looking for suggestions, maybe an alternative approach, or sample Inserts to define the different rules.
Thanks.
```
Create table tblBusRule
(
    RuleID int Primary key not null,
    ColName varchar(20) not null,
    Operator varchar(2) not null,
    ColValue varchar(10) not null,
    RuleOrder int not null
)
```

Viewing 9 posts - 1 through 8 (of 8 total)

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

ratbak Ten Centuries Points: 1358 More actions · Answer 1

I'm not clear what you mean by rules-based updates. When you say "apply business rules to update my table", do you not also mean for inserts into that table? Based on the description & my understanding, this sounds more like a case for referential integrity and declarative constraints than a case for a rules engine. Please provide more detail if you think I'm misinterpreting the scenario.

What is the table that this discussion of business rules applies to? Can you provide DDL? We know what you've proposed for a rules table, but not what you're trying to apply rules to.

Channel: Create a Channel table & foreign key from your table to Channel table. Or just use a check constraint if the available values are essentially static and you don't need a lookup table for people to choose from.

ShipFrom: Create a ShipFrom table with all the valid values. Since this can be a many-to-one relationship, create an association table that allows you to map your unnamed table to one or more ship-from values. Foreign key from the association table to the ShipFrom table, and from your unamed table to the association table.

ProdLine: Not really understanding this one. But almost certainly an application for a lookup table or check contraint.

Custtype: Don't store comma-delimited lists in a single column. Create a CustType table with valid values, and since this can be a many-to-one relationship, create an association table that allows you to map your unnamed table to one or more customer type values. Foreign key from the association table to the CustType table, and from your unamed table to the association table.

And if some of these values are interrelated, then you might need to add associations between lookup tables to ensure those dependencies are enforced (e.g., if certain product lines only ship from certain sources, or some product lines only apply to certain customer types).

Bruin SSCrazy Points: 2058 More actions · Answer 2

The Channel,ShipFrom, and Prodline all exist in the unnamed table(Update table).

The rules table would be used in logic like:

If Channel = ???

and shipfrom = ??? Could be single entry, or ALL, or ALL except(xx,xx,xx)

and Prodline = ??? All(*), or using a Like 5%

trying to figure out CustType logic

Then Update Unnamed table using ColValue from rule tbl.

Bruin SSCrazy Points: 2058 More actions · Answer 3

Rather than having to hard-code all of these rules in SP thats why I was thinking using this rules table.

ratbak Ten Centuries Points: 1358 More actions · Answer 4

How many scenarios/rules are we talking about? Based on what you clarified, you may well have a case for a rules table/engine approach.

If many rules, if the rules change, and/or if you need a business person to maintain the rules via a UI, the rule approach seems like the right approach.

If there are only a handful and they are fairly static, I'd still be inclined to just create a separate procedure for each.

Bruin SSCrazy Points: 2058 More actions · Answer 5

10-15 rules at this point...yes we would like a UI for BEU to make changes to the different rules..

Have any good examples of how to accomplish based on requirements provided?

Thanks again..

Phil Parkin SSC Guru Points: 246960 More actions · Answer 6

Bruin wrote:

10-15 rules at this point...yes we would like a UI for BEU to make changes to the different rules..
Have any good examples of how to accomplish based on requirements provided?
Thanks again..

If you want help building a UI, you're probably better off in a .NET (or whatever technology you choose) forum.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Bruin SSCrazy Points: 2058 More actions · Answer 7

okay thanks, but that further down the road... looking for help in configuring the rules based schema and maybe some examples using my criteria above.

Thanks.

Bruin SSCrazy Points: 2058 More actions · Answer 8

If I do the rules table approach how should I multiple comma delimited entries, or can I avoid that?

Thx.