A decision table is just a table
Every business has rules that change faster than its software. A discount for
large orders. A different rate for export customers. A promotion that runs
until the end of the year. Each one starts as a sentence in a meeting and ends
as an IF buried somewhere in the code.
From then on, every change goes the same way. Sales wants the threshold moved from 500 to 400. That becomes a ticket, the ticket waits for a developer, and the developer makes a change that needs testing and a release. Meanwhile the one person who actually understands the rule cannot even see it.
There is an old, well-tested answer to this: the decision table. Every row is one rule. The columns are the conditions, plus the outcome. Business people can read one and, more to the point, change one. The usual catch is that it needs a rules engine next to your application, and that engine is one more thing to deploy, integrate and keep in sync.
In TSQL.APP a decision table is just a table. The business edits the rows on a screen, and the code only reads them.
The rules, as rows
Take discount rules. A table with one column per condition and one for the outcome:
sqlCREATE TABLE dbo.discount_rule (
id int IDENTITY PRIMARY KEY,
priority int NOT NULL,
customer_group nvarchar(20) NULL, -- NULL = any
region nvarchar(20) NULL, -- NULL = any
min_quantity int NOT NULL DEFAULT 0,
discount_pct decimal(5,2) NOT NULL,
valid_from date NOT NULL,
valid_to date NULL -- NULL = no end date
);
Filled in, it reads the way the sales team would put it themselves:
| priority | customer group | region | from quantity | discount | valid |
|---|---|---|---|---|---|
| 10 | wholesale | export | 500 | 12 % | from 1 Jan |
| 20 | wholesale | any | 100 | 8 % | from 1 Jan |
| 30 | any | any | 1000 | 5 % | from 1 Jan |
| 40 | retail | any | 0 | 2.5 % | Nov–Dec only |
An empty cell means "any", and the lowest priority number that matches wins. That is the whole rulebook. There are no exceptions hiding anywhere else.
The code only reads them
Now the part that would normally hold the rules. Here it does not hold a single one:
sqlUPDATE ol
SET ol.discount_pct = ISNULL(r.discount_pct, 0),
ol.rule_id = r.id
FROM dbo.order_line AS ol
OUTER APPLY (
SELECT TOP (1) dr.id, dr.discount_pct
FROM dbo.discount_rule AS dr
WHERE (dr.customer_group IS NULL OR dr.customer_group = ol.customer_group)
AND (dr.region IS NULL OR dr.region = ol.region)
AND ol.quantity >= dr.min_quantity
AND ol.order_date >= dr.valid_from
AND (dr.valid_to IS NULL OR ol.order_date <= dr.valid_to)
ORDER BY dr.priority
) AS r
WHERE ol.order_id = @id;
This sits in a button on the order screen. In TSQL.APP a button's script gets
the open record's fields as ready-made variables, so @id is simply the order
the user is looking at.
Two things are worth noticing. The statement contains no discounts, no thresholds and no customer groups, only the shape of the matching. Moving the threshold from 500 to 400 is now a change to a row, not to this code. And it handles the whole order in one statement rather than looping line by line. For a database, applying a rulebook to a set of rows is a join, which is the thing it does best.
It also writes down which rule fired. When a customer asks why they got 8 % and not 12 %, the answer is a row number, not an investigation.
The obvious objection
So the business still has to write SQL? No. This is where the framework does the work that a separate rules engine would otherwise do.
A screen in TSQL.APP is generated from a table. Point a card at
discount_rule and the list view, the edit form and the field types come from
the table's own definition. Nobody builds a "rule editor".
The sales manager opens a list of rules and changes the threshold in a field.
Who may do that is decided on the server, like every other permission in the framework. The card has a role, and creating, changing and deleting rows each have their own setting, so you can let one role edit and everyone else only read. Sales can own the rulebook without being able to touch anything else, and the rest of the company can still read it.
The table holds the rules and the screen lets the right people change them. The code says nothing about the rules at all.
Who writes what
It is worth being straight about where the line falls. The person who designs
the table and writes that UPDATE knows SQL. TSQL.APP is a platform for people
who know SQL, and it does not pretend to turn a sales manager into a developer.
What it does is put the line in the right place. Deciding what the discount is belongs to the business, and with a rule table it is theirs to change, any day, without a release. Deciding how a rule gets applied belongs to whoever writes the SQL, and that part rarely changes. Most of the friction between business and IT comes from those two living in the same piece of code. Split them apart and most of the tickets go away.
There is documentation and a programming reference online, and you can always book a demo.