Hello,
in the course of time a piece of code that was used to take decisions programmatically grew into a mess of multiple nested IF statements.
We decided to implement it as a decision table.
We have a working solution, yet I would like to know how other programmers would solve this problem.
Because there are more than 20,000 possible combinations of the attributes, a table that holds each of them is out of question, nobody would be able to maintain it.
Here is a simplified example with clients which each can be in different regions, one attribute that can be Yes or No and one attribute that is derived from many other properties and there is no table that has every possible value of this attribute (I know there should be a table for this instead of hard coding them but that is another story).
The decision table is hierarchical meaning that each column carries more weight than the columns to the right.
The result is either Y (YES) or N (NO)
DROP VIEW ranked_decisions;
DROP TABLE decisions;
DROP TABLE clients;
DROP TABLE regions;
CREATE TABLE clients
(
client\_id INTEGER NOT NULL ,
client\_name VARCHAR2 (100 CHAR)
) ;
ALTER TABLE clients ADD CONSTRAINT cli_pk PRIMARY KEY ( client_id ) ;
CREATE TABLE regions
(
region\_id INTEGER NOT NULL ,
region\_name VARCHAR2 (100)
) ;
ALTER TABLE regions ADD CONSTRAINT reg_pk PRIMARY KEY ( region_id ) ;
CREATE TABLE decisions
(
client\_id INTEGER NOT NULL ,
region\_id INTEGER ,
dec\_yn\_1 VARCHAR2 (1 CHAR) ,
dec\_attr\_1 VARCHAR2 (10 CHAR) ,
dec\_result VARCHAR2 (1 CHAR)
) ;
ALTER TABLE decisions ADD CONSTRAINT dec_cli_fk FOREIGN KEY ( client_id ) REFERENCES clients ( client_id ) ;
ALTER TABLE decisions ADD CONSTRAINT dec_reg_fk FOREIGN KEY ( region_id ) REFERENCES regions ( region_id ) ;
INSERT INTO clients (client_id,client_name) VALUES (1,'A');
INSERT INTO clients (client_id,client_name) VALUES (2,'B');
INSERT INTO clients (client_id,client_name) VALUES (3,'C');
INSERT INTO clients (client_id,client_name) VALUES (4,'D');
INSERT INTO clients (client_id,client_name) VALUES (5,'E');
INSERT INTO regions (region_id,region_name) VALUES (10,'BY');
INSERT INTO regions (region_id,region_name) VALUES (11,'BW');
INSERT INTO regions (region_id,region_name) VALUES (12,'HE');
INSERT INTO regions (region_id,region_name) VALUES (13,'TH');
INSERT INTO regions (region_id,region_name) VALUES (14,'HH');
CREATE OR REPLACE VIEW ranked_decisions AS
WITH all_combinations AS (
--generate each possible combination of the attributes
SELECT cli.client\_id
,reg.region\_id
,yn.yn\_1
,attr.attr\_1
,dc.dec\_result
-- Ranking of the results
-- If there is a result with dc.client\_id \<> NULL, a value of 8 is associated
-- Accordingly for dc.region\_id 4, dc.dec\_yn\_1 2 and for dc.dec\_attr\_1 1
-- Thus I can be sure that combinations that have a match get a higher
-- value than generated combinations
,ROW\_NUMBER () OVER (
PARTITION BY cli.client\_id,reg.region\_id,yn.yn\_1,attr.attr\_1
ORDER BY CASE WHEN dc.client\_id IS NULL THEN 0 ELSE 8 END
+ CASE WHEN dc.region\_id IS NULL THEN 0 ELSE 4 END
+ CASE WHEN dc.dec\_yn\_1 IS NULL THEN 0 ELSE 2 END
+ CASE WHEN dc.dec\_attr\_1 IS NULL THEN 0 ELSE 1 END
DESC
) r
FROM clients cli
CROSS JOIN regions reg
CROSS JOIN (
SELECT 'Y' yn\_1 FROM dual UNION ALL
SELECT 'N' yn\_1 FROM dual
) yn
CROSS JOIN (
SELECT 'LA' attr\_1 FROM dual UNION ALL
SELECT 'M' attr\_1 FROM dual UNION ALL
SELECT 'SR' attr\_1 FROM dual UNION ALL
SELECT 'N' attr\_1 FROM dual
) attr
JOIN decisions dc
ON ( (cli.client\_id = dc.client\_id OR dc.client\_id IS NULL)
AND (reg.region\_id = dc.region\_id OR dc.region\_id IS NULL)
AND (yn.yn\_1 = dc.dec\_yn\_1 OR dc.dec\_yn\_1 IS NULL)
AND (attr.attr\_1 = dc.dec\_attr\_1 OR dc.dec\_attr\_1 IS NULL)
)
)
SELECT ac.client_id
,ac.region\_id
,ac.yn\_1
,ac.attr\_1
,ac.dec\_result
FROM all_combinations ac
WHERE r = 1;
--Default for each client
INSERT INTO decisions (client_id,region_id,dec_yn_1,dec_attr_1,dec_result)
VALUES ( 1, NULL, NULL, NULL, 'N');
INSERT INTO decisions (client_id,region_id,dec_yn_1,dec_attr_1,dec_result)
VALUES ( 2, NULL, NULL, NULL, 'N');
INSERT INTO decisions (client_id,region_id,dec_yn_1,dec_attr_1,dec_result)
VALUES ( 3, NULL, NULL, NULL, 'Y');
INSERT INTO decisions (client_id,region_id,dec_yn_1,dec_attr_1,dec_result)
VALUES ( 4, NULL, NULL, NULL, 'N');
INSERT INTO decisions (client_id,region_id,dec_yn_1,dec_attr_1,dec_result)
VALUES ( 5, NULL, NULL, NULL, 'Y');
SELECT dec_result
FROM ranked_decisions
WHERE client_id = 4
AND region_id = 14
AND yn_1 = 'N'
AND attr_1 = 'LA';
Of course we get a N.
Let's add an exception
--Default for region HH is Y for client A and D
INSERT INTO decisions (client_id,region_id,dec_yn_1,dec_attr_1,dec_result)
VALUES ( 1, 14, NULL, NULL, 'Y');
INSERT INTO decisions (client_id,region_id,dec_yn_1,dec_attr_1,dec_result)
VALUES ( 4, 14, NULL, NULL, 'Y');
Now we get Y for the same query.
Some more exceptions might be
-- Client A is Y if region is BY
INSERT INTO decisions (client_id,region_id,dec_yn_1,dec_attr_1,dec_result)
VALUES ( 1, 10, NULL, NULL, 'Y');
--except if attribute 1 is LA
INSERT INTO decisions (client_id,region_id,dec_yn_1,dec_attr_1,dec_result)
VALUES ( 1, 10, NULL, 'LA', 'N');
-- Client D is Y if YN-attribute is Y
INSERT INTO decisions (client_id,region_id,dec_yn_1,dec_attr_1,dec_result)
VALUES ( 4, NULL, 'Y', NULL, 'Y');
Do you have other ideas for decision tables with defaults and exceptions?
Regards
Marcus