Hack 81. Build Decision Tables

When you need a query to make decisions based on multiple criteria you can hardcode the logic into a query, or you can use a decision table.

Suppose that you have to apply delivery charges on orders being dispatched, as shown in Table 10-8. The cost is determined by the total weight of the package in most cases; however, free delivery is offered when the cost of the order is at least $300.

Table 10-8. Mail charge rules

Condition	Charge
Order cost is $300 or more	$0
Weight is up to 1 kg	$10
Weight is up to 5 kg	$20
Weight is up to 10 kg	$25
Weight is more than 10 kg	$40

Table 10-9 shows some orders.

Table 10-9. The orders table

id	cost	weight
Josh1	$150	6 kg
Drake	$100	3 kg
Megan	$100	1 kg
Josh2	$200	3 kg
Josh3	$500	1 kg

You can hardcode the rules on postage costs using a CASE statement:

mysql> SELECT id,
 -> CASE WHEN cost >=300 THEN 0
 -> WHEN weight<2 THEN 10
 -> WHEN weight<5 THEN 20
 -> WHEN weight<10 THEN 25
 -> ELSE 40
 -> END AS Postage
 -> FROM orders;
+-------+---------+
| id | Postage |
+-------+---------+
| Josh1 | 25 |
| Drake | 20 |
| Megan | 10 |
| Josh2 | 20 |
| Josh3 | 0 |
+-------+---------+

Hardcoding the values means you have to change the query if the rules change. A more flexible solution has your rules placed in a table. That means that when the business rules change, the changes can be implemented in data rather than in code. Table 10-10 shows how you can express the rules as data. Every row of the table represents one of the conditions in Table 10-8.

Table 10-10. The mailCharge table

field	minVal	maxVal	charge
cost	300	1,000,000	0
weight	0	1	10
weight	1	5	20
weight	5	10	25
weight	10	1,000,000	40

You can CROSS JOIN the orders with the rules to see every order against every potential rule. Then, for each rule, either the cost or the weight is relevant and you can have that quantity show up as testQnty in this query:

mysql> SELECT id,mailCharge.field,
 -> CASE WHEN field = 'cost' THEN orders.cost
 -> WHEN field = 'weight' THEN orders.weight
 ->  END AS testQnty,
 ->  minVal, maxVal, mailCharge.charge
 -> FROM orders CROSS JOIN mailCharge
 -> ORDER BY id,field,minVal;
+-------+--------+----------+--------+------------+--------+
| id | field | testQnty | minVal | maxVal | charge |
+-------+--------+----------+--------+------------+--------+
| Drake | cost | 100.00 | 300.00 | 1000000.00 | 0.00 |
| Drake | weight | 3.00 | 0.00 | 1.00 | 10.00 |
| Drake | weight | 3.00 | 1.00 | 5.00 | 20.00 |
...
| Megan | weight | 1.00 | 10.00 | 1000000.00 | 40.00 |
+-------+--------+----------+--------+------------+--------+
25 rows in set (0.00 sec)

With five orders and five rules you need to test 25 potential rule applications. A rule applies only if the relevant value (testQnty) lies between the minimum and the maximum for that rule. You can include that restriction by introducing a condition:

mysql> SELECT id, charge FROM
 -> (SELECT id,mailCharge.field,
 -> CASE WHEN field = 'cost' THEN orders.cost
 -> WHEN field = 'weight' THEN orders.weight
 -> END AS testQnty,
 -> minVal, maxVal, mailCharge.charge
 -> FROM orders CROSS JOIN mailCharge) t
 -> WHERE minVal <= testQnty AND testQnty < maxVal
 -> ORDER BY id;
+-------+--------+
| id | charge |
+-------+--------+
| Drake | 20.00 |
| Josh1 | 25.00 |
| Josh2 | 20.00 |
| Josh3 | 0.00 |
| Josh3 | 20.00 |
| Megan | 20.00 |
+-------+--------+

Notice that order Josh3 matches two rules. Because these rules include discounts, you should take the smaller of the two values by applying MIN:

mysql> SELECT id, MIN(charge) AS charge FROM
 -> (SELECT id,mailCharge.field,
 -> CASE WHEN field = 'cost' THEN orders.cost
 -> WHEN field = 'weight' THEN orders.weight
 -> END AS testQnty,
 -> minVal, maxVal, mailCharge.charge
 -> FROM orders CROSS JOIN mailCharge) t
 -> WHERE minVal <= testQnty AND testQnty < maxVal
 -> GROUP BY id
 -> ORDER BY id;
+-------+--------+
| id | charge |
+-------+--------+
| Drake | 20.00 |
| Josh1 | 25.00 |
| Josh2 | 20.00 |
| Josh3 | 0.00 |
| Megan | 20.00 |
+-------+--------+

You might arrange a table so that the MAX value was appropriate. This would be the case if your postal charges were based on the package's size and weight, for example. Another option would be to use SUM: you might charge a basic amount plus additional fees for excessively heavy or bulky items, and you could include discounts as a negative amount.

The example shown performs a test on two columns of the source table, but you can apply this technique across any number of fields.

Although we have shown a CROSS JOIN between the order table and the mailCharge table, this operation is actually an INNER JOIN. The optimizer is going to have a hard time making this query run quickly; there is no real shortcut to generating the whole CROSS JOIN and filtering the result set.

10.5.1. Hacking the Hack

Suppose you have a database of employees, which includes how many days per week they work and their job title. Every employee has a contact in the HR department who deals with their employment contract.

Table 10-11 shows an extract from the employee table.

Table 10-11. The employee table

name	dept	hoursPerWeek
Barney	Production	8
Betty	Security	10
Fred	Production	20
Wilma	IT	5

The rules for working out the appropriate HR contract are as follows:

1. If an employee's department is Security, the contact is Alice; otherwise...
2. If an employee is working 30 or more hours per week, the contact is Brian; otherwise...
3. If an employee is working 16 or more hours per week, the contact is Catherine; otherwise...
4. David deals with everyone else.

These rules have a clear order. Because several conditions may apply to a particular individual, you must indicate which rule takes priority.

You can see these rules encoded in the hrContact table, as shown in Table 10-12. The value NULL is used to indicate "any value."

Table 10-12. The hrContact table

priority	dept	minHours	contact
90	Security	NULL	Alice
80	NULL	30	Brian
70	NULL	16	Catherine
60	NULL	NULL	David

You can obtain a list of all possible contacts according to the rules shown. You can save this query as a VIEW named possibleContacts:

mysql> CREATE VIEW possibleContacts AS
 -> SELECT name, priority, contact
 -> FROM employee JOIN hrContact
 -> ON (hrContact.dept IS NULL OR hrContact.dept=employee.dept)
 -> AND (hrContact.minHours IS NULL OR hrContact.minHours<=
 -> employee.hoursPerWeek);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM possibleContacts;
+--------+----------+-----------+
| name | priority | contact |
+--------+----------+-----------+
| Betty | 90 | Alice |
| Barney | 80 | Brian |
| Barney | 70 | Catherine |
| Fred | 70 | Catherine |
| Barney | 60 | David |
| Betty | 60 | David |
| Fred | 60 | David |
| Wilma | 60 | David |
+--------+----------+-----------+

You can then use a correlated subquery to find the contact with the highest priority:

mysql> SELECT name, contact
 -> FROM possibleContacts x
 -> WHERE priority = (SELECT MAX(priority)
 -> FROM possibleContacts
 -> WHERE name = x.name)
 -> ORDER BY name;
+--------+-----------+
| name | contact |
+--------+-----------+
| Barney | Brian |
| Betty | Alice |
| Fred | Catherine |
| Wilma | David |
+--------+-----------+